Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Google Analytics

Hello,   I am trying to connect Power BI to Google Analytics. I have the connection set up and can bring through tables but I am having trouble finding a good Primary key and foreign key to connect...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Anonymous ,

 

It is hard to use single date/time dimension as relationship keys to link different tables.
In my opinion, I'd like to suggest you to add a calendar table to link these tables based on date fields.

 

For time part, you can extract hour part to build relationships.

 

Regards,

Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hello Xiaoxin Sheng,

 

I am a little confused. Would you mind clarifiying on how you could pull off that relationship with a calendar table?

 

Thanks!

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Anonymous ,

    Actually, if you try to create relationship based on date/time, it means you need a whole datetime table, it existed huge amount of records.(time table include 'hour', 'minute', 'second' dimension has 86400 row per day) 

    So I suggest you to add calculated columns to these tables to store date part and time part. Then you can create two relationships based on date dimension and time dimension.

    Regards,

    Xiaoxin Sheng

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      The only flaw I can see is that Google Analytics does not allow me to pull in seconds. I can only pull in Hour and Minute. Do you have any other ideas on how to connect using the Client ID? Right now Google Analytics does not allow me to pull it in but I am wondering if anyone has found a better way to connect their data.

       

      Also, if we only use Hour and Minute we get duplicate values when looking at our primary key.