Sales Target Implementation in SSAS Cube

This is regarding the design of Sales Target into the existing Sales Cube.

Have anyone done this kind of design before or can point me to a reference where i can learn the steps to Model the tables and create an effective Sales Target Measure Group in the sales cube to compare the actuals and target values. Also deal with problems of Target at different levels than actuals.

If i want to set the target at different hierarchy levels of dimension, should i build different fact tables, or how do i handle this. The source of my data is csv files and all the information for the target comes in this csv file.

Also i was thinking to create different dimensions ( shrunken dimensions), for from the Customer dimension in order to link the actuals and targets properly at different levels of granularity. What would be the best design approach.

I just need a kick start or an idea to implement this, please help me to get this achieved.

Hi vini,

Definitely I would build different facts in your cube - the granularity is different and you need different Dimension Relationship definitions.

CSV files are a poor source for cubes - you will inevitably need to clean and manipulate the data along the way to suit the cube - the best tool for that is a relational database.

Depending on your cube technology, technically you can relate your Targets to higher levels in your customer dimension, but the catch is that you need to have a totally robust key structure, with unique relationships at every level. This rarely exists in real data, and you tend to get tied in knots trying to invent it (and keep it running as the data evolves).

So personally I prefer the "shrunken dimension" (I'd call this a "subset dimension") approach. The "subset dimension" should have a regular relationship to your existing sales facts. Then you can freely slice both sales and targets by that dimension.

For the Dates dimension you should have clean relationships, so you can usually relate your Targets to higher levels without needing a "subset dimension".

Good luck!
Mike