April 5, 2014 at 7:55 pm
Greets!
I'm an accidental DBA that's helping design a new BI solution for my current employer. Although this sounds kind of scary, I do have 7 years of software development experience behind me. The original intention for me was to simply manage the project, but have taken a heavier interest in helping design too. Thus, I had a few questions for the BI community.
My current database is a well structured OLAP data mart in a snowflake schema. I'm dealing with about 1 GB of data a day that has to be imported into this data mart and be processed for reporting. The process for reporting is the area where my questions lie for this thread.
I'm stuck between the best option for processing raw data into aggregated data for reporting.
For example, let's say there is a request for a few canned reports to be developed that will be used on a daily basis that aggregate this data when it's available for the day. My solution for this is to develop a stored procedure that pulls all the data needed, aggregates it and dumps the results into a physical table. Then I create a new report for SSRS that queries that table for the data it needs to display to the end user.
The reason I chose this solution is for a few reasons. The first reason is because the data source is in it's rawest form. There are many Fact tables with about many dimensions used for each query. Certain elements of that data need to be transformed before it ends up in the end users hand. Therefore, if I process the data before the end user queries it, I can make those transformations with an ETL process before aggregation is even done within a new schema just for reporting. The second reason is for speed. Due to the amount of data being aggregated, simply setting up an view or aggregating the data off the query does take some time. If I preprocess this aggregation over night, dump the results into a table, then selecting the result is lighting fast.
That all said, is there a better way? Is this the right way? Am I being silly?
I still feel good about this approach because I'm basically doing the same methods as an OLAP Cube. I define a cube, a bake the sucker (aggregate the data) and it's ready to be queried by the end user for reporting. I'm just not using the technology just yet. But, I want too. However, that's really part of my question too! :hehe:
Current Technologies:
SQL Server 2008 R2
SSIS/SSAS/SSRS
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply