Forum Discussion
Advice Needed: Data Modeling / Table Relationship
The goal is to have each transaction reflect a location that is associated with it. Each transaction is housed in a fact table and can be identified with a location through 2 columns: Owner / Entity. A Location dimension table was created and is connected to the transaction fact table through the store number. Problem is that when a transaction is identified by a district (district is 1 hierarchy level higher than store), I cannot get the associated transaction to reflect the district. Only specific transaction items will reflect the district as the location. It is ok that there is no store number associated with certain entities (ex: TACOS + BURGERS), the goal is to have the district number reflected if the entity reflect's district. Below you will find sample data for context: Location dimension table / current fact table data output / expected fact table data output. I know that my current set up is expected and the missing data (district number) would not show but how would you approach this if the requirement is to have it reflected?
Any advice will be greatly appreciated.
Location Dimension Table: This dimension table is linked to the fact table via Store_Number
| District | District_Number | Store | Store_Number |
| Seattle (00101) | 101 | 10001 - 1 Seattle St. Seattle, WA | 10001 |
| Los Angeles (00102) | 102 | 10002 - 2 Los Angeles Blvd. Los Angeles, CA | 10002 |
| Austin (00103) | 103 | 10003 - 3 Austin Dr. Austin, TX | 10003 |
| Chicago (00104) | 104 | 10004 - 4 Chicago Ln. Chicago, IL | 10004 |
| New York (00105) | 105 | 10005 - 5 New York Ave. New York, New York | 10005 |
Current Fact Table Data: Notice only items TACOS + BURGERS does not have a district number being reflected.
| trans_id | item | district_number | store_number | entity | owner |
| 1 | Pizza | 101 | 10001 | Pizza Hut | Store Manager 10001 |
| 2 | Sushi | 101 | 10001 | 10001 - 1 Seattle St. Seattle, WA | Joe Rogan |
| 3 | Tacos | Chicago (00104) | Michael Jordan | ||
| 4 | Burgers | Austin (00103) | Elon Musk | ||
| 5 | Ramen | 105 | 10005 | 10005 - 5 New York Ave. New York, New York | Store Manager 10005 |
Expected Fact Table: Notice that items TACOS + BURGERS now reflect the district number. It is ok that there is no store number associated with TACOS + BURGERS, the goal is to have the district number reflected if the entity reflect's district.
| trans_id | item | district_number | store_number | entity | owner |
| 1 | Pizza | 101 | 10001 | Pizza Hut | Store Manager 10001 |
| 2 | Sushi | 101 | 10001 | 10001 - 1 Seattle St. Seattle, WA | Joe Rogan |
| 3 | Tacos | 104 | Chicago (00104) | Michael Jordan | |
| 4 | Burgers | 103 | Austin (00103) | Elon Musk | |
| 5 | Ramen | 105 | 10005 | 10005 - 5 New York Ave. New York, New York | Store Manager 10005 |
Current Data Model
You definitely want to use the DIM table, but there is no way I can see to relate them.
In your top table, Taco/Burgers have no district or store. So how woudl they relate to a dim table. My offered advice was to populate the district in Power Query since the district seems to be embedded in the customer location - Chicago (00104) is district 104 per your 2nd fact table.
What am I not understanding?
edhans thank you again for your support! Truly appreciated. I think you answered it for me. There is probably no possible connection for me to create by using the DIM table. I was hoping for some sort of workaround where I would be able to have the DIM[District Number] reflected on a table.
Once again, thank you so much for your support!
4 Replies
You should use Power Query to put the district number there from the entity name.
if [district_number] = "" or [district_number] = null then Text.TrimStart( Text.BetweenDelimiters([entity], "(", ")"), "0" ) else [district_number]When done it looks like this:
Full code is here.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZDRCsIwDEV/JfRJYcqm7gOmCCJMxAkiYw9hhq1sttC1Cn69sdP5oFB6k9uccGmei0gEYi8fD2SNwsjfYTi4sHGW68xqQ5CiwooM9BNFkIvZ6811tfyhvcIEIsgIrW0JMjv91AGcEp7ZaoKDrlD5XXN2jljqjrU/q1qWWGkY8apwMWYnlWWN1MJWm8sbW7C9dIZzfcHEdVaqnpu/uHWrFaSuazwSs3PAKymfM36nHZRTx7CjO5y1aSC50XTogqH6+ymxKIon", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [trans_id = _t, item = _t, district_number = _t, store_number = _t, entity = _t, owner = _t]), #"Added Custom" = Table.AddColumn(Source, "New District Number", each if [district_number] = "" or [district_number] = null then Text.TrimStart( Text.BetweenDelimiters([entity], "(", ")"), "0" ) else [district_number]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"district_number"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"New District Number", "district_number"}}), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"district_number", type text}}), #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"trans_id", "item", "district_number", "store_number", "entity", "owner"}) in #"Reordered Columns"How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.edhans thank you so much for your support! Question, are you suggesting that I do not use my Location dimension table? What I failed to mention is that my source SQL query yes has the District Number parsed out. Problem is that when I drop the Dimension District Number column into a table, the District Number is NULL. My Fact table does have the district number.
The goal is to use the dimension table and have the district number column be reflected like shown in the expected output. All of this may be confusing but I hope you understand. Please let me know your thoughts.
You definitely want to use the DIM table, but there is no way I can see to relate them.
In your top table, Taco/Burgers have no district or store. So how woudl they relate to a dim table. My offered advice was to populate the district in Power Query since the district seems to be embedded in the customer location - Chicago (00104) is district 104 per your 2nd fact table.
What am I not understanding?