Using Aggregations to Performance Optimize a Cube - Getting Started with SQL Server 2012 Cube Development - page 252

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 ag-
gregations 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]
GO

3. In Management Studio, connect to Analysis Services, right-click on the server
in the Object Explorer and choose Properties .