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?
...
- 5,408
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, ...
- 41.3k
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 ...
- 2,026
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 ...
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,...
- 176k
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 ...
- 68.2k
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 ...
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 ...
- 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 ...
- 30.3k
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 ...
- 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 ...
- 760
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.
...
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 ...
- 12.8k
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 ...
- 67.1k
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.
- 26.8k
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 ...
- 41.3k
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 ...
- 41.3k
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 ...
- 23.2k
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 ...
- 33.4k
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 ...
- 69.3k
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/
- 1,793
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 ...
- 8,849
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 ...
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 ...
- 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 ...
- 30.3k
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 ...
- 65.6k
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 ...
- 71
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 ...
- 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 to the ...
- 5,762
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,...
Only top scored, non community-wiki answers of a minimum length are eligible