Google Analytics to Power BI - star schema

[elisabeth2203]

Hi! I'm trying to get data from Google Analytics into my Power BI model. But the API from GA give me some headaches. I am trying to create a star schema to make a proper datamodel, but I dont understand how I should create the dimension-tables?

GA dont allow me to get e.g. only countries/cities without also getting a metric. If I combine the countries/cities with for instance users (metric), I don't think I will have a valid dimension-table? In other words, how did you guys manage to make a proper star schema from GA-data?

I would love to see any examples of the data model behind the Google Analytics dashboards in Power BI! 

[v-yangliu-msft]

Hi  @elisabeth2203 ,

Here we need at least a valid measure to display the data.

For more details, please refer to the video on the third-party website.

URL:https : //www.youtube.com/watch?v=FW4s43dmisc

https://community.powerbi.com/t5/Power-Query/Google-Analytics-Expression-Error-on-PBI-Desktop/td-p/1...

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@elisabeth2203 , Can you share some sample structure? Can tell you how to create

one example - getting common dimension from two tables

Bridge Table: https://www.youtube.com/watch?v=Bkf35Roman8&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=19

[elisabeth2203]

@amitchandak, thank you so much for the reply!

It seems like GA-data is build up from dimension and metrics, and its not possible for me to only get dimensions without combining it with a metric. E.g. to be able to build a star schema for this datamodel, I need atleast one dimension table for geography (country, city) and one dimension for sources. I guess I could choose to combine both country, city and users just to be able to get the data, and then delete the "users"-column and replicas in power query. But im not sure if this is a valid way to make a dimension table?

My next problem is that I dont know how to make the best relationship between the fact and dimension tables, since it is not possible to register different users with a userID (a user in this case only represents a click on the website, and in my understanding it seems like they are kind of "anonymous"). Because of this I can not make a one-to-many relationship towards the fact table, which means that my only option is to create a many-to-many relationship between e.g. city in the dimension and fact-table. But from what I have learned, a star schema should basically only consist of one-to-many relationships, and that many-to-many relations should only be used in special cases.

 Not able to get only dimensions

Datamodel

As you can see, im quite confused when it comes to making a star schema from Google Analytics data, and I would be REALLY happy for all the help I can get!