Forum Discussion
Dataflows, Azure and Azure SQL processing large dataset
Community ChampionWhat's the cardinality like on the dax query column? If people are just accesing power bi reports I would guess there is a large amount of repetition?
If so could you use azure data factory to process it into a dimensional model in sql before you load? Eg the dax query column becomes a dimension table.
I have very little experience with azure data factory but that approach would work in on premise SSIS.
An alternative would be to have your incremental dataflow point at a view in SQL. Intitially you could put a date filter in the view to limit to the first few weeks. Run the dataflow. Extend the filter in the view. Run again and build up gradually.
Hey Ben,
I like the idea with the view and gradual expansion. That is a possible solution (albeit a bit painful). I also suggested breaking the DAX column out to its own table and it would significantly shrink the data size, but the other side of that is we are duplicating data. Instead of 7 million rows, we'll have 14 million... just spanned over 2 tables.
So the cardinality on the DAX column is 1-1 on the Performance level. Meaning each "transaction" has its own ID and what happened. There is an additional event type column, since a user doesnt necessarily need to use DAX to create a performance transaction. Here's an example below, I'm not including all the columns on this but you get the idea. There are other ones for CPU usage and Duration as well that are helpful.
The issue with cardinality comes when you JOIN to the Activity Log table (This is done through the Admin APIs, which gather specifically what a user is doing). The JOIN is tricky, but I was able to create a 5 min interval that defines a user doing "something" and join it to the performance data at that point with the same user in that same time frame doing "something". There is no direct JOIN between Activity Log and Performance Logs as far as I have seen.
Basically what this means is we are able to see what a user is doing within a 5 minute window and what they did, but it is possible for duplication... except at that level it doesn't hold a ton of importance because we still see what they did and how it had an effect on our performance.
- bcdobbs
I'll have a play with our log analytics tomorrow and give it some thought. As said I'm new to the azure id of life, how are you getting data from log analytics into your sql server?
That question is a bit outside of my realm, I'm working with a DBA as well. My guess is that he either runs it through Data Factory or some sort of direct feed from the storage on the Resource Group directly into SQL. I Know we have a few SPROCs running to keep the data clean and loading properly.
It can get pretty daunting because theres so much data (Depending on how active your users are).
It would be really cool if Microsoft actually did this and stored this in an efficient environment. I know they have the data but we just can't see it or access it other than the pre-loaded template reports they provide.