I need to slice the data based on Multiple Flags on the Transactional record. The Flags are on the same dimension but not in the same hierarchy.
Below is the sample transnational data and corresponding fact table that we have generated. I need to write MDX query in order to generate report as whoen in the Expected Report below.
Here PrintMail, App Notifications, SMS and Email are various types of communication channels through with the message was delivered. Some of the messages are delivered through multiple channels.
Transactional Table
Date MessageCount PrintMail AppNotification SMS Email 1/1/2019 1 1 1 1/1/2019 1 1 1 1/1/2019 1 1 1 2/1/2019 1 1 1 2/1/2019 1 1
Fact Table
Date MessageCount PrintMail AppNotification SMS Email 1/1/2019 3 2 2 1 1 2/1/2019 2 1 2 0 0
Expected Report
All 1/1/2019 2/1/2019
Message Count 5 3 2
PrintMail 3 2 1
AppNotification 2 1 1
SMS 3 2 1
Email 1 1 0
My question is: how can I show various channels are rows here? I am not sure if this will require a new dimension and if yes, how would that look like.
By the looks of your Fact table the diffrent channels are your facts. If that is the case then you dont need a new measure. All you need to do is in your MDX query put the measures on row axis and the date on column axis. The Query should look like
Select {[DimDate].[Date].members} on columns,
{[Measures].[MessageCount],[Measures].[PrintMail],
[Measures].[AppNotification],[Measures].[SMS],[Measures].[Email]}
on rows
from [YourCube]