Tracking use of measures/dimensions

Question

Tracking use of measures/dimensions

leehbi

SSCrazy

Points: 2874
More actions
September 17, 2018 at 8:51 am

#379098

Hi,
I have a SSAS multi-dim cube used by around 100 people.
It's a legacy cube that I'm re-developing on SQL2016, has around 80 measures, 20 dimensions.
I want to asses which measures/dimension attributes are being queried.
The only experience I have is using the OLAPQueryStore, which I've previously used to help design aggregates, but this provides encoded values that can't really be used for my purpose.
Does anyone know if Extended Events can be used? This cube is running on 2012 R2.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

PB_BI SSCoach Points: 17463 More actions · Answer 1

leehbi - Monday, September 17, 2018 8:51 AM
Hi,
I have a SSAS multi-dim cube used by around 100 people.
It's a legacy cube that I'm re-developing on SQL2016, has around 80 measures, 20 dimensions.
I want to asses which measures/dimension attributes are being queried.
The only experience I have is using the OLAPQueryStore, which I've previously used to help design aggregates, but this provides encoded values that can't really be used for my purpose.
Does anyone know if Extended Events can be used? This cube is running on 2012 R2.

It is possible, I've set it up myself several times using a
variation of this:https://marcosqlbi.github.io/SsasEventsAnalyzer/

I have actual code that I have used on prior contracts to set it up in one go, PM me if you need something.

I'm on LinkedIn

leehbi SSCrazy Points: 2874 More actions · Answer 2

Hi
Thanks for the link. In the end I ended using the script from here and used the QueryBegin event. Exporting XEL file to CSV (manual). I then wrote a c# processor to pick out dimensions/measures using regex counting number of connections for each element.
It's not perfect as Excel creates some wild MDX including what look like EXCEL specific calculations (XL_PT*). Not a major problem but will try and alter regex pattern to exclude these.
Ideally I would prefer to keep this in SQL but didn't have enough knowledge of EE to do that.
I'm going to run some tests to ensure I'm not missing anything - subcubes etc.. but at first glance it looks okay.

Lee

PB_BI SSCoach Points: 17463 More actions · Answer 3

leehbi - Wednesday, September 19, 2018 5:15 AM
Hi
Thanks for the link. In the end I ended using the script from here and used the QueryBegin event. Exporting XEL file to CSV (manual). I then wrote a c# processor to pick out dimensions/measures using regex counting number of connections for each element.
It's not perfect as Excel creates some wild MDX including what look like EXCEL specific calculations (XL_PT*). Not a major problem but will try and alter regex pattern to exclude these.
Ideally I would prefer to keep this in SQL but didn't have enough knowledge of EE to do that.
I'm going to run some tests to ensure I'm not missing anything - subcubes etc.. but at first glance it looks okay.
Lee

I always used the QueryEnd event - that way you get the performance metrics too 🙂

If you want it kept in SQL you have to set up a table to consume the details from a .xel file. You then write stored procedures to query and insert the data from the .xel file into the table. You then wrap it all in an agent job that goes like....
1. Stop Trace
2. Run Stored procs
3. Archive .xel files
4. Start Trace

Then you have that run about every hour or so (it usually takes seconds). Be mindful that it doesn't always capture who is running the query - I've never figured out why and I think there's a connect bug raised somewhere, which I'm sure Microsoft are working hard on to fix.

I'm on LinkedIn

leehbi SSCrazy Points: 2874 More actions · Answer 4

Wouldn't be better to pull the data of the ring buffer?

PB_BI SSCoach Points: 17463 More actions · Answer 5

For your purposes, yes, since you are doing counts. My process is more to see what's happening and to diagnose long running queries etc.

That said, if I found a long running one, I'd still run it through profiler 😀

I'm on LinkedIn

jmetape Right there with Babe Points: 731 More actions · Answer 6

Hi PB_BI, I'm interested in your solution using the SSAS Events Analyzer from Marco @ SQL BI ( http:://marcosqlbi.github.io/SsasEventsAnalyzer/ ) . I've downloaded that code and am currently setting it up in our shop...

i'm curious about your timing of the stopping / starting of the trace. I assume you miss some queries? Missing some queries while the trace stops/starts probably isn't a real big issue, but on top of tracking performance we also want to track utilization of our model. I realize there are ways to mitigate that issue -- perhaps using the file_offset of the sys.fn_xe_file_target_read_file , or perhaps starting a new trace immediately before stopping the current one (with a different name ), or parsing during non peak usage ... anyway, wondering what your approach to that is.

Thank you,
Joe

jmetape Right there with Babe Points: 731 More actions · Answer 7

Hi, I discovered that starting the trace from a job (as our service account) results in the username not being populated. When executed from my machine (as me), the username is populated. The XMLA statement is exactly the same.

here is a screenshot of the traces results when the trace is started as myself, note the highlighted columns are populated and RequestID has a number in it:

here are the results when the trace is created as the service account . The highlighted columns are not populated and RequestID are all 0's:

PB_BI SSCoach Points: 17463 More actions · Answer 8

jmetape - Friday, September 28, 2018 8:20 AM
Hi PB_BI, I'm interested in your solution using the SSAS Events Analyzer from Marco @ SQL BI ( http:://marcosqlbi.github.io/SsasEventsAnalyzer/ ) . I've downloaded that code and am currently setting it up in our shop...
i'm curious about your timing of the stopping / starting of the trace. I assume you miss some queries? Missing some queries while the trace stops/starts probably isn't a real big issue, but on top of tracking performance we also want to track utilization of our model. I realize there are ways to mitigate that issue -- perhaps using the file_offset of the sys.fn_xe_file_target_read_file , or perhaps starting a new trace immediately before stopping the current one (with a different name ), or parsing during non peak usage ... anyway, wondering what your approach to that is.
Thank you,
Joe

I think unless you are looking to get very very very detailed results then the approach I mentioned is fine. It normally finishes in under a few seconds on a relatively modest machine. Keep it simple - the overlapping approach could work but there's a danger of capturing duplicate information if you're not careful. If you do need to schedule it for once a day during downtime, the job will probably take longer as it will have a bigger file to read. Without specific numbers of users it's difficult to tell which would be the best approach - little and often or big and seldom.

I'm on LinkedIn

PB_BI SSCoach Points: 17463 More actions · Answer 9

jmetape - Tuesday, October 2, 2018 9:39 AM
Hi, I discovered that starting the trace from a job (as our service account) results in the username not being populated. When executed from my machine (as me), the username is populated. The XMLA statement is exactly the same.
here is a screenshot of the traces results when the trace is started as myself, note the highlighted columns are populated and RequestID has a number in it:
here are the results when the trace is created as the service account . The highlighted columns are not populated and RequestID are all 0's:

As I'd mentioned - this is something that I'd previously noticed and have not been able to get to the bottom of. From memory (it's been a couple of contracts since I did it) I had the job running as the agent service account and it was intermittent. You could try setting up a user on the SSAS system and then set up a proxy account for the job that runs your traces (in the context of this thread) and see if that resolves it. Maybe it could be raised as a connect bug. I'd do it myself but the last time I did I started getting phone calls in the middle of the night from a man who identified himself as "Altruistic Bill" and he kept threatening to "donate [my] kneecaps to someone less concerned about silly bugs".

I'm on LinkedIn

jmetape Right there with Babe Points: 731 More actions · Answer 10

Thanks for the messages, I appreciate the info. For the timing of the loading of the files, here is what we are going to do, once a day:

1. Stop the trace
2. Move the xel file(s) from the SSAS server to a file share
3. Restart the trace
4. Run stored procs /Process/archive the xel files from the file share.

Moving the files from the SSAS server takes seconds
As for the missing data when the trace is started from a job, I'll try out what you said (will ignore all calls about my kneecaps). I'll post if I do get any resolution
Thank you