How can we get the usage of measures and dimensions in SSAS cube?

1
votes

We are struggling to find the usage of measures and dimensions in SSAS cube.

Objective: To have a statistical dashboard to find the unused or most used measures and dimensions.

I have got help from https://blogs.perficient.com/microsoft/2011/06/ssas-usage-statistic-dashboards/ to enable the OLAPQueryLog table.

OLAPQueryLog table provides the following information

  1. MSOLAP_Database
  2. MSOLAP_ObjectPath
  3. MSOLAP_User
  4. Dataset
  5. StartTime
  6. Duration

We are using Dataset field to only find the usage of our columns but not measure.

I have also tried getting the users current sessions using DMX query.

Select * from $System.discover_sessions

but this is returning me the currently active sessions.

How can i get the historic sessions?

Does any one has solution for it?

Thanks,

1
ssasssas-tabularmsbissas-2008ssas-2012
Are you working with Multidimensional or Tabular? OlapQueryLog will only work with Multidimensional (and even for Multidimensional it is probably not the right approach) - GregGalloway
We are working with tabular cubes, do you have any other alternative? - Vikas Gupta
I would start with using extended events and then querying the results... there is a lot of useful data you can leverage there - SeeCoolGuy
When creating extended Events, I am getting an error "Sequqnce contains no elements (System.Core)" - Vikas Gupta
It was a permission issue, I am able to log it, - Vikas Gupta

1 Answers

1
votes

Thanks guys, for your help.

I have achieved it Tabular Cubes using:

  1. Enabling Event Logs using Extended Events
  2. Using only Query End Event Log to get the statistics
  3. C#: Using C# to read details of the event
  4. By Using TextData, and regular expression in C#, extracting measures and dimensions