Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Google Analytics to Power BI - star schema

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! 

Show and Tell

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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!