Data Warehousing and Business Intelligence

6 January 2010

Taking a cube offline

Filed under: Analysis Services — Vincent Rainardi @ 8:32 pm

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 🙂

Advertisements

5 Comments »

  1. Why not just unprocess the cube?

    Comment by Chris Webb — 6 January 2010 @ 9:58 pm | Reply

    • Thank you Chris, an excellent suggestion. It took only a few seconds and the cube disappears from the clients, even though it’s still there on the server so we can reprocess it later.

      Comment by Vincent Rainardi — 7 January 2010 @ 9:02 am | Reply

  2. I agree with you . Vincent Rainardi
    I have the same problem.
    In my case . I have 2 versions of cube
    1 CUBE_V01 is the old one
    2 CUBE_V02 is the new one
    I have many securities roles for my users in both cube.
    One day i want change all connection from CUBE_V01 to CUBE_V02 (Reporting services / Excel pivot)
    But I don’t want to stop processing CUBE_V01 (In case of error occur in CUBE_V02),
    I Need to make CUBE_V01 to invisible from user . But no options in SSAS to do this.

    ———————–
    Thanks
    Thanapat

    Comment by Torres — 8 January 2010 @ 8:23 am | Reply

  3. Here are this and some other articles on SSAS Detach and Attach Database:

    http://ssas-wiki.com/w/Articles#Detach_and_Attach_Database

    Comment by Sam Kane — 24 February 2011 @ 4:43 pm | Reply

  4. Thanks!

    Comment by wer wer — 29 August 2012 @ 12:43 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: