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


14

lots of Discussions about ETL vs ELT out there. The main difference between ETL vs ELT is where the Processing happens ETL processing of data happens in the ETL tool (usually record-at-a-time and in memory) ELT processing of data happens in the database engine Data is same and end results of data can be achieved in both methods. it very much depends on ...


14

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

If this is cyclical ETL, and you are in a development (i.e. NOT LIVE) data environment, then you definitely should manage your indexes as a part of your load cycle. I do this for several data sets every month, the largest of which adds around 100 GB monthly to a 5 TB data set. I have done extensive testing, and from my own experience the most efficient way ...


12

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

It's almost a matter of semantics. A lot of hot air gets released in discussions about this but I'm not really convinced that there is any real philosophical depth to a distinction between the two. At some level you can view ETL as transforming data in a client-side tool before finally loading it, with ELT implying that the data is transferred to some sort ...


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


10

It seems you want to aggregate location based statistics over time for rainfall. A database structure like the one below would let you do that. The 'data source' could be just a filename, or some indication as to where it came from. create table DimDataSource ( DataSourceID int identity (1,1) not null DataSourceDesc nvarchar (100) --...


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

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


10

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 :...


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 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

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: ...


9

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


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

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

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, ...


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


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? ...


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