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? ...
user avatar
16 votes

Clustered columnstore indexes and foreign keys

You've got lots of questions in here: Q: (The lack of foreign keys) confuses me a lot! It is a good practice (not mandatory) to have Fk's in the DWH for a variety of reasons (data integrity, ...
user avatar
  • 41.4k
15 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 ...
user avatar
14 votes
Accepted

Compare two similar Postgres databases for differences

Have a look at the Stack Overflow Q & A, How to check difference between two databases in PostgreSQL?; in particular Another PostgreSQL Diff Tool (apgdiff) (schema comparison only). Apgdiff is ...
user avatar
13 votes

Best approach for populating date dimension table

UPDATE: for a more generic example of creating and populating a calendar or dimension table, see this tip: Creating a date dimension or calendar table in SQL Server For the specific question at hand,...
user avatar
13 votes

How do you calculate how much hardware resources you need for a database?

The short answer: Can't tell. Not enough info. The long answer... If your data is growing at 10%/month, it will be about a year before it is 64/24 times as big. So if you grow the RAM and the ...
user avatar
  • 68.8k
12 votes
Accepted

Is Data Warehousing possible in MySQL and PostgresSQL

You can certainly use MySQL or PostgreSQL for this requirement using Python as your database access language. I've never used Python cubes so I can't speak to that. I would recommend that you use ...
user avatar
11 votes

Alternative to EAV for dynamic fields in a star schema data warehouse

I can think of three solutions - EAV, XML, and Sparse Columns. The latter is vendor-specific and may not be useful to you. Whichever method you choose, you may wish to consider storing the original ...
user avatar
  • 3,680
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 ...
user avatar
  • 30.5k
10 votes

Server roles in SQL Server 2016

These terms are not new to SQL 2016, but they are also not server roles as far as I know. For all i know these are pre-built Parallel Data Warehouse resource classes. There isn't much documentation ...
user avatar
  • 15.4k
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 ...
user avatar
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. ...
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 ...
user avatar
9 votes

Alternative to EAV for dynamic fields in a star schema data warehouse

EAV is not a bad design, per se, it is simply a design that requires a fair amount of forethought and can be wrought with performance issues as the quantity of data rises. It may be that for your ...
user avatar
  • 67.2k
9 votes
Accepted

Datawarehouse bad practice?

If it works for you then it's a good practice. There are basically no hard rules for databases.
user avatar
  • 26.9k
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 ...
user avatar
  • 41.4k
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 ...
user avatar
  • 41.4k
9 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 ...
user avatar
9 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 ...
user avatar
  • 33.5k
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 ...
user avatar
  • 71k
7 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/
user avatar
  • 1,813
7 votes

Purpose of Data Warehousing?

As a BI consultant, my view on datawarehousing is that it provides (primarily) non-technical users with an easily accessible set of facts and dimensions. Often, you'll see the following features in a ...
user avatar
7 votes

Datawarehouse bad practice?

Building temporary indexes for ETL jobs is not necessarily bad practice, as the index builds are fairly quick. Where it might not be so efficient is if you have relatively small incremental updates ...
user avatar
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 ...
user avatar
  • 15.4k
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 ...
user avatar
  • 30.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 ...
user avatar
7 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 ...
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 ...
user avatar
  • 15.4k
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 ...
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,...
user avatar

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