Layering datasets with different grains
I have two datasets, let's call them 1 and 2, and I want to layer them on top of each other. Typically I do this by ensuring they have the same grain and are writing to the same fact table, and then load them incrementally in different processing groups. In my current scenario however, dataset 1 can grain to a/b/c, and dataset 2 can only grain to a/b. What's the best way to handle this? I'm trying to avoid a union if possible :) Thanks!
Answers
-
Dan,
You didn't state whether the 2 data sets are the same subject matter, just different granularity. It isn't a best practice to force a grain. It's helpful to understand a live example as the lowest grain [c] represents. What you've described is a dimensional model where [c] is day-level granularity and data set 2 is an aggregation to month, for example. This modeling is an acceptable design.
This post requires a deeper-dive and show and tell to fully understand the requirement for the best guidance. If you wish to have a conversation off-line, please email me.
0 -
Hi Diane, it's two datasets from different systems of the same subject matter, i.e. sales from system 1 and sales from system 2, but system 2 sales can grain to other dimensions in system 2 that system 1 cannot. Is it safe to still grain system 1 sales to those dimensions without having the keys available, to maintain the same fact table?
0 -
Not sure I get the picture, but you can declare 2 datasets and populate these with multiple write-command() from a nested SQL-statement from almost every report-extension-level.
0
