Forum Discussion
Dataflows, Azure and Azure SQL processing large dataset
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.
Community ChampionI'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.