CODEWITHSUNDEEP
excelssas
lolxor
lolxor

Reputation: 55

Is there a way to split SSAS dimension in 2 without breaking references?

I have recently gotten involved with an old BI solution (SQL Server, SSIS, SSAS). One dimension is very bloated with 50ish attributes and it processes slowly. I want to break it down in at least 2-3 dimensions to reduce processing time. My concern is that all pivot tables and other front-end reporting utilising these attributes will break and need redesigning - we are a big company and tons and tons of excel-sheets etc. currently use this dimension.

Is there a way to split the dimension while maintaining references and filters to the affected attributes?

Upvotes: 1

Views: 171

Answers (1)

Mike Honey
Mike Honey

Reputation: 15037

I would rethink this approach - I would expect splitting a dimension to increase processing time, not reduce it. SQL will need to run 2-3 queries to get the data (instead of 1) and SSAS will need to build and check it's dimension-fact relationships 2-3 times (instead of 1).

I would have a look at whether the time is being spent running the SQL queries to gather the info, or in SSAS's processing of that data. You can get a rough feel by watching Task Manager while that dimension is being processed - if the SQL queries are efficient then the sqlserver.exe process should only spike up in CPU briefly, before msmdsrv.exe takes over.

Upvotes: 0

Related Questions