February 14, 2010 at 10:09 am
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
February 15, 2010 at 7:17 am
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply