Recreating a Report Model

Wednesday, October 31, 2007

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...

The Parent – Child Dimension

Tuesday, October 9, 2007

A Parent - Child Dimension is a special sort of dimension, to be used in case:  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). 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...

Browsing a Cube and Influencing Aggregations

Thursday, September 27, 2007

You can browse a cube through the cube browser in Analysis Services or through the one in the Management Studio. I usually use the one in the Management Studio, as it gives your more space to look. In the cube I created in Analysis Services, I added a Budget hierarchy in my Budget dimension. I decided to check the data for the new hierarchy in comparison to the money that was assigned to it in the Budget fact table. And it was problematic…I was opening the Budget hierarchy and in the first 3 levels it reacted pretty quickly, but...

Solve Order and how it can improve your MDX

Friday, September 14, 2007

I thought this time around, we'll discuss how solve order can affect your MDX calculations. I found that people tend to ignore this property, as it is not mentioned in the "Form View" of the Calculation Tab of the cube (which is what people usually tend to use when they write MDX for a cube. I hope this will be taken into consideration in SQL Server 2008). Now, this is a shame, as solve order can greatly affect your calculations. For each calculation you do in MDX, you can specify the optional property of solve_order. If you do not mention...

Slowly Changing Dimension type 2 in SSAS

Saturday, September 8, 2007

There are quite a few ways of ways of documenting the changes a business entity goes through over time. The most famous of them all (and the most difficult to build) is a Slowly Changing Dimension (SCD) type 2. doing this in a table, you would create a table which would include: an incremental key, the business key, the attributes you'd like to track for that entity, a start date and an end date (for which the information was right) and a flag which would equal 1 when we are looking at the last row available for the member...

Ooooooooooh Weeeeeeeeeeeh!

Wednesday, August 29, 2007

"Oh my God! Oh my God! Oh my God! Oh my God!" And that was just my initial reaction when I discovered my post about the latest cumulative update for SQL Server 2005 SP2 got a mention on the opening page of Microsoft Israel!     Yes! It's true!! I could hardly contain myself! J Still, I waited till I got home before doing my little dance of happiness in front of the mirror hearing in my ears "I'm so excited, and I just can't hide it…" (the Pointer Sisters for those of you who were not around in the 80's). I would...

Recreating an Analysis Services Project

Saturday, August 25, 2007

Well, continuing our story about strange things that can happen, I thought I'd share with you this little tidbit. I was taking over a project from someone who had left the company a while back. In the cube that was on the old server, there was an MDX calculation. Only thing was that calculation was missing from the project on the new server (and I admit I couldn't guess what was behind it). So what can you do when you need something that in a project of an old cube? You import the project back to you! The way to do...

Strange things can happen to a cube….

Thursday, August 9, 2007

…like for instance, it could all of a sudden turn into a ball!  J Ok bad computing joke. So this time around I thought I'd tell you about a weird incident I had with my cube. I found out I could see my cube in the Management Studio and even browse it, but when trying to run an MDX on it (again, in the Management Studio) it would tell me I didn't have any cubes for the MDX to run on.. I could still run MDX scripts on it in my SSAS project so I continued as usual, thinking to myself...

SSAS Back Engine and Named Queries in the DSV

Thursday, July 19, 2007

Hi everybody,   What’s up? Thought I’d share with you something strange I found while using named queries in the DSV layer in SQL Server Analysis Services. I have developed quite a complex layer of connected tables in my DSV. As development had evolved (with the customers demand) I found myself turning 3 of my regular tables into named queries. The named queries were basically using the same table twice in an inner join to compare the state of the entity in two different times in the table. After this change, my process time for the cube and the dimensions grew from about...

Friendly Names and Translations

Monday, June 18, 2007

We define names for our tables and columns following the guidelines defined in our company. we might even adhere to international naming conventions. Still, nothing promises us the names we give our DB objects will be comprehensible to our customers. And this is where SSAS steps in - Analysis Services enables you to give alternative names to your dimension attributes in the following two ways: Translations may be given to: dimensions, dimension attributes, user hierarchies, measures, measure groups and to many other objects. The translation usually appears in a separate tab called (surprise) "Translations". This tab enables us to...