18
I've solved this by having a very simple calendar table - each year has one row per supported time zone, with the standard offset and the start datetime / end datetime of DST and its offset (if that time zone supports it). Then an inline, schema-bound, table-valued function that takes the source time (in UTC of course) and adds/subtracts the offset.
This ...
17
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?
Memory
Space in the database data files to physically write the inserted data to
Space in the database log files
Space in TempDB (Data and log)
As for how much a ...
15
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, relations visible for semantic layer, ....)
A: Correct, it's normally a good practice to have foreign keys in a data warehouse. However, clustered columnstore indexes ...
13
I wouldn't want to have 200 data flows in a single package. The time it'd take just to open up and validate would make you old before your time.
EzAPI is fun but if you're new to .NET and SSIS, oh hell no, you don't want that. I think you'll spend far more time learning about the SSIS object model and possibly dealing with COM than actually getting work ...
12
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, here's my attempt. I will update this with the magic you use to determine things like Fiscal_MonthNumber and Fiscal_MonthName, because right now they're the ...
12
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 PostgreSQL - it has windowing functions and CTEs (common table expressions). It also supports CHECK CONSTRAINTs and a full range of set operators.
MySQL is ...
11
Here is a whitepaper on when auto_update of stats occurs. Here are the salient points vis-a-vis automatic updates to statistics:
The table size has gone from 0 to >0 rows (test 1).
The number of rows in the table when the statistics were gathered was 500 or less, and the colmodctr of the leading column of the statistics object has changed by more ...
11
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 request data in a raw format, in a table or flat file. It will make it easy to try new ways of storing the data, allow you to reload data if you discover a ...
11
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 recommended by Alexander Kuznetsov - a published database book author and bit of a guru.
Maybe also look at Liquibase or Compare Database Table Data.
There are ...
11
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 buffer_pool by 64/24, you are somewhat likely to have the same cache performance of the buffer_pool. After only one year.
The 99% utilized doesn't really say ...
11
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 using a star
schema only one date dimension is needed, this is a dimension role playing implementation.
There are two dimension role playing implementations :...
11
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 answer satisfies me and help me to start the task. On the process, I hope I get better understanding (if exists) of these techniques. Here is my findings:
...
10
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 on that except some blog posts.
Have a look at these
PDW, Integration Services and Resource Classes
A teched presentation from Channel 9
I only heard about ...
10
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.
My rookie thoughts: Aren't these problems irrelevant to splitting the
database? They are simply problems that need to be solved on their own
either way.
...
10
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 details specific to SQL Server.
When does one still need dimension tables when using CCI?
1. The volume of changes to the dimension table makes updating the ...
10
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 architecture and can be used to test releases that are due to be moved into production or to allow users to view upcoming features before they are released.
The second ...
9
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 system, it would work well.
When I designed a system for storing query strings, I had no idea in advance what fields I would be interested in. I created a table ...
9
If it works for you then it's a good practice. There are basically no hard rules for databases.
9
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 that for data warehouses, you need storage that can provide data fast enough for SQL Server to consume it. Microsoft built a great series of white papers called ...
9
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 need to happen regardless of who builds it.
When you're done building requirements, then you can start sketching out what you'll need to build in order to ...
9
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 there's no automatic failover.
If that's the case for yours (because really, a data warehouse in Full recovery is banana-town-crazy), your best bet would be a ...
8
Firstly...
Separating Datime/Time into a Date dimension and a Time dimension is definitely the way to go.
To manage multiple time zones you need to duplicate the DateKey and the TimeKey so that you have the following:
LocalDateKey
LocalTimeKey
UtcDateKey
UtcTimeKey
You say...
The problem I am having with all that is that 11:00 PM on Tuesday,
...
8
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 partitioning, among many others.
7
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 data warehouse:
denormalized dimensions,
simplified fact tables and measures,
pre-aggregated balances,
pre-accumulated amounts over some time dimension,
often ...
7
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 on very large tables, but it sounds like this is not the case here.
The only caveat is if you expect the tables to grow substantially with time. If they are ...
answered Jan 20 '15 at 16:32
ConcernedOfTunbridgeWells
16.5k2 gold badges53 silver badges70 bronze badges
7
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 reduce the time to get a data warehouse up and running, or did the time it took to ramp up on learning the tool eat the time that otherwise would have been gained?
...
7
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 varbinary(max) data types. Or, design the columnstore index so that it doesn't include these columns.
Simply put, your options are to forgo columnstore entirely, ...
7
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 Warehouse" is sometimes used when it shouldn't be. Do they want a system that aggregates data back to the beginning of time and is loaded nightly and is missing ...
7
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 in the table.
DECLARE @Transactions TABLE (
TransactionId INT
,UserId VARCHAR(10)
,CountryId INT
,TransactionDate DATE
,[MonthName] VARCHAR(...
7
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 also have customer groups or item types and your schema looks more like
fact table -> customer table -> customer group table
fact table -> item table -> item ...
Only top voted, non community-wiki answers of a minimum length are eligible
