Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Great challenge: mdx or business intelligence in SSAS? Rate Topic Display Mode Topic Options
Author
 Message
 Posted Sunday, February 14, 2010 10:09 AM
 Forum Newbie Group: General Forum Members Last Login: Thursday, August 08, 2013 2:40 AM Points: 3, Visits: 77
 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 BSales Dealer A (region A)Sales from:region A -> region Aregion A -> region Bregion A -> region CNumber of sales inside region: 1Sales Dealer B (region B)region B-> region Bregion B-> region Aregion B-> region CNumber of sales inside region: 1For 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
 SSC Eights! Group: General Forum Members Last Login: Yesterday @ 3:10 PM Points: 927, Visits: 5,709
 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

 Permissions