Advice for Schema for a Promotional Database

Power BI Data Model
1

Hello All:

Still a newbie with Power BI. Been focusing on elevating my Dax skills and should have spent a little more time on Database Schema.

I know I am making this harder than it needs to be, and I am sure you experts know what to do in this case.

I am trying to create a promotional database.
The problem I am running into is that the promotions are the same except that they span multiple accounts.
Please see attached example of Data fields.
So, I will create the following tables:

  • Product Table (ID, Product Name, Descriptive Attributes)
  • Account Table (ID, Account Name)
  • Territory Table (ID, Territory)
  • Date Table
  • Promotions table (ID, Promo Name, Product ID for products included in promo, promo start and end date) I attached a sample table.
  • Sales Fact Table (Product ID, Account ID, Territory ID, Date, units)

Not sure how I should create a relationship between Promo and Sales table since there can be multiple products in the same promotion but for difference customers.

Any help and advice would be greatly appreciated.

Thanks to all.

KG
PromotionDimensionTable.xlsx (9.6 KB)

2

Hi @KGB,

Maybe someone has different idea,
but if I got it - you try to achieve to see sales with promo effect.

Maybe you could have in Sales Fact : ProductID, AccountID, TerritoryID, Date + PromoID + Facts

In Promo Lookup/Dimension -Id, Promo name, Start, End date
Maybe you can also record for No promotion situation.

And if you need separate product_id, promo you can add in separate table promo_product_mix (sorry I am currently ran out of ideas).

Good luck.

3

Hi @KBG, did the response provided by @mspanic help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

4

Hi @KBG, we’ve noticed that no response has been received from you since May 26th. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

5

Hi @KBG, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.