June 16, 2018 at 4:46 am
Hello
I have one date dimension and one campaign dimension. Every campaign has a start and an end date. The campaigns are repeated every year, but they start on different days.
Now I need to have an matrix like that:
Columns = year
Rows = (Day1, Day2, Day3, .. of the campaign period)
A MDX query is possible. But I need to show the data in PowerBi, so I can't use a MDX query.
Does anybody have an idea how to design the cube, to be able to show the data like that?
June 17, 2018 at 4:01 am
I work in advertising myself. I have a DimCampaign table in my dataset (not in a cube). Do you not have the start and end date of the campaign in the actual dimension table itself (i.e.: in the DimCampaign table)? If so, you just JOIN your fact table to that campaign on the campaign id and do something like WHERE fact.date >= to campaign.startdate AND fact.date < campaign.enddate to list all dates between each campaign start/end date of the dimension. If your fact table has a list of records by day, then it should list every day within each campaign range. Thus, if you have 365 days per campaign regardless of start/end date, you should return each record as the day for that year.
If your data is not by day, then it's a matter of getting it by day. Same with getting start/end per campaign in the campaign dim. In the event you don't have start and end dates in your DimCampaign table, then you can create one by simply grouping your fact data by the campaign id with MIN() and MAX() dates as two separate columns. Can update your DimCampaign table with that or do it as a JOIN (SELECT).
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply