Data Modeling Issue (Please Help)

[mm14222]

Hi All, 

I have been trying to build a data model from multiple datasets (excel files) in power bi to help connect all advertising data with sales data, and be able to use time intelligence feature to calculate sales of this year and comparing vs last year. 

The Problem : The data model is not working and date feature is working on one set of the data either Advertising dataset or Sales Dataset, however when i plot them into a single table one can be correct and other not. 

Question : based on the data model illustrated below, why i cant use the data from the Calendar between Data available in the Application table and Google Ads Table, and what would be the solution for that.

Note: I still have other sources such Facebook, Instagram, display, etc.. so the table should be much larger however i'm tying to fix it for one channel and later expand. 

Basically i have the following tables (I'm including a detailed image to visualise the model) 

- MASTER LIST : Countains list of all applications, these application have all the information about people who applied for service

- Booked Table : Countains list of all applications that have bought the service

- Reject Table : Countains list of all applications that has been rejected or failed

- Calendar Table : Dynamic Generated Date Table to help in time intelligence feature, and feeding from Master List Application Date. 

- Google Ads Table : Countain all the campaign level Data 

- Drill Down Table : Countains a drill down hirarachy for the Campaign Name and Ad Group Name with Campaign ID and Adgroup ID (Note this is the only table that has the campaign and Adgroup Name, all other table may have Campaign ID and Ad Group ID) 

What i'm trying to achieve is a table that has a drill down down for 3 categories : 

Campaign Name

Ad Group Name

Keywords 

and the metrics should be generated from the model. 

Data Model Details : 

Data Model Illusration : 

[sanimesa]

@mm14222If I may make a suggestion - sometimes it makes sense to start from the very basic. I'd first create a denormalized table with all the desired dimension columns and fact columns and see if the data is consistent. Then deconstruct the model from this.

[AllisonKennedy]

@mm14222  What's your experience with relational data models? Unless you're a PRO, stay away from Many - Many relationships. 

I haven't read your post in full detail, but my initial advice is to DELETE the three Many-Many relationships that you have, and relate all four of those bottom tables (lets call them 'Fact' tables) to each one of the top tables (lets call them 'Dimension' tables) using a ONE to MANY relationship where possible.

Once you've done that, you should see your dates behaving more as expected. 

Keep us posted though, as this will no doubt change some of your other visualizations and calculations, but in general Many-Many is a special use case ONLY. 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[mm14222]

Thanks so much for your kind advise, i have tried it, i tend to avoid many to many, however for strange reason Power BI is only allowing this type of relation, which i dont understand why. 

is there a way i can share more info without having to share the entire deck to enable you to see what is going on? 

really been working on this for 48 countinous hours, and i'm not sure why it cant be fixed regardless the change in strategies.

[AllisonKennedy]

@mm14222  You can also annotate on your data model view what you have tried and which columns you are using to create the relationship. Please expand the tables so we can see all the column names if possible, I think one of the tables was maybe cut off. 

Ultimately it comes down to understanding your data and how it all relates, which you will know better than us but we can assist. Can you confirm that you have tried relating the tables to a dimension table? 

If you can mock up something similar to this image here that shows us more detail about how each table is connected and explain why, then we can suggest ways you can form the needed dimension tables and get this working properly.

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[sanimesa]

@mm14222It probably means you do not have unique values in the dimension tables. For creating one to many relationship, you have a dimension table and a fact table. The relationship key must be unique in the dimension table.

If you do not have unique values in the dimension table, then you will need to follow some technique described here:

https://docs.microsoft.com/en-us/power-bi/guidance/star-schema

Also please read:

https://docs.microsoft.com/en-us/power-bi/desktop-relationships-understand

I would not recommend posting anything proprietary or confidential but if you can post a limited amount of sample data, perhaps experts here can provide more detailed assistance.

[mm14222]

Thanks so much for your kind guidance, and apologies for the late response, been crazy days trying to solve this issue.

The objective of this data model is a standard business problem, as most of the businesses today have online campaigns, so they must look into ways to bridge the data between them? The objective is brining the following Dimensions and measures into and connect with the application table, also i need to keep this process sustainable, as if i need a lot of pre-processing then the table cannot be refreshed on daily or weekly manner:

Google Ads : 

Date | Campaign Name | Ad Group Name | Keyword >> Impressions | Clicks | Cost

Application Level Data : 

Date | Application Number | Campaign Name | Ad Group Name | Keyword | Cost 

Cost per Application would be Sum (Cost) Campaign Name | Ad Group Name | Keyword by Date 

I'm also trying to migrate this into a star model, however, that would require a change in the complete data structure especially the application table as it has over 100+ columns.

During a conversation with a college of mine, she advised me to concatenate CampaignID+AdGroupID+Keyword at both sides to create a matchkey, but if the matchkey is forcing the lowest level (keyword) then what would happen to the higher such campaign or Adgroup 

this is what i will be testing today. 

Thanks for all your help and guidance guys.