I have the following scenario and aren't sure of what approach I need to take to solve it. Any advice would be welcome (im very new to warehousing).
Scenario: I have a high transaction OLTP database (SQL Server) containing 20+ inter-related tables. It is all highly normalised. Over a year I am looking to accumulate something like 10+ million rows of information (most likely more).
The database is used extensively by an application (ASP.NET) which allows users to view (and in some cases update) the information in the database. The majority of the data is inserted by BizTalk.
Once an entire transaction is completed (which may take for example 2 weeks), it will not be changed in the future, but will still need to be reported on. I.e. View All Orders in March 07
Problem: As my data volumes grow, the performance of this database will undoubtedly begin to crawl, for both selects and inserts/updates. To solve this, I would like to move the completed transactions into an achieve (i.e. my datawarehouse) that will store the information in a denormalised state which the application can continue to use to perform queries (which I hope will be very fast).
I understand this isn't a typical use for a warehouse, namely I am not summing numerical figures or interesting in aggregating information based on measures/hierarchies.
Is what I want to do a correct approach, or can someone provide a better solution?
Any help would be greatly appreciated,
TM
Hello. You can build your data warehouse as highly normalized as you prefer. You can control the aggregation of information in one or several data marts that you can build from views or in tables in a new database.
Data warehouse and data marts(aggregated denormalized) informations do not have to be the same.
In the data warehouse you will normally remove some tables in order to keep the number of relations down to a minimum. You will also translate OLTP-columns to more business like names.
Still, if you build a copy of the source system in the data warehouse you will be able to remove historical transactions from the source. That will help performance in the source system.
Also, analytical queries will not compete for resources with transaction registration.
HTH
Thomas Ivarsson
|||
So Thomas,
If I were to build a denormalized warehouse could I then build a UDM cube on top of it without building an OALP fact/dimension type structure?
|||
Hello JohnSLG. Yes, I think you can. You can build cubes on top of OLTP-systems.
You can have all information like dimensions, hierarchies and measures in one table and build a cube on that.
Best practice, however, points to a data mart on top of a data warehouse.
Kind Regards
Thomas Ivarsson
|||
Thank you for your response, greatly appreciated.
Could you also point me to where I can get a grounding in general warehousing theory? Then build onto this with Analysis Serviecs.
Kind regards
TM
|||Here is a good link: www.kimballgroup.com
Regards
Thomas Ivarsson
