DCSIMG
October 2007 - Posts - Ella Maschiach's BI Blog

Ella Maschiach's BI Blog

BI, Olap Technologies, Tools, System Analysis and Design

News

About Ella Maschiach

Business Inteligence

October 2007 - Posts

Recreating a Report Model

Remember how I taught you to recreate your Analysis Project? Well, let's say this time you have a model you deployed to the Reporting Services Server, but you have changed it since then and want the old version back. So how would you recreate the model you have on the server?

 

First of all, create a new Data Source View in your Analysis Project. It doesn't have to include all the relevant tables, as you'll only use it as a shell to build upon. After you've finished defining the DSV, save the project. Then, right click the new DSV icon you have in your Solution Explorer and choose "View Code".

 

Now, open your Report Server, (the link would usually be something along the lines of: http://ServerName/reportserver/Pages/Folder.aspx

Go to your "models" folder (or wherever you may have chosen to save the model you're trying to recreate). Click on the relevant model from he ones in the folder. What you see is the XML code that defines that model on the server. Right click the XML code and choose "View Source". This should export all the text for you to the notepad application. Use CTL + F to search the document for "DataSourceView". You'll find it somewhere down the document. Erase everything before the "<DataSourceView". Click again CTL + F for "DataSourceView" to find the next time that it shows in the document. Erase everything after the relevant ">". What you have left in your notepad is the definition of the model. Click Ctrl + A to mark all the text in your notepad document, and then Ctrl + C to copy it. Now use Ctrl + A to mark all the text in the code of your project's DSV and Ctrl + V to paste instead of it, the text for the model on the server. Close the window showing you the source code. You'll find you have a DSV filled with the relevant tables connected, and all as you have defined on the Report Server!

 

Be sure that the Data Source on which the DSV is based, also exists in your SSAS project (with the same name and the required access to the tables).

A Shift in the Market

This week, a couple of things happened. The first big news was that SAP had purchased Business Objects. The move is considered a response to Oracle buying Hyperion earlier this year. Microsoft has acquired ProClarity earlier in 2006. There also remains Cognos, SAS and Panorama as individual players. Still, Panorama has its own sort of news for this week, after it fired 20 of its workers in Israel (the article is in Hebrew). The workers are said to be from the development department and make for a fifth of the entire Israeli office of Panorama (which has 250 workers worldwide). Panorama has said this is not a dramatic move and still expects its new version of its cube viewer to be available during 2008.

All of this is happening in the BI Market which is said to grow by 11% till 2010, a larger growing rate than the one the general software market has.

I can't help but wonder to myself - how will the BI Market shift next?

Cumulative update package 4 for SQL Server 2005 Service Pack 2

CU 4 was released yesterday, and as in previous versions, contains the hotfixes that have been developed since the release of SP2. Like with cumulative update 3, this one is also multilanguage and is available through writing a request to Microsoft Online Customer Services at http://go.microsoft.com/?linkid=6294451. To install cumulative update package 4 for SQL Server 2005 Service Pack 2, you need to enable the SMO and the SQL-DMO stored procedures.

The cumulative update is also known as build 3200. For further detail, please see the knowledge base article.

The Parent - Child Dimension

A Parent - Child Dimension is a special sort of dimension, to be used in case: 

  1. You have data even for non – leaf members. For instance, you may have set – up costs allocated for the Category level (the higher level), but profit data for your Product level (the lower level).
  2. You have an unbalanced hierarchy. The classic example for this might be the Geographical Hierarchy which at times might look like: Country > State > City, and at other times like Country > City. For an international company trying to track its worldwide business, the hierarchy in the US would be: US > New York > New York.  Its business in the UK would look like: UK > London. (See illustration below).    
     

The Adventure Works example shows us the use of this for the Employee Dimension which incorporates both aspects: you have a ragged hierarchy for employees as each manager might not necessarily have the same number of level of workers under him \ her. You also have managers which have sales attributed to them, not only to the leaf - level employees.

In my own SSAS project, I found I needed the parent child dimension to describe my organizational hierarchy. This stemmed mostly from the fact I had budget allocations for different levels of the organization (not necessarily the team level unit which is the lowest level). Using the parent child dimension also enabled me to delete "unnecessary" levels which were created in the MF tables but were unnecessary in my SQL tables (these were mostly business units with no name which only existed in the MF tables as the MF tables only allow for the usual rigid hierarchies).

Last but not least, parent child dimensions are the only kind of dimension that you can write-enable, so that may also be a consideration for implementing them.

The Parent Child Dimension will have at least 3 columns:

1. The parent key

2. The child key (the key for the member of the current level)

3. The child name (the name for the member of the current level)

Both the parent key column and the child key column must be of the same data type. For the highest level (in my case, the entire organization), we may give a parent key which either equals itself, a non existent value key or null.

You can add a Parent Child Dimension to your cube, using the dimension Wizard. You define the key column to the dimension as being the child key column with it's name being the child name column. In a later screen of the wizard, you check the square for "this dimension contains a parent - child relationship between attributes" and the wizard should recognize that the relevant column is the parent key column, giving you a preview for the result.

When you use a hierarchy with a parent child dimension, you can use the HideMemberIf property that specifies when members will be hidden in a ragged hierarchy. For instance, you can choose if to hide a member if its name is identical to that of its parent.

For more details about how to incorporate a parent child dimension in your Analysis Services project please refer to the excellent MSDN article on the subject.