Hi, i’m just getting started with the Power BI connector and discovering the problems with mixing event and table data, such as session and visitors not summing correctly. But at the same time, I’d like to get things like event categories which happen in sessions, which in the documentation is a mixing of the tables. Is there a way to load the 2 separate tables, then merge on a dimension?
Hi @cro1100! You can merge event and session data based on the Session ID. It is added by default to every query you run in Power BI, so you’ll be able to join the data from the Sessions and Events table.
You can also use the Reports → Queries section in the PowerBI connector in such cases when you want to get ready reports, the same as the ones visible in Piwik PRO Analytics UI (but bear in mind that you won’t get the ‘Total’ number visible in the PPAS UI at the very top of the report - it is something that we’re planning to add in the future).
emilia, i’m not seeing session ID in the reports query i’m using, nor the date. do i have to make queries in the raw data for this to work?
@cro1100 If I understand correctly what you’re aiming to obtain, I guess in that case the Queries API won’t be enough (there are some limitations coming from the Analytics API - like not being able to retrieve the “Total” number) In such cases, you can retrieve all raw data for sessions and events, and you’ll be able to work on it like on a standard database and run any query.
And for your questions - the date is available in the Queries, you just need to know that it’s called ‘Timestamp’ there (I know it’s not intuitive but the Analytics API forced us to use that name - we mentioned that in the manual Microsoft Power BI Desktop integration | Piwik PRO help center)