Stitching dimension when there is no facts available

[AlterIT]

This might be a classic requirement where users want to see dimensions all the time regardless if there is a fact reported. I am trying to get my Accounts listed for each week regardless of Sales reported against them.

As you could see in the 'Fact' for 2021 Week - 2 I don't have Sales reported for C & D. I am referring to my entire list of Accounts in another table (Master Account List) . I am looking to do this sort of scaffolding only for my latest year.

For this example Latest Year is 2021 and I wanted to see all Accounts (A,B,C and D) against all the weeks if Sales is not reported in at least one of the segments. e.g. For Week 1 (2021), Accounts A,B and C has at least one segment reporting Sales, similarly for Week - 2 A & B is reporting Sales for at least one. Scaffolded dataset may not need to have Segment and Sales information filled since it may not be appropriate. However it should have Year & Week filled so that any Account wont be missing for any week.

I have attached spreadsheet with sample data (Fact & Master dimension) also Current vs Expected screenshot how the data layout will be for clarification. Thank you for the help with this.

[pdave87]

@AlterIT hi, great question. I believe below will address your use case. We need one text input to create all dimension and once that is ready we can union master dimension with input sales data and create a summarized group for all dimension that will provide below result. #2 is master dimension with exhaustive list of Account, Segment, Year and Week (That can be achieved in similar method if the sources are different)

Happy solving 🙂

[AlterIT]

@pdave87  Thanks for helping on this. One challenge I see with this one is I need to add Week as part of master dimension which is not really I am looking for. I need to allow weeks to reflect based on data availability. e.g. Lets say I have fact available only until week 2, I should only be stitching the data until Week 2 and not beyond that. 

[pdave87]

@AlterIT I am happy to be part of this community & learning everyday 🙂

Back to your solution: we can use filter condition on week based on requirement <= 2 before the step in creating output. Will this address your final output? We can also address this via standard macro and pass it on to users requiring this output within the organization 🙂 But that's more advanced step we can look into if required.

Regards,

Pratik

[AlterIT]

What I really meant to say is , that week is dynamic based on fact availability. Let’s say we have fact until week 10 , this data filling process need to happen only until week 10 and so on 

[pdave87]

That can be considered as iteration limited to x number, we can address that through macro as an exit condition?  

[Christina_H · Accepted solution]

Is this what you're after?  Making sure that every account name is included in every year/week in the data.

[AlterIT]

@Christina_H  That's what exactly I was looking for. 

One addition to the solution I am looking for is, how can I do this stitching/scaffolding only for the latest year available in the Fact. In this example this Account stitching should only work for 2021. So finally historical data would be "as is" and latest year's data will be scaffolded with missing accounts.

Thank you for the help !!

[AlterIT]

@Christina_H  I think I got that additional step based on your initial solution. I have added a filter for Max Year and in final "Join"  I have taken all 3 outputs and combined.

Please add in if there is a better way to achieve it.