Monday, May 20, 2013

[SQL Server Data Warehousing] Building a database reporting/dashboard solution


Hi,


We are in the process of building a MS SQL 2012-based data warehouse which stores data from 3 separate transactional databases (sales data). We have gotten to the point where the data extraction is automatic and the data is being added to the respective tables without error.


We are now at the point of building the reports and are having huge problems due to timeouts etc. As soon as any grouping is added to queries, the whole thing grinds to a halt.


At this stage, there are a very few indexes in the report source tables. In addition, records are by Date, and the smallest reporting date unit is Month.


We are at a bit of a junction point where we need to decide:


1. do we add more indexes


2. do we build some summary tables that refresh on a nightly basis (this timing is adequate for the reporting requirements


3. do we introduce something like SSAS


In terms of data volume, the main sales transaction table currently has about 80mill records, growing at a rate of about 8mill per year.


We are relative novices so are unsure which direction to head. Is the choice obvious for experts in this area?



raeldush



.

social.technet.microsoft.com/Forums

No comments:

Post a Comment

Search This Blog