21

First impressions Depending on your performance requirements, 100TB is a fairly aggressive data volume. If you want Oracle, you should check out their Exadata systems. Also, take a look at the offerings from Netezza or Teradata. With that volume of selects you might want to look at an OLAP based front end or at least fairly aggressive use of materialised ...


18

Business Intelligence Edition Business Intelligence edition has some useful features, like Master Data Services and non-additive aggregations (i.e. anything but sum/count). EE has partitioning and the rest of the large database features. The EE features are mostly relevant to users with large data volumes. If you have less than (say) 100GB of data then ...


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


13

Is B.I. a business or technical project? There are too many variables to answer this categorically; I'm tempted to VTC the question as it doesn't really have a single correct answer. However, on second thoughts I can say a few reasonably meaningful words on the subject. Customer Intimacy Business Intelligence (or more prosaically reporting) is very ...


13

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


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


13

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


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

What you are describing is a data warehouse. The live, normalized, read-write system is OLTP (online transaction processing) and the denormalized read-only snapshot is a data warehouse. The structure of the data warehouse could be a Star Schema, especially if it's highly denormalized. Data warehouses often have summarization in addition to denormalization....


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


10

Joel Brown has summed up the nature of a data warehouse. I'll add something here about reporting requirements. What is a data warehouse good for A data warehouse is good for analytical reports where you want to calculate aggregates, trends or other statistical or financial metrics over a large volume of data. Generally a periodic load is best for this ...


10

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


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

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

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

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

Some other options to consider when dealing with massive data volumes like this include: Everything that @ConcernedOfTunbridgeWells posted Greenplum from EMC Parallel Data Warehouse from Microsoft Don't plan on skimping on hardware costs anywhere. A system with these sorts of specs is going to cost you some big bucks.


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.


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