Skip to main content
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
12 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.5k
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,203
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
  • 30.3k
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
  • 42.1k
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
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
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.5k
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

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
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.5k
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

Scripts to disable constraints and indexes for data warehouse load

Disabling Foreign keys before loading the data is a bit worrying in your case because when you are enabling them, your scrip does not have any error handing .. TRY .. CATCH blocks. After the data is ...
Kin Shah's user avatar
  • 62.4k
4 votes
Accepted

Data warehouse vs Data mart (sql server)

This is a pretty good question. This topic, many times, gets over-thought. It really all comes down to business needs. If the need to compartmentalize data in to different subject areas (Finance, ...
Wes's user avatar
  • 1,126
4 votes
Accepted

Boolean as measure in fact table or dimension/attribute value

Here is a flexible way to store it that should support changes in requirements in the future. Store the target in a dimension like dim_complaint_type. That way if in the future you have different ...
JeromeFr's user avatar
  • 289
4 votes

Dealing with different types in a fact table

I would steer clear of the 3 option. It's called a snowflake instead of a star schema. It's a rather advanced solution which can be used when necessary but has it's own drawbacks. As usual refer to ...
Tom V's user avatar
  • 15.7k
4 votes

Always On Availability Groups as a Secondary Read Only Database

The secondaries can be used as read only. Keep in mind data is still being added to the secondaries in near real time. Transaction log backups are conducted against the primary AG instance. If the ...
SQLMac's user avatar
  • 182
4 votes
Accepted

Always On Availability Groups as a Secondary Read Only Database

1.Does Always On Availability Groups mean the database will basically be mirrored and that I can use that server to offload the heavy reporting to? It'll be a full copy of the database. You can think ...
Sean Gallardy's user avatar
4 votes

Best practices for transferring data across long, wide tables

Have you considered adding a ''rowversion'' column to your source tables? The simplest way to think about rowversion (formerly timestamp in SQL Server) is as a database-wide autonumber. Every INSERT ...
mwolfe02's user avatar
  • 432
4 votes
Accepted

Data Warehouse Kimball Customer and Customer Address Dimensions

The right schema depends on how you intend to use this data. Let's talk about your options: a) Bridge table. I would not use this approach, because bridge tables are used to express N:N ...
Evandro's user avatar
  • 868

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