Tag Info

Hot answers tagged

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


16

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


16

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


14

In my experience, a recursive hierarchy is the most practical way of tackling this. It offers the following advantages: Unlimited depth. Compactness. Flexibility. Speed. By contrast, it takes an extra table for each level of "-to-many" joins. This is hard coded and difficult to maintain against schema updates. By using filtered indexes, a large table of ...


11

It is mostly a license issue. These developments end up patching the code quite heavily, so if you were to deal with MySQL, you'd either have to open-source your code or be at the mercy of MySQL's corporate owner for the life of your business. Some offers for MySQL get around that by implementing their work as a storage engine, but that doesn't offer all ...


11

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


11

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


10

I have been working with Pentaho for about a year now. Pentaho is a full Open Source suite for Business Intelligence. It's strenght is that it relies on independently managed project : Pentaho Data Integration (Kettle) ->ETL Pentaho Report Designer (PRD) -> Report designer Mondrian -> R-OLAP cube and much more.. You can use them as a whole (Pentaho ...


10

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


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


9

Don't put nulls in the Warehouse or in the Marts. The warehouse should be well normalized (at least BCNF) and therefore should exclude nulls. Nulls might be preserved in staging tables if they exist in data sources but they shouldn't be needed in the warehouse itself. Marts should be designed to support presentation tools and user queries. Nulls just get ...


9

Here is my opinion: If you are having very few updates/deletes you can increase the pagefill factor to 95%. This will save on space and reads. Do some testing though. Partition the table based on a broad category like year. Put these partitions on different filegroups.


9

You aren't bottlenecked by CPU usage. The majority of the time, SQL Server will be IO bound since physical disk access is many magnitudes slower than CPU or memory. An analogy to what you are asking is: I have this huge fire hose connected to my kitchen faucet. How come I can't get full pressure from the fire hose like they get from a hydrant? The ...


9

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


8

I can see two reasons: 1) historically, PostgreSQL had better query planner and statistics analyzer. This might be not true now, but few years ago PostgreSQL was much better then MySQL on complex queries, which is OLAP ones. 2) PostgreSQL have better functions/triggers/etc programming support.


8

+1 for @JNKs explanation. Simplest way to verify your bottlenecks would be to reset wait stats before the process starts: DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR); Immediately when the process is completed, capture wait stats. If you want to get a more fine grained picture of where the bottlenecks are occurring, capture the wait stats to a table on 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

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


8

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


8

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


8

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


7

You likely won't run into the issues of write problems, as I assume this would be something created once (or once per year), and then not touched. But using an index will likely be a hinderance if you're searching by week ... The problem is, if the index is used, it might scan that first, and then grab each record out of the table individually, which when ...


7

I would recommend Building a Data Warehouse With Examples in SQL Server by Vincent Rainardi, as this covers SQL Server specifics. The Data Warehouse Toolkit is also an excellent and practical guide, but isn't platform specific.


7

You can leave the FK to some dimension tables as NULL if those dimensions are not known or not applicable. You just have to remember to use outer joins when you do your reporting query. Alternatively, some people create a "none" and/or an "n/a" dimension record for data mart dimensions and then populate fact table FKs to point at these rather than using ...


7

Parallel Server Sets: PARALLEL_DEGREE_LIMIT limits the degree of parallelism, but if your query is sorting or grouping the number of parallel processes can be twice as much (two server sets to enable inter-process parallelism). That explains why you will see 48 parallel processes even with a limit of 24. This also happens if you use resource manager to ...


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


6

200 million rows per year is not especially large (unless the rows are unusually large). You need to pay attention to sound database design principles (normalization) and make use of standard features like indexing and partitioning. Obviously the right hardware is important too. There isn't enough information here to give specific advice. Consider hiring ...


6

No, you do not need to enumerate the columns used in a materialized view when creating the materialized view log. In fact you cannot create a materialized view log using the primary key method and include all the columns because you would be including the primary key column itself, which is not allowed. The concept of a materialized view log is to store ...


6

I think you're not understanding what is meant by datawarehouse. It's not a tool. Or an application. Or a database. It doesn't mean "big database". You said, we worked with MS Business Intelligence and MSSQL as Data Warehouse storage. MSSQL wasn't just the storage for the DWH, it is the DWH. A datawarehouse is a database which is specifically ...


6

Rule of Thumb: Does your workload fit with "traditional" approaches? Like oracle? Oracle can handle "00's of thousand of data of call" so does other databases like MySQL and SQL Server. You start looking into "Big Data Tools" when these solutions start to break. It's a pretty old video but it has some good insights: ...



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