April 25, 2012 at 9:55 am
Business Requirement: Providing a self sufficient self service tool that power users or leadership could use to derive performance data.
Tools in discussions: Report Builder 3.0 would like to be used in conjunction with SQL 2008 and SSIS 2008 to build these self servicing reports.
Current architectural setup: We have a reporting database environment set up with 5-8 databases or components. This is highly normalized relational data set up that is refreshed on a hourly frequency.
Proposed set up: The proposed set up is to create a bulk copy of the reporting data on a nightly basis to bring them over to the self service server and then create datamarts out of it. The creation of datamarts will be big individual transactional tables with highly denormalized data in it that will be added to report builder as data sets. In some cases views will be created out of the datamarts to add them as data sets for the report builder tool.
Can you guys critic and provide alternative suggestions about the design methodology of building data marts for servicing report builders.
April 26, 2012 at 6:33 am
MaverickMan (4/25/2012)
Business Requirement: Providing a self sufficient self service tool that power users or leadership could use to derive performance data.Tools in discussions: Report Builder 3.0 would like to be used in conjunction with SQL 2008 and SSIS 2008 to build these self servicing reports.
Current architectural setup: We have a reporting database environment set up with 5-8 databases or components. This is highly normalized relational data set up that is refreshed on a hourly frequency.
Proposed set up: The proposed set up is to create a bulk copy of the reporting data on a nightly basis to bring them over to the self service server and then create datamarts out of it. The creation of datamarts will be big individual transactional tables with highly denormalized data in it that will be added to report builder as data sets. In some cases views will be created out of the datamarts to add them as data sets for the report builder tool.
Can you guys critic and provide alternative suggestions about the design methodology of building data marts for servicing report builders.
Above description is too generic - mostly consultant style gibberish.
If business requirements ask for a DSS then design a star-schema datamart for each domain you have to report on then design an ETL process that keeps such datamart up-to-date.
If you want specifics, specific questions may help.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 26, 2012 at 7:08 am
Just one thing that jumped out at me:
Current architectural setup: We have a reporting database environment set up with 5-8 databases or components. This is highly normalized relational data set up that is refreshed on a hourly frequency.
The proposed set up is to create a bulk copy of the reporting data on a nightly basis
If your users are used to having data that is at most an hour behind are they really going to be happy with going to reports that are 24 hours behind?
I would agree you need to create a data warehouse and look at incremental loading stratagies.
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
April 26, 2012 at 7:38 am
Thanks for the suggestions, I agree it was extremly vague description of the initiative unfortunately it is in the conceptualization state. Do you suggest us going through some proof of concepts or case studies before we implement a datawarehouse with incremental loading strategy.
April 26, 2012 at 7:51 am
The first step I would take is to sit down with the Business and the users and establish exactly what they want / need. This will help you establish what your requirements actually are, for example it may be that they are happy with data that is 24 hours behind possibly negating the need for you to look at incremental loading (if you are happy doing a full load overnight)
It will also be a huge help if you learn and understand the businesses processes (if you don't already) and consider the volumes of data you will be processing
After this I would then develop your proof of concept
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
April 26, 2012 at 7:54 am
There are LOADS of links on the web here is one for the Kimball website, I'm not saying this is the definitive place to look for ideas and strategies but certanly has some information and tools that you may find helpful
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply