Using usage-based optimization to optimize the cube

A third option to create aggregations is to use usage-based optimization. This means that you create a log table in SQL Server that will contain all the statements that are executed against Analysis Services. This log is then used to create the relevant aggregations in the cube. To create aggregations based on usage-based optimization follow the given steps:

1. Open up SQL Server Management Studio and connect to your SQL Server database.

2. Run the following statement to create a new database, and to add the user starting the Analysis Services server to the database:

   --Query 8.4
   create database SSASQueryLog;
     GO
     USE [master]
     GO
     CREATE LOGIN [NT SERVICE\MSOLAP$MULTIDIMENSIONAL] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
     GO
     USE [SSASQueryLog]
     GO
     CREATE USER [NT SERVICE\MSOLAP$MULTIDIMENSIONAL] FOR LOGIN [NT SERVICE\MSOLAP$MULTIDIMENSIONAL]
     GO
     USE [SSASQueryLog]
     GO
     ALTER ROLE [db_owner] ADD MEMBER [NT SERVICE\MSOLAP$MULTIDIMENSIONAL...