Taking a cube offline

How do you hide a cube? How do you take the cube offline? Make it hidden or invisible so users can’t access it? I have never came across this question before. My cubes are always accessible by the users all the time. Why would I want to take it offline? Well, read on.

Sometimes a data warehouse may contain wrong data. For example, after a release is deployed to production. On that particular day, rather than letting the users consume incorrect data, it is better to close the BI systems. Meaning that we don’t allow any users to access the warehouse database, access any SSRS report, or access any cube.

But how do you restrict access to a cube (SSAS DB)? The answer of course, is by modifying the security. For each role we can specify whether the role can access a particular cube or not. But, we could have many roles. For example, because of dimensional or cell security. If you have say, 100 roles, it could take 2 hours to change them all. So how do you to prevent the users from accessing the cube?

Of course you could just stop the SSAS service. But if you have several SSAS DBs in that server, you don’t want to stop the SSAS service. Because you want users to be able to access the other cubes.

So, what could you do to take the cube offline? Delete the SSAS DB? But how do you put the DB back after the data warehouse is fixed? Yes, you could backup the DB and restore it back. This way no body can access the cube, because the SSAS DB is physically deleted. But, if the SSAS DB is very large, it takes a long time to back it up. Backing up a 10 GB SSAS DB could take 15 mins. If the cube produce wrong numbers, you don’t want users consuming incorrect data. It could be fatal for the business. you want to take the cube offline immediately. You don’t have 15 mins. You need to do it in 1 minute.

But how? Is there an property called ‘visible’ for a cube? An property that you can easily set using SSMS? Yes we do have ‘visible‘ property for a cube, but it’s in BIDS. Not in SSMS. So you can’t make a cube ‘hidden’ without deploying it first. To use this ‘make a cube visible or hidden’ property you need to either a) import the DB into a BIDS project, modify the attribute and redeploy to server, or b) script the DB to XMLA, modify the cube property execute the modified script and reprocess the cube. But do you need to reprocess?

No. You don’t need to reprocess the cube. You don’t want anybody to access the cube. So, just delete the cube. But, before deleting the cube, import into BIDS project first (or script to XMLA). This is quick, probably about 1 minute. When the DW is corrected, then you deploy from BIDS to the server, or execute the modified XMLA and reprocess the cube.

There is another option: detach. It works like in relational DB. An SSAS DB is physically a folder in the server called DatabaseName.0.db. Inside this folder there are many files: dimensions, partitions, etc. This folder is located under \data folder. ‘Detaching’ an SSAS DB means disconnecting the folder from the SSAS engine. So it’s a quick operation. Close all open files, flush the memory and away you go. Detaching a 5 GB cube only take a few seconds.

So how do you put it back? Attach, of course. ‘Attaching’ an SSAS DB means presenting/connecting this folder to the SSAS engine. Does it take a long time? No. Attaching a 5 GB cube takes about 30 seconds.

Still, better if we have “hidden mode” or “visible mode” though, in SSAS. Like “single user mode” and “multiple user mode” in SQL Server.
SSAS 2011 that is 🙂