There are a number of available tools that are free and useful when developing SSAS cubes. The most important is a very simple one that merely needs to be turned on. That’s right. Turned on. SSAS allows you turn on an internal process that will log a good bit of useful data on things to do with your cubes. You need to turn it on. First, as in the picture, right-click your SSAS server in the Object Explorer. Choose ‘Properties’. After doing so a window will open, including the values seen in second picture below… For example, the values in the query log connection string for my machine are: “Provider=SQLNCLI10.1;Data Source=.;Integrated Security=SSPI;Initial Catalog=msdb“.
The QueryLog sampling is set for 1…that means I’m going to capture information from every query to the OLAP server. OK, so don’t do this on a production server! But on your own development box…there’s no harm and you’ll learn a lot. You’ll be able to track who uses your cube. Perhaps that’s mostly you. But, still, you’ll begin to gain a sense that you know what’s happening with your cube…and that’s important!
Below, you’ll find code to make use of the data in the table…