Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Great challenge: mdx or business intelligence in SSAS? Expand / Collapse
Author
Message
Posted Sunday, February 14, 2010 10:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 10, 2014 8:34 AM
Points: 3, Visits: 78
Dear specialists,

I have a real challenge which I cannot solve. Hopefully you guys can help me...

The problem concerns car dealers and sales inside and outside their regions. It's not possible for me to get the correct sales when I drill up in my dealer dimension. This is because the region 'increases' when I drill up and more sales become inside this region. It's not a simple sum aggregatefunction. I will explain this using an example.

I have a dimension car dealers. This is a parent-child dimension with a maximum of four levels.
Country -> Holding -> Group -> Dealers. But not all the dealers have a group as parent. Some of them are directly underneath a country.
All dealers have a unique region (a couple of zipcodes) and all of them do sales inside and outside their region. I created a measure with the number of sales a dealer does inside its region. This works perfect for that level (the lowest), but doesn't work for higher levels. The case is, if I drill up, the number of sales in a region is not a simple sum of all dealers underneath because the region of a group are all regions from its childs. This means that the number of sales in a region also increases.

For example:
Group ZZ has two dealers A and B

Sales Dealer A (region A)
Sales from:
region A -> region A
region A -> region B
region A -> region C
Number of sales inside region: 1

Sales Dealer B (region B)
region B-> region B
region B-> region A
region B-> region C
Number of sales inside region: 1

For group ZZ the number of sales in its region is: 4
(Because A and B is 1 region for group ZZ, sales from A-> B are also counted as sales inside the region. )

My facttables contains all sales on the lowest level (dealer).

I do not have a clue how to solve this. Maybe in MDX or with adding some business intelligence to the dimension dealer? I work with sql 2005/2008.

Thanks for taking the time to read this and hopefully somebody can give me hint where to look,

Regards Frank





Post #865290
Posted Monday, February 15, 2010 7:17 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 4:14 AM
Points: 1,164, Visits: 6,269
The Dealers have sales, both inside their region and outside their region. I'd represent this with different measures, and you can add them up for total sales for the dealer.
And you may want to have 2 heirarchies - 1 strictly by region, another strictly by dealers.
This would be a bit clearer to the users when navigating the cube.

I'd assume that the dealers assigned to just country get put in an unknown bucket of some kind at the lower levels.
Greg E
Post #865519
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse