Forum Discussion
Need Help with Table Relationships
Hi everyone, struggling a little with optimizing relationships between dim/fact tables in my report and would appreciate some advice.
Context:
The report visualizes weekly product performance, measured by sales and web metrics (e.g. page views, conversion). Sales and web metrics data are imported from two separate databases. Model below is a sample indicating key columns per table that link to others.
Table relationships:
dim_region - linked to fact_sales by SalesDBCountryCode and fact_webMetrics by Country ISO (e.g. US)
dim_date - linked to fact_sales by Week (can also use Date) and fact_webMetrics by Date
dim_product - linked to fact_sales and fact_webMetrics by ArticleKey
dim_promotion - unsure what's the best way to link
Visual filters (highlighted in yellow in image):
dim_region - CountryName (e.g. United States, Australia)
dim_date - Week (e.g. 2024W05)
dim_promotion - PromotionKey (e.g. Summer Sale)
Questions:
1. dim_region - would the filter work fine with the current relationship setup to both fact tables, or is there a better way to go about this?
2. dim_date - fact_sales database contains both Date & Week data, however fact_webMetrics only contains Date. As I'm only working with Week for visual filter, would it make any sense to import by Week vs Date in fact_sales to reduce data size? And would it affect how relevant data from fact_webMetrics is retrieved (relying on dim_date mapping of Date->Week)?
3. dim_promotion - this essentially contains a list of promotions (e.g. Summer Sale, Weekend Promo, etc), their active period (can be Week or Quarter), and the products within the respective promotions. How do I link this to the other tables if I will be using visual filters for PromotionKey and Week (e.g. showing sales and webMetrics data for products in Summer Sale during Week 2024W06)?
4. Would both fact tables be properly "linked" by the common dimensions? i.e. based on visual filters, matrix tables in report will display product sales (e.g. pieces sold, gross/net totals) and corresponding web metrics (e.g. page views, add-to-carts) for the products.
Thanks in advance!
3 Replies
Hey xeraphim,
Answering in the order of your questions:
- Yeah that's fine. Dimensions can and should be used with multiple fact tables, if you want to look at both fact tables in the same visual.
- Week is probably unnecessary in your fact_sales table. Hopefully your relationship is on dates.
- Without any further details, I would probably store promotion data in the two fact tables themselves, as applicable. Since you want to be able to use promotions as a filter, I would then also keep a barebones promotion dimension table (that may not be much more than primary key and name).
- Hard to know without seeing the actual columns used in the relationships themselves (ex: see #2), but everything looks fine for the most part. The one relationship I would want to think more about is dim_region to fact_webMetrics; having a many to many relationship is far from ideal. My first question is why is the relationship with fact_sales 1:M but the relationship with fact_webMetrics is M:M?
Just my thoughts and something to think about! I think you have a really good start and are on the right track. 😄
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)Thanks for the quick response!
#2 - The current relationship is on week. Both week and date exist within the fact_sales database; I opted to load sales data by week instead of date under the impression that it's a smaller dataset to import (and visually I'd only be showing sales by week). Does this have any implications in connecting fact_sales with fact_webMetrics through visual filters applied on dim_date, seeing that the fact_webMetrics dataset only has date data and not week?
#3 - Store promotion data is currently imported from a separate excel file - would I just be importing this, and merging data to both fact_sales/webMetrics tables through merge query based on matching ArticleKey and Week? In this case the store promotion dates are only on week basis so a bit confused as to how I'd link this to the fact tables as well (i.e. fact_sales has week but webMetric doesn't)
#4 - dim_region table looks like below, where SalesDBCountryCode is the relationship to fact_sales and CountryISO is the relationship to fact_webMetrics. Both are unique to their respective fact tables and not natively available in the other. SalesDBCountryCode is straightforward as the values are unique and identifies country-channel. This however is not available in fact_webMetrics, with the only identifier being CountryISO and the table only contains Online channel information (i.e. no data should show up if I filtered for sales data for Store). I wanted to set up visual filters by CountryName and Channel, hence building the dim_region table as such. Is there a better way around it?
SalesDBCountryCode CountryISO Channel CountryName US-S-001 US Store United States US-O-105 US Online United States AU-O-502 AU Online Australia AU-S-572 AU Store Australia #2 - If you never care to do any analysis on date, aggregating at the weekly level is fine. Aggregating at different levels between the two tables will have no impact because your dimension table has both Date and Week. However, that begs the question: Why not aggregate your web metrics by week too?
#3 - Can you not add week to the web metrics table, even if you're not aggregating at that level?
#4 - Ah I see. I think it's not ideal but not as problematic as normal because your many-to-many relationship is single direction and between a dimension and a fact table. One possible way to change it to a one-to-many relationship is to bring in SalesDBCountryCode to the web metrics table (presumably it doesn't matter which one) to have the relationship on the unique SalesDBCountryCode column to both fact tables.
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)
