December 17, 2008 at 9:23 am
Just beginning to learn SSAS.
I purchased “Delivering Business Intelligence with Microsoft SQL Server 2005” by Brian Larson. On page 53 he says,
“A UDM is a structure that sits over the top of a data mart and looks exactly like an OLAP system to the end user. One of the major advantages of a UDM, however, is that it does not require a data mart. You can also build a UDM over one or more OLTP systems.”
On page 59,
“The UDM allows OLAP cubes to be built directly on top of transactional data. The UDM does not need a strict star or snowflake schema data mart as its data source. Instead, any well-structured, relational database works just fine.”
Does anyone have any experience in building a cube on top of transactional data? If so, how were the results?
As an experiment I built a cube with 3 dimensions (a customer table, a region table, and a time dimension from an invoice table) on a very small data mart with no star or snowflake schema. The tables were normalized. I browsed the cube in the SSAS project, and it appears to have worked.
In the future I may build a relatively small cube (100 customers x 4 business units x 50 products x 6 regions x 100 sales reps x 2 years).
As I want to keep things as simple as possible because it may be just me doing this, I am considering creating a data mart using T-SQL (no SSIS) without a star or snowflake schema, and building the cube on the normalized tables in the data mart. I will also consider creating a star schema, but I will ask questions about that in another thread.
Does anyone have any helpful comments?
Thanks.
December 18, 2008 at 5:38 am
You can build the cube on top of the transactional system or from multiple systems like is stated in the book. I would not recommend placing the cube directly on top of a live production transactional system though because of impact on the system during processing or accessing the data depending on the storage option you are using in SSAS. The cube you are creating is extremely small, but still I would look at implementing a star/snowflake dimensional model (denormalized) using surrogate keys (not the transactional natural keys) for unique identifiers. Putting the cube on top of a structure like this that is in more of a offline and reporting structure (on being used to load, update, and process against) will provide a better solution in the end.
Obviously this will require additional work and time to create this option, but in the end it will be worth it. Typically it seems that when you create a sample or POC like this and others see it they will want to start using it or will start to request additional items. Before you know it this could grow very quickly and become mission critical through the enterprise. This is usually what happens with a basic Excel report that turns into a massize Excel spreadmart with mission critical reports and data.
I would also recommend taking a look at additional books for guidance in regards to SSAS (I haven't read Brian's unfortunately, but have read others) - http://www.ssas-info.com/analysis-services-books. I would recommend "Applied Microsoft Analysis Services 2005 : And Microsoft Business Intelligence Platform by Teo Lachev".
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
December 18, 2008 at 5:56 am
Thanks for your advice, Dan.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply