DCSIMG
November 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

November 2007 - Posts

I did this!

Though I didn't do it alone…

The slogan for Ikea is "dreams to take home with you". For me it was a bit of a dread. It started just by going there after having heard about how crowded it gets there on the weekend. Don't know if it's because we arrived in the evening, but it was actually a lot less crowded than it was last time I went there in the middle of the week! I went there with my boyfriend to pick up a kitchen trolley. We waited about a month for the local branch it to get and it finally arrived. After bringing it home, there was no way around it, we had to put it together (though his dad assured me that in case of an emergency we could always call him and he would do it for us ;))

So last Saturday morning we dedicated to the BEKVÄM kitchen trolley…

 

 

And we did it in 2.5 hours! I'm soooooooooo proud!

I'm not a terribly "handy" person and so I began mostly with reading the instructions to my boyfriend (which was in charge of the physical work…). As we went through it I was worried we'd end up with too many or too little screws (stranger things have happened). But we got the hang of it, and in the end, when my boyfriend got tired, I was the one doing the dirty work! (the little feminist in me was somewhat proud)

So yes, I'd like to think we conquered Ikea! In 2.5 hours :) …

Posted: Nov 30 2007, 08:20 PM by Ella Maschiach | with 4 comment(s)
תגים:,

Creating a Report Model based on an Analysis Cube

Well I guess congratulations are in order because I finally got around to using a report builder in my project. Turns out that building a report model based on a cube is as easy as 1-2-3. Still I had a problem, as for some unknown reason the translations were showing for my dimension names and my measures, but not for the dimension's attributes... At first I thought maybe this is a problem solved by Service Pack 2 (as my tests were done on a Server with Service Pack 1). Alas, no. So, a further look in the MSDN library, came up with a more detailed example as to how to incorporate translation in your cube based report model. I admit, this was the first time I had ever used the "LocaleIdentifier". But as I have a different translation than Chinese in my cube (which was the example in the relevant article), then what should I put in the connection string of the data source? So the next search on the web was for a table mapping the different languages to their respective Locale Identifier code. Looking at what the report model generated, I saw I still needed to add a few things to make it easy to use. So I added the following things: 
  1. I used in most of the attributes the InstanceSelection property which maps to the relevant application, just how the user may choose a value for that dimension attribute. It even seems that Microsoft has published a recommendation for the use of this property in its Best Practices for Analysis Services 2005 whitepaper. I found though that the report model shows me the same sort of list whether I choose "List" or "FilteredList", and it didn't show me anything when I chose "Mandatory Filter". What I liked here was the fact the in a list, Report Builder enables the user the filter for values even within the attribute value itself. The Panorama Web Access we're using as a cube viewer, only lets my user filter for values in the beginning of the attribute value. 
  2. I also found that the report model shows all of my attributes (even if I chose them to be "AttributeHierarchyVisible: False"). Also, as it represents a flat model, it didn't show the User Hierarchies I created in my SSAS project. So I ended up making 2 perspectives in my cube: one for the Panorama Nova View and one for the Report Model. I gave "AttributeHierarchyVisible: True" to all of my attributes, and the same translation as they have in their User Hierarchy. I also gave all the attributes in a relevant User Hierarchy their User Hierarchy's name under their "AttributeHierarchyDisplayFolder" property, so that the user would easily recognize from where to pick out that attribute in the Report Model. I also later saw that if you choose the attributes in your User Hierarchy to be visible even in the Panorama Nova View perspective, then you'll see those attributes also in the folder you defined for them. Apparently, Panorama also recognizes and knows how to use the AttributeHierarchyDisplayFolder property. In the Panorama Nova View perspective I marked the User Hierarchies I had from before and unmarked the attributes that were hidden before and now visible for the use in the Report Builder. In the Report Builder perspective, it was vice – a – versa.     
  3. What gave me the most grief was my Parent Child Dimension. As I described in a previous post, I use the dimension to describe my organizational hierarchy as I need to connect to it at different levels for my Budget fact table. In a parent child dimension the key attribute shows in the level with which it is linked, without the ability to aggregate up the levels. So I found myself adding 4 more attributes, as the number of levels in the organizational hierarchy. I chose not to show them in my Panorama Nova View perspective, as they look awful in that viewer, (whereas the key attribute performs beautifully). In the Report Model perspective I'm showing those 4 attributes and they look just like the real key attribute. The relevant attribute has a value for its level and the corresponding parent has a value in its level. Where I skipped a level, I had "null" as a name.
  4. Calculated measures I defined in my cube were not shown in the Report Model. I later found out this was because I needed to connect each calculated measure to a relevant measure group. So I did this through the "Calculations Property" button. The scopes I defined in MDX also take effect in the Report Model.
  5. I also saw that the Report Model was defining drillthrough reports built according to its own choice (based mostly on the keys in the relevant fact table). Now, I didn't want my user to be looking at keys, but rather at the relevant attributes connected to those keys. So I decided to create my own report builder clickthrough reports using report builder generated reports which I saved on the server and linked to the report model. I am also considering adding a few Reporting Services reports which will be based on the cube, thus enabling me quite easily to link an SSRS report to another SSRS report that will be saved on the Report Server.   
When I was just building the cube based model, I thought I'd be through with it all within 2 days. Truth be told, I discovered the Report Model introduced me to all sort of new niches in my Analysis Services Project. And I ended up working a bit more than I thought I would... Still, I imagine this was quicker than had I needed to build the whole mapping of connections from the relational database for the report model. I really liked the way the cube dimension and measure groups translated into entities in the report model. Most of all – I liked the way the report model enforces the connections I defined in the "Dimension Usage" tab of the cube, through the use of the measures themselves. In conclusion, I would like to respond to Marco's question and say that dragging all the Budget attributes to the report gave me a report quite quickly. But then again, the comparison may not be fair, as in Panorama and in the Cube Browser in Management Studio I am looking at the Budget User Hierarchy. In Report Builder there are no User Hierarchies and my report was actually a collection of attributes put one after the other. Last but not least, let me also recommend you a great article on the subject of building a report model based on a cube, which to my regret I got only towards the end of my work on the Report Model. If I had found it earlier I may have been able to save quite some time on searching the web. The article really goes through all the small little corners you may bump into while building your model. As my coworker found it through Live Search and not Google (which I was using), I am also starting to reconsider moving to that search engine...
Posted: Nov 21 2007, 08:06 PM by Ella Maschiach | with 31 comment(s) |
תגים:, , , , , , , , , , , , , , , , , ,

Microsoft SQL Server 2008 November CTP

Yes, it's true, it's finally here! The latest version of Microsoft SQL Server 2008 CTP just got released and it includes all sorts of goodies – especially for us developing on SSAS. The general highlights include:

 
  1. T – SQL intellisense
  2. Geospatial Support
  3. Analysis Services MDX Query Optimizer - Block Computation
  4. Analysis Services Aggregation Design
  5. Lookup Enhancements for SSIS
  6. Robust Report Server Platform
 

Microsoft is urging you to download it and send them feedback so as to refine and enhance product features. The CTP will automatically expire after 180 days. The download is available here. The CTP version of SQL Server 2008 Express Edition is also available.

Posted: Nov 20 2007, 09:48 AM by Ella Maschiach | with 1 comment(s) |
תגים:, , , ,

Adventure Works Samples

Just in case you were also looking for the SQL Server 2005 Sample Database, then you should know Microsoft has chosen to move it to the CodePlex site. SQL Server 2008 July 2007 CTP Samples are also available on the same site. I recommend downloading the versions which are marked in the end with CI as they enable a Case Insensitive search within them. The LT version is also CI.

I really love the way Adventure Works gives you an entire look on the abilities of SQL Server.  
Posted: Nov 12 2007, 06:43 PM by Ella Maschiach | with no comments
תגים:, , , , ,

IBM to Acquire Cognos

Well I guess we could all see that one coming: IBM has just announced that it intends to acquire Cognos. News of the intentions themselves surfaced some time ago, and I guess the latest news in the area helped make the final budge. The amount of independent BI companies is again slimming down.
Posted: Nov 12 2007, 06:33 PM by Ella Maschiach | with no comments
תגים:,

Looking at your Report on the Report Server

Continuing my theme of looking at things in the appropriate place, I thought I'd add another story. I was just building a report in Reporting Service, and defined the TextAlign property of the line to be "right". For some reason, when I previewed the report in the Visual Studio on my pc, in some cells the alignment was left and in some it was right.

I found in a few occasions, that when it comes to viewing your report in SSRS, you should define all of your needs in the report and then deploy it to the server. It's only in when you look at the report on the Report Server itself, that you can accurately see the different influences your definitions have on the report.

Posted: Nov 09 2007, 07:06 AM by Ella Maschiach | with no comments
תגים:, , ,