I got asked a very odd question today - "How do you use virtual cubes in Analaysis Services 2005?". I was momentarily speechless (a rare thing for those that know me!). This got me to thinking that there must be quite a few installations of the previous version analysis services 2000 (AS2000) out there run by IT professionals who may have had a look at the current version and gone wibble wibble.
In AS2000 each database comprised a set of dimensions and cubes with each cube centred around a fact table. Suppose we have two fact tables reseller sales and internet sales. Each of these has different dimensions related to it for example resellers sales has a foreign key to the reseller dimension which is not appropriate for intenret sales. In AS2000 you could do two things:
This worked but was tricky to set up particularly if the two fact tables were aggregated at different levels. For example think of sales v budgets. A sale would have the day of the sale where targets might only be set at the quarter level so you need to aggregate daily sales to the quarter level before comparing them to the budget to determine variance.
SSAS changed all that, possibly a copy of Ralph Kimball's Data Warehouse Lifecycle Toolkit materialised though a wormhole in Redmond, opened at page 271, and somebody thought that'll do nicely and so now multiple fact tables are combined in a single cube where each fact table becomes a measure group as you can see from the screengrab below. The orange arrows shows where the date in reseller sales is at the level of date(day) whereas the sales targets are only at the level of quarter.
So there are only 2 cubes in the adventure works sample and one of those is for mining whereas there were half a dozen in the old and less complicated Northwind sample in 2000. Of course this means that there are tons of attributes and measures in an analysis services 2005 so the danger here is that the user gets overwhelmed wiht all the stuff in the cube where before a cube was more tightly focused on a particular function.
To reslove this we can use perspectives and security to direct the user to their usefulk stuff and we'll look at that next.
Hi Andrews that for your useful document. BTW could you please give an example of an SQL View that can Union set of Fact table. I am having trouble merging a set of fact tables while Recreating a virtual cube using your method.
Thanks once again