Usage Based Optimization

One way of optimizing performance might be to optimize the aggregation design according to the actual usage of the OLAP database.

To enable this we must first create a SQL server database, capable of logging users’ requests to the OLAP database.

Open the Microsoft SSMS and connect to Database Engine. Create a simple, empty database named e.g. SalesQueryLogDB.

Connect to Analysis Services...

...and select Properties for the Analysis Server.

Set the five properties related to Logging:

Make the following changes to the settings:

• CreateQueryLogTable -> change to true.
• QueryLogConnectionString -> change to point to newly created SalesQueryLogDB.
• QueryLogSampling -> change to 1 (to sample every single query).
• QueryLogTableName -> (keep it unchanged, i.e. OlapQueryLog)

Once you click OK the newly created table in the SalesQueryLogDB will start logging all queries sent to this Analysis Server.

If, for example, we open the TARGIT Client and open a few analyses or create a couple of cross tables, we will be able to see the OlapQueryLog table being populated.