duplicated facts against multpile dimensions in MS OLAP control (excel/SSMS)

View previous topic View next topic Go down

duplicated facts against multpile dimensions in MS OLAP control (excel/SSMS)

Post  gettingthere2 on Tue Jul 26, 2011 11:14 am

Probably an easy question for some..........

I want to see the same fact side by side in a report in excel. i.e.:



If we have 30 sales in our fact table which has 2 dimensions - Channel and Promotion Type





Sale Channel A Sale Channel B Promotion Type A Promotion Type B
Sale count 14 16 8 22


But when I drag the second dimension onto the olap control I get this:

Sale Channel A Sale Channel B
Promotion Type A Promotion Type B Promotion Type A Promotion Type B
Sale count 6 8 2 14






Is it even possible to achieve this in excel? If so how?

gettingthere2

Posts : 5
Join date : 2011-06-30

View user profile

Back to top Go down

Re: duplicated facts against multpile dimensions in MS OLAP control (excel/SSMS)

Post  gettingthere2 on Thu Jul 28, 2011 7:40 am

BUMP

Anyone?


gettingthere2

Posts : 5
Join date : 2011-06-30

View user profile

Back to top Go down

Re: duplicated facts against multpile dimensions in MS OLAP control (excel/SSMS)

Post  hang on Thu Jul 28, 2011 8:45 am

I can see one way to achieve that. You could consolidate sales channel and promotion type into a single dimension (Cube_Dim) that stores column names on two dimensions in a single attribute, something as follows:

Cube_Dim
SK Name
1 Sale Channel A
2 Sale Channel B
3 Promotion Type A
4 Promotion Type B

Now create a fact view based on the fact table by union as follows:

select cd.SK, a.SaleCount
from
(
select 'Sale Channel A' as Cube_Dim_Name, SaleCount (1 for base level)
from fact f
join dim_channel d
on f.ChannelKey=d.ChannelKey
and ChannelName='Sale Channel A'
union all
select 'Sale Channel B' as Cube_Dim_Name, SaleCount (1 for base level)
from fact f
join dim_channel d
on f.ChannelKey=d.ChannelKey
and d.ChannelName='Sale Channel B'
union all
select 'Promotion Type A' as Cube_Dim_Name, SaleCount (1 for base level)
from fact f
join dim_promotion p
on f.PromotionKey=p.PromotionKey
and p.PromotionType='Promotion Type A'
union all
select 'Promotion Type B' as Cube_Dim_Name, SaleCount (1 for base level)
from fact f
join dim_promotion p
on f.PromotionKey=p.PromotionKey
and p.PromotionType='Promotion Type B'
) a
join Cube_Dim cd
on a.Cube_Dim_Name=cd.Name

Now you can just drop the new dimension name onto the column row and fact onto the centre, you should be able to see what you want. Not so pretty, but it works.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: duplicated facts against multpile dimensions in MS OLAP control (excel/SSMS)

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum