5 minutes SSAS Admin for SQL DBAs

Often, a SQL DBA has to administer SSAS. Here’s a quick 5 mins guide.

1. How to process a cube?In SSMS, right click cube name, process, OK.

2. When was the cube last processed?File, New, DMX query, select  * from $system.mdschema_cubes

3. Who is using the cube right now?File, New, DMX query, select * from $system.discover_connection

4. How do I give a user access to the cube?Put the user into a new AD group. In SSMS, + on CubeName, right click Role, new role, Membership, Add, type the AD group name, click Cubes on the left pane, Access = Read

5. How big is the cube?The size of this folder: C:\Program Files\Microsoft SQL Server\MSAS11.SSAS_MD\OLAP\Data\CubeName.0.db
   Easier but less accurate: in SSMS, right click cube name, properties, Estimated Size.

6. How do I deploy cube changes to production?In Dev server, right click the Cube name, script database as, create, new window. Change the <Database Name? to “x_New”, and the connection string (Search “ConnectionString” to find it). Save it as x.xmla. Change connection to Production, F5 to execute the script. Right click the new cube, Process, OK. After the new cube is processed, delete the old cube and rename the new cube.
   If you have secondary Prod server, use Synchronize: right click the cube on SSMS, Synchronize. This will copy SSAS files from Secondary Server to Primary Server incrementally (only changed files are copied). You need to process the cube first in the Secondary Server.

7. How long does it take to process the cube?Open SQL profiler, File, new trace, specify SSAS server, Event: Progress Report Begin and End, column filter: DatabaseName like xxx.

8. How do I process a cube every night?In SSMS, right click cube name, process, script. Create a new SQL Agent job, new job step, type = AS Command, command = paste the script.

9. How do I track who’s using the cube every day?Setup query log table. See this article: link

10. Server spec for SSAS?Start with 16 GB, quad core, 100 GB disk. That should be ok for cube size 5-10 GB.

11. How do I limit what each user can see in the cube?In SSMS, + on Cube Name, right click Role, new role, Dimension Data, select the dimension, tick the number you want the users to see.

12. Error when processing the cube: the attribute key was not found.In SSDT, in Solution Explorer pane, double click the Dimension, look at the DSV panel. Make sure all the dimension key values in the fact table exist in the dimension table. If your dimension is made up of 2 tables chained together, make sure the referenced value exist in the referenced table.

13. How do I find out the MDX of the query?Install OLAP Pivot Table Extension: link (thanks to Greg Galloway)

14. How do I browse a cube?
    In SSMS, right click the cube name (not the database name), Browse.
    Better: open Excel, Data menu, From Other Sources, Analysis Services, type the SSAS Server Name, Next, select the cube name, Finish.

15. How do I query the cube data using SQL?
    select * from openquery(LinkedServer1, ‘select Measure1 on columns from Cube1′).
    LinkedServer1 is setup as Provider: OLE DB for SSAS 11, Product Name: MSOLAP.5, Data source: AS Server Name, Catalog: AS DB Name.

16. The cube is very slow
    Put a trace on SQL Profiler to capture all MDX that hit the cube. Find the long running MDX. Optimize using cube partitioning, aggregation, look at cell-by-cell calculation, check cell-based security, check server memory, use cache warming, don’t process the cube during office hours.