SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Great challenge: mdx or business intelligence in SSAS?


Great challenge: mdx or business intelligence in SSAS?

Author
Message
huning
huning
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 89
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
Greg Edwards-268690
Greg Edwards-268690
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1513 Visits: 8483
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search