17 votes
Accepted

How many rows can SQL Server process in a single INSERT statement into a table?

According to the documentation the only limitation on the number of rows stored per table is as below Limited only by available resources What local resources does this potentially include? ...
George.Palacios's user avatar
16 votes
Accepted

Difference between star schema and data cube?

After spending some time, reading reference books, I came to the point, where I could define the difference between the star schema and data cubes. I could not comment on this definition but this ...
Rathish Kumar B's user avatar
11 votes
Accepted

When are dimension tables still needed using clustered columnstore indexing?

I don't think that your question is applicable to any RDBMS that supports columnar storage. I'm writing my answer from the point of view of SQL Server and most of the reasons depend on implementation ...
Joe Obbish's user avatar
  • 32.1k
10 votes
Accepted

How many date dimensions for one fact

A dimension represent a category of information: e.g. date, product... in your case you have three attributes in the fact table referring to the same analysis axis which is 'DATE', so if you are ...
Yassine LD's user avatar
10 votes

Compare two similar Postgres databases for differences

For schema comparisons, pgadmin4 has a Schema Diff feature (in beta as of 26 Jun 2020). https://www.pgadmin.org/
Rob Bednark's user avatar
  • 2,143
10 votes
Accepted

What are the main reasons to split a Data Warehouse into multiple databases?

You are definitely on the right track! 320GB is not huge for a database, particularly a DW. 1) Current db is poorly optimized, little documentation, sub-optimal datatypes, sub-optimal indices. ...
Vérace's user avatar
  • 29.7k
10 votes
Accepted

Benefit of having time dimension in a star schema?

Let me answer this question with a scenario starting with a simple Transaction table. When our business started, management wanted to know the 'name' of the month, so I've included that information ...
Scott Hodgin - Retired's user avatar
10 votes

Difference Between a Staging and the Production DWH

I think this is a case of the same word being used to describe two different things. The first being a staging environment. As you mention, this is a near copy of the production environment ...
Mr.Brownstone's user avatar
10 votes

What is the best option for High availability on a data warehouse?

Most data warehouses are in Simple recovery model, and most true HA solutions require Full recovery (AGs, Mirroring). The minimum for Log Shipping is Bulk Logged, but that's not really true HA since ...
Erik Darling's user avatar
  • 40.5k
9 votes
Accepted

Data warehouse server. How do you calculate RAM/CPU specifications?

Great question, and I did a session about this at TechEd a few years ago called Building the Fastest SQL Servers: https://channel9.msdn.com/Events/TechEd/NorthAmerica/2012/DBI328 In it, I explain ...
Brent Ozar's user avatar
  • 42.9k
9 votes
Accepted

Is it possible to build Data warehouse by myself?

It's a lot like asking, "Can I build a house by myself?" It depends a lot on what your definition of "house" is. Talk with your internal users to build a set of requirements first - that's going to ...
Brent Ozar's user avatar
  • 42.9k
9 votes

Difference between star schema and data cube?

Qutoting from Kimball Dimensional Modeling Techniques Star Schemas and OLAP cubes Star schemas are dimensional structures deployed in a relational database management system (RDBMS). They ...
Galal Ouda's user avatar
8 votes

SQL Server Edition for MS 70-463

No, you will need an Enterprise-equivalent (Evaluation/Developer) Edition to take the 70-463 course. It requires a broad range of Enterprise-only features, including column store indexes and ...
Paul White's user avatar
  • 83.7k
7 votes

data warehouse automation tool: benefit or bane?

I'll weigh in from the user perspective. I have worked extensively (15 or so projects) with one of the automation tools on a SQL Server backend, and results were mixed. Did the DWA tool greatly ...
Tom V's user avatar
  • 15.7k
7 votes
Accepted

Where should I put nvarchar(max) dimensions in my data warehouse?

Microsoft recommends using a CCI for large data warehouse tables with a few caveats including: Don't use a clustered columnstore index when: The table requires varchar(max), nvarchar(max), or ...
Joe Obbish's user avatar
  • 32.1k
7 votes

Is it possible to build Data warehouse by myself?

Along the same lines as @Brent's statement of, "It depends a lot on what your definition of 'house' is", you need to find out what "Data Warehouse" means to those who requested it. The term "Data ...
Solomon Rutzky's user avatar
7 votes
Accepted

What are the difference between a "dimension" table in a star-schema and a "lookup" table in a relational database?

If you only have those tables there will be little difference between your datawarehouse star schema and your actual schema. You probably have a much more complex relational schema though, where you ...
Tom V's user avatar
  • 15.7k
7 votes

Design star schema for many-many relationship

Many-to-many tables are typically handled with a "bridge table." This is really just another name for a many-to-many table like you'd use in a transactional database: one column for an FK ...
Jon of All Trades's user avatar
7 votes

What is the difference between Federated and Decentralized Data Warehouse?

A decentralised data warehouse is essentially a collection of data warehouses maintained by individual regions or business units but made available centrally. These may be on the same physical server,...
ConcernedOfTunbridgeWells's user avatar
6 votes
Accepted

Ordering of a dimension in sql server datawarehouse

Analysis Services will set the data type of the dimension attribute to be the same as the source column. It is sorted exactly as you asked for it. Your days are declared as a character type. Therefore,...
billinkc's user avatar
  • 15.8k
6 votes
Accepted

Avoid using complicated strings in Joins in SQL Server?

You don't need to use the ProductKey column as the key column in DimProduct. You can create a surrogate key that is a composite key made up of DimProductID that is an IDENTITY column and increments ...
Solomon Rutzky's user avatar
6 votes

Run SQL Agent Job 3 times in a Row

I think I would solve it this way: Create a driving stored procedure that identifies if work needs to be done. That could mean checking a table for the next folder month to process. This driving ...
Scott Hodgin - Retired's user avatar
5 votes

FK Issue with Fact Table

I see a tweet as an event happening, so I would model it as a new fact table FactTweet. More specifically as a factless fact. The dimensions for FactTweet would be DimDate, DimCar (if you can relate a ...
JeromeFr's user avatar
  • 289
5 votes
Accepted

How do you index alphanumeric foreign keys on large fact tables?

Assuming that your assumption is correct regarding the VARCHAR(50) field using a collation of SQL_Latin1_General_CP1_CI_AS, then you should consider altering those alphanumeric "code" fields in each ...
Solomon Rutzky's user avatar
5 votes

data warehouse automation tool: benefit or bane?

Eckerson's list is poorly researched. There is a more comprehensive catalogue of data warehouse automation tools on our web site, at http://ajilius.com/competitors. I'll answer your questions from a ...
Ron Dunn's user avatar
  • 151
5 votes
Accepted

Schema for Financial Database

You can achieve this by creating 3 or 4 tables: Company Information Fundamental Entries Fundamental Data MarketCap Data (Optional) Company information will contain information about all the ...
aholkar's user avatar
  • 66
5 votes
Accepted

Scripts to disable constraints and indexes for data warehouse load

For a few things that you may have not considered: If an index starts as disabled you'll end up rebuilding it after your script finishes. If an index starts with page compression you'll rebuild it ...
Joe Obbish's user avatar
  • 32.1k
5 votes
Accepted

Which DB for 1 table with 100 Million rows per year?

With that table an no extra keys/indexes you have 160 bytes per row, so 100,000,000 rows per year is about 16GByte per year. Any decent DBMS (SQL Server, postgres, [YourFavouriteDBHere], ...) should ...
David Spillett's user avatar
5 votes

What's the best way to concatenate a multi-column foreign key?

It's quite common in Data Warehouse schema designs to assign a new, integer, single-column key to every table, and retain the source-system key (sometimes called the "business key") as well. ...
David Browne - Microsoft's user avatar
4 votes

Data warehouse server. How do you calculate RAM/CPU specifications?

The Fast Track Data Warehouse Reference Guide for SQL Server 2012 is actually a bit out-of-date especially if you're moving to SQL Server 2016 (really? Call me), not just in terms of time, but also ...
wBob's user avatar
  • 10.4k

Only top scored, non community-wiki answers of a minimum length are eligible