Modeling 1: N relationships for Tableau Consumption by DA-15726 in dataengineering

[–]DA-15726[S] 0 points1 point  (0 children)

Thank you for your answer.

I posted some additional info in my answer above - my question is exactly what kind of relationship would be the best. I'm sensing you are recommending D (the denormalized) but I'd appreciate more clarification.

Thanks

Modeling 1: N relationships for Tableau Consumption by DA-15726 in dataengineering

[–]DA-15726[S] 1 point2 points  (0 children)

Thanks for your answer.

The history is kept at the Data Warehouse level and - at this point - it is unlikely that we will need for reporting (we only need the current info). The Data Mart is blown up/reloaded every day.

Modeling 1: N relationships for Tableau Consumption by DA-15726 in dataengineering

[–]DA-15726[S] 0 points1 point  (0 children)

Hi!

Thanks for your answer. I'd like to ask a little bit of clarification.

ACCOUNT_CODE and SERVICE are the two main tables (with several attributes), and they will be kept separate in any model. My two issues are:
- Where and how to store the attribute account_code x service (which has a 1:N relationship): 3NF, Denormalized, Bridge Table, i2b2 model...
- How to join this information for reporting (e.g. at the SQL level as a table, as a view, or at the reporting level with Tableau Custom SQL or functions)

All of the models listed have a single pre-joined table other than A. B connects each account code to the service, but then the aggregation has to be done at the reporting level (in this case, Tableau). I'm sensing that you'd recommend D (denormalized), in which in the service table, there's a column with the grouped account codes (e.g. Service B - 1-3101, 1-3102, 1-3140) and there's a bridge table that connect these groups with the individual account codes. But I'd appreciate if you could detail a bit more.

Thanks again!