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

[gettingthere2]

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]

BUMP

Anyone?

[hang]

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.

[Sponsored content]