Pages

Thursday, April 22, 2010

Creating solution file from a SSAS database

In many of my projects, I have heard my developers asking each other whether it is possible to get the solution file of a SSAS database and I have turned a deaf ear to almost all the cases in spite of knowing the answer. Now before you give me that flabbergasted look, let me try to justify my position. As an IT company, we do have certain processes and one of them is to keep your code in VSS (used for version management). Unless you follow this rule, there is every chance that you might miss upon a particular Change Request / functionality and end up implementing a new fix on an outdated version. And it is not common to see people doing their development work on the online version of SSAS (which is the SSAS database) because it is much easier. You just have to save your work to see the changes while if you need to do the same changes in the offline version (which is the solution file), you will need to save it and then deploy to see the changes (talking about changes like modifying the calculated members script. For most other changes, the cube has to be processed for the changes to be reflected). So I would rather not let them know the answer so that they are extra careful in the future and avoid making changes in the online version.

But sometimes, in support scenarios, it is necessary to know this technique. Let's say, you have been supporting an application which has been live from the past 5 years and now a change request has come in. Now let us face the ground reality, the application has changed hands so many times that you don't know how many vendors have been involved nor do you know where the latest source code is. At this time, the best option is to retrieve the source file from the online version and this post will show you exactly how to do that.

1) Go to Start-->Programs-->Microsoft Visual Studio 2005 and click on Microsoft Visual Studio 2005 which will launch the IDE (If your SSAS database is 2008 version, use Microsoft Visual Studio 2008 instead)

2) Click on File-->New Project

Select the Import Analysis Services option and after giving the required Name, Location and Solution Name, click OK.

3) A Wizard message will pop up prompting to continue by clicking Next. Click Next and the fill in the server name as well as the DB which you need to import. Click on Next after that

4) The Wizard will start importing the source files from the database and on completion, the Finish button will be activated. Click on Finish.

5) Presto! You have the source files right in front of you.

Disclaimer: Use with caution, Tech Lead advice recommended. I don't want companies coming after my neck just because my blog meddled with their processes :)

2 comments:

  1. Can you post a blog/resolution on the below topic

    We use an existing cube that was created by client some years ago. Now, I have a request to add two new attributes to an existing dimension. I want to add the two attributes without disturbing the existing dimension structure/properties. So when I recreate the dimension with the extra two attributes, how do I make sure that the new dimension is an exact replica of the old and of course has the two new attributes.
    Any checklist or something like that.

    ReplyDelete
  2. Hi Deepan,

    Ideally nothing else should change if you are just adding 2 attributes to your dimension, but anyways you will have to refresh your cube. You can follow the steps below and let me know if you need further help:-
    1) Use the technique mentioned in this post to get a solution first.
    2) Make sure that the view/named query in your DSV is changed to ADD those two attributes (should be like adding 2 columns in your view, check very carefully if you need to make any logic changes)
    3) Just add those 2 attributes in your dimension (you can also work around with the dimension attribute properties if you need)
    4) As long as you dont have facts coming in at this level, you dont need to make further changes to the cubes. You can process the cubes to another database, and see whether this is what you want. If it works, move to the production :)

    If you are still hesitant or not sure, mail me at jason143@gmail and we can talk in detail.

    ReplyDelete