Creating a Report Model based on an Analysis Cube

November 21, 2007

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…

Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

28 comments

  1. RickyMay 15, 2008 ב 0:57

    Hi Ella

    Great article, well written.

    I was wondering if there is any update on editing using BIDS to edit Report models based on SSAS Cubes?

    Kind regards

    R

    Reply
  2. Ella MaschiachMay 15, 2008 ב 9:39

    Hi Ricky,

    < ?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /> 

    Thank you for the compliments.

    I did do a follow up post to this one, detailing how you can update your report model based on a cube. The link is the last of the above comments of mine, or you can simply click here.

     

    Hope that helps,

    Ella

    Reply
  3. ZeeMay 30, 2008 ב 1:35

    Hi all!
    i am new to the SSRS ..my first task was to create a report on the cube…now i have like 25 Dims and 1 Fact i have tried all the possible ways i know but the system hangs up on me whenever i try to bring in all the Dims…Wht will be the best way to bring in all the Dims with same Measures being applied on all of them…or i should go ahead and deploy the model and then work on it?

    any suggestions?

    Reply
  4. Ella MaschiachMay 30, 2008 ב 16:53

    Hi Zee,

    I’m afraid to say I’m not quite sure I understood your question. If you have a cube ready with all the dimensions connected to it, then all you have to do for an SSRS report is to deploy the SSAS project and then connect to it with the DS of your report. The DS would obviously be of type “Microsoft SQL Server Analysis Services”.

    If on the other hand what you want to do is to connect your dimensions to your fact, what you need isn’t an SSRS project, but rather an Analysis Services project. Connecting the Dimensions to the Measure group can be done in the “Dimension Usage” tab or even with the help of the cube wizard.

    I hope that helps,

    Ella

    Reply
  5. ZeeMay 30, 2008 ב 21:34

    Hi Ella!
    thanks for the reply!!let me explain a lil better , what i am trying to do is cube is already built , next i connect reporting services to the cube and i can see the cube and browse it in reporting services…now the format of the report is :
    DIM 1 Measure1/Measure1.1/Measure1.3 …..
    DIM 1.1
    DIM 1.2
    DIM 1.3
    DIM1.4

    DIM2
    DIM2.1
    DIM2.3
    DIM2.4
    DIM2.5

    now i need the report in this format that the DIMs which have Hierarchies in them (1.1,1.2,1.3 etc) come according to all the Measures which are in the colomns…i need the DIMS on top of each other with the same Mesures the same format not in the format below…

    DIM 1 DIM 2 Measure1/Measure1.1/measure1.3..
    DIM 1.1 DIM 2.1
    DIM 1.2 DIM 2.3
    DIM 1.3 DIM 2.4
    DIM1.4 DIM 2.5

    now the problem i am getting is whenever i pull in all the Measures and bring in more then 3-4 Dimensions at a time the system hangs up…whts the best way i can bring in more then 4 Dims at a time ..i tried matrix but its giving me the format which i dunt want at the top…i built separate tables for each Dimension with all the measures but the system hangs up….
    so thts the prob:)

    thanks again for the reply and any help wud b really appreciated!!
    Zee!!

    Reply
  6. Ella MaschiachJune 1, 2008 ב 12:17

    Hi Zee, 

    Well I think I understand you better now, and if I have, then I think my answer has to be that I’m not sure there’s a way to accomplish what you want…

    You see, in a report based on a cube, if you put one attribute hierarchy after the other, the back engine creates a crossjoin between that attribute and the one that comes after it. That means, the back engine checks how does each and every member in that attribute relate to each and every member in the other attribute. If you have a lot of members in each attribute, then that makes the check longer. If you do this sort of check on a lot of attributes (as you had put them all one after the other) then you make the query even heavier. I admit I’m not even sure that dedicated cube browsing applications are capable of doing a report for 25 attributes from 25 different dimensions, it all depends on how heavy the query becomes.

    And btw, Reporting Services isn’t a dedicated cube browser like ProClarity and Panorama are. I deployed my Adventure Works cube to my Report Server and generated a report model based on it. I was capable of creating a report of 8 attributes from 7 different dimensions and 4 measures from 1 measure group. But that still won’t promise you anything, as each case should be checked individually.

    Maybe you should try creating a report model based on a cube (as described in the above post) instead of a reporting services report? As you say you only have one fact, I think it shouldn’t cause you too much trouble, but I still recommend reading my post about the pros and cons of building a report model based on a cube   

    Hope that helped,

    Ella

    Reply
  7. ZeeJune 5, 2008 ב 0:41

    thanks alot !! ya i tried it in every possible way , didnt work now i have a store procedure which is running MDX with an open query and populating the tables in the database , so i created the report from there!! i still have to try that report model option though so lets c what happens:)!!

    Thanks!

    Reply
  8. Ella MaschiachJune 6, 2008 ב 9:29

    Hi Zee,

    Thank you for coming back to update us all on what you did 🙂

    Ella

    Reply
  9. JagadishJuly 7, 2008 ב 15:12

    hi ,
    how to view hierarchy in report builder.
    Jagadish

    Reply
  10. Ella MaschiachJuly 7, 2008 ב 19:06

    Hi Jagadish,

    As I had written in paragraph number 2: “as it (the Report Model) represents a flat model, it didn’t show the User Hierarchies I created in my SSAS project”. If you still want to see hierarchies, you can do that in an indirect way. In the Report Model you enable the user to see all the relevant attribute hierarchies. Then, when the user works with the report model on the user interface, he can place the attribute hierarchies one after another, in the same order they appear in the User Hierarchy. If you do that in a matrix report, you get a (+) sign next to each member of the attribute hierarchies, making it look very much like your User Hierarchy.

    Hope that helped,

    Ella

    Reply
  11. TrevorJuly 15, 2008 ב 15:42

    Hi Ella

    I have managed to get my calculated measures to display in my Report Builder model as discussed in your original article. Now I am finding that some of my calculated measures are coming through as blank. On closer inspection I found that my “Same month as Last Year” and “Year to Date” calculated measures are not coming through. Here are samples of these two calculations. I have a feeling that the problem is caused because the calculations refer to hierarchies and report builder is not too hot on hierarchies!! Your help will be appreciated. Thanks

    CREATE MEMBER CURRENTCUBE.[MEASURES].[Sales LY]
    AS (
    ParallelPeriod
    (
    [Month].[Month].[Month],
    12,
    [Month].[Month].CurrentMember
    ),
    [Measures].[Sales]
    ),
    FORMAT_STRING = “Currency”,
    NON_EMPTY_BEHAVIOR = { [Sales] },
    VISIBLE = 1 ;

    CREATE MEMBER CURRENTCUBE.[MEASURES].[Sales YTD]
    AS Sum
    (
    PeriodsToDate
    (
    [Month].[Financial Month Hierarchy].[Financial Year],
    [Month].[Financial Month Hierarchy].CurrentMember
    ),
    [Measures].[Sales]
    ),
    FORMAT_STRING = “Currency”,
    NON_EMPTY_BEHAVIOR = { [Sales] },
    VISIBLE = 1;

    Reply
  12. Ella MaschiachJuly 16, 2008 ב 12:50

    Hi Trevor,

    I think you’re correct. I’m also guessing your problem is caused by the fact your MDX calculations are based on user hierarchies which Report Builder doesn’t recognize. Perhaps you should try creating new calculated members for your Report Model or Modifying the calculation you have.

    For instance, I’m assuming your Last Year Sales would look at your last year and the current month in a dimension called Period. So we can also write it as:

    CREATE MEMBER CURRENTCUBE.[MEASURES].[Sales LY]

    AS (

               (

               [Period].[Year].CurrentMember.Lag(1),

              [Period].[Month].CurrentMember

            ),

            [Measures].[Sales]

    )

    And so use the Lag function on an attribute hierarchy instead of Parallel Period on a user hierarchy. Obviously, you need to add to this also a check that if you’re at your first year then this calculation has no meaning (using the IIF function along with IsEmpty).

    Along that Example, the Periods To Date function can be updated to something along the lines of:

    CREATE MEMBER CURRENTCUBE.[MEASURES].[Sales YTD]

    AS Sum

    (

     TopCount(Descendants(Ancestor([Period].[Year].CurrentMember, 1), [Period].[Year].CurrentMember), 1): [Period].[Year].CurrentMember

    ,

     [Measures].[Sales]

    )

    I admit I’m not completely sure about the Year to Date alternative as I know I’m not addressing the Month within the year and so maybe that should be added into the calculation.

    These are just thoughts and examples as I haven’t got your cube in front of me, but I think it gives a good example on what you can do if the simple MDX that’s based on hierarchie doesn’t work.

    I would also recommend looking further into my post about the pros and cons of building a Report Model on an SSAS Cube.

    Hope that helped,

    Ella

    Reply
  13. TrevorJuly 21, 2008 ב 17:05

    Hi Ella

    I tried modifying the calculations to this:
    CREATE MEMBER CURRENTCUBE.[MEASURES].[Sales LY]
    AS (
    (
    [Month].[Month].CurrentMember.Lag(12)
    )
    ,[Measures].[Sales]
    ),
    NON_EMPTY_BEHAVIOR = { [Saes] },
    VISIBLE = 1 ;

    but to no avail, it is still coming through as blank in Report Builder but yet when I browse the cube in Management Studio it is fine. I must admit, I am getting far to many funnies related to Report Builder and Cube based models to have much confidence in recommending it as a good ad hoc reporting tool. I really hope Microsoft is going to do some radical improvements in this area in future releases of the product.

    Reply
  14. TrevorSeptember 12, 2008 ב 8:59

    Hi Ella

    I was wondering if we have met a dead end related to my above posts. Hopefully someone can still assist??

    And I have a new challange..

    In a Reporting Services report based on a cube we have a variance calculated measure of this month over same month last year (for growth percentages, etc). The formula is CY-LY/LY and is defined in the cube as Variance Percent. This comes in fine when we show it at a detail level. When we want to see totals it does not work so well. We use the aggregate function but then everything comes out blank. We have to rather do the calculation in the report as a function ((SUM(This Year) – SUM(Last Year)) / Sum(Last Year).

    Is this another potential bug? I read somewhere that this is overcome by using an OLEDB driver for AS instead of the usual cube driver, but then you cant use parameters. Not really the answer that I wanted.

    Reply
  15. Ella MaschiachSeptember 13, 2008 ב 17:20

    Hi Trevor, 

    Well unfortunately enough, I haven’t encountered the problem you’ve mentioned. Also, I haven’t heard about anything new coming from Microsoft on that subject. Even more so, from my personal impressions of it, things look like they will stay the same for SQL Server Reporting Services 2008. I’ve started playing around with something quite interesting called Intelligencia which is specifically targeted at SSRS reports based on cubes and I think you should definitely check it out yourself.

    Sorry I can’t be of more assistance to you on the subject. 

    All the best,

    Ella

    Reply
  16. VladOctober 7, 2008 ב 13:35

    Hi Ella.

    Could you help me clicklthrough report based on cube AdwentureWorksDW sql2008?
    I am trying to create simple sample cliclthrough table report .
    I created 2 reports (Country -> state) and (state->city with property “allow clickthrough…”)
    I am trying to link them by choosing clickthrough page in report Manager. But report Manager returned errors
    (The model item “udmmodeling:Entity_Dimension_Customer” was not found in the model “/Models/cube test”. (rsModelItemNotFound))
    I will be glad if you have any suggestions.
    And one thing else:
    Is it correct that in ReportManager in ckillthrough page I cannot see Perspectives on which report can be based?

    Reply
  17. Ella MaschiachOctober 9, 2008 ב 14:36

    Hi Vlad,

    First of all I have to admit, I haven’t come across such an incident in my work and I didn’t manage to find anything about it on the internet so the next things will only be a suggestion. First of all, have you followed the instructions from the SQL Server 2008 Tutorial:
    http://msdn.microsoft.com/en-us/library/ms365304.aspx
    http://msdn.microsoft.com/en-us/library/ms345252.aspx
    Have you used the same base entity as the one you’re trying to link the report to?
    The rest I’ll try to look into when I get back from vacation….

    All the best,
    Ella

    Reply
  18. VladOctober 10, 2008 ב 16:32

    Hi Ella

    As I could read in pages you noticed MS wants to create link reports from Adventure Works tables not a cube SASS.
    Is it possible create cube based linkrepots?
    /or it is feasible only for tables/

    Reply
  19. Ella MaschiachOctober 26, 2008 ב 12:17

    Hi Vlad,

    Sorry for the late reply, but I have only recently come back from vacation…
    I admit I feel we might be mixing up things here.
    You can create Clickthrough Reports based on a cube and relate them to the base entity from which the Report you generated had started. Clickthrough Reports don’t have to be based on a relational Report Model.
    Also, you define which Clickthrough report you want for which entity through SSMS, by connecting to the relevant Report Server and defining it for the relevant entity in the Model.
    From your last comment, I couldn’t tell though if what you were actually asking was about Linked Reports developed in Reporting Services (rather than Report Model) on a Data Source of a cube – was that actually your question?
    Last but not least, in Report Manager and in SSMS (while connecting to the Report Server), you do not see the perspectives of a Model, but only the Model itself.

    Hope that helped,
    Ella

    Reply
  20. SabarinathanNovember 26, 2008 ב 4:50

    Hi Ella .
    Today i faced a problem in Report builder that. i created SSAS Cube with 10DIM with 2 FACT table . but in SSRS Output came fine but in report builder i cant able to bring the 2 fact table .
    if i drag any one of the measure values from fact table then another one will disappear ..
    i tried a lot but cannot able to slove this issues.

    is it possible to bring 2 fact table in report builder as like SSRS ..

    Very Urgent .. Reply ASAP ..
    Thanks in Advance!

    Regards,
    Sabari.

    Reply
  21. Ella MaschiachNovember 28, 2008 ב 1:54

    Hi Sabari,

    I’m afraid there doesn’t seem to be a solution to your problem. As I had stated in my post: Creating a Report Model based on an Analysis Cube – Pros and Cons under paragraph 4: “a report model based on an SSAS project will only show you measures from one measure group each time”. Though there is an exception to the rule (which you can read at the post), that is the usual case. I hope the posts helps you understand that are also other problems (and good things) in this sort of development.

    All the best,

    Ella

    Reply
  22. AnaJanuary 20, 2009 ב 19:23

    Hi Ella,

    I have a DM based on SSAS. when I generate the model it ignores one dmension that is only connect do facts througt Dimension Usage (not in DSV) using 2 atributes.
    Do you know why this is happening. Is this a limmitation?

    Thanks in advance.

    Best Regards,
    Ana

    Reply
  23. Ella MaschiachJanuary 21, 2009 ב 15:30

    Hi Ana,

    I had a cube with one fact table and one dimension connected to it based on one attribute and another dimension connected to it based on two attributes. I deployed that cube to the RS Server and generated a Report Model. The Model shows me all the dimesions properly, and the cube looks as it would in the cube browser.

    Try to look if it’s something else – have you hidden all the attributes of that dimension?

    All the best,

    Ella

    Reply
  24. AnaJanuary 21, 2009 ב 17:23

    Hi Ella,

    Thanks for your answer.

    Did you tried some like this:

    Fact
    Field A
    Field B
    Field C

    Dimension
    Field D
    Field E

    And the relation is:

    A – D
    And
    B – E
    All the attributes in my dimension are visible. I’m not understanding what I am doing wrong. 🙁

    Thanks,
    Ana

    Reply
  25. Ella MaschiachJanuary 22, 2009 ב 13:47

    Hi Ana,

    I had something like:
    Fact: key 1, key 2, key 3, measure
    Dimension: key1, attribute x
    Dimension: key 2, key 3, attribute y

    The relevant keys were connected and I saw it fine. Perhaps you defined the dimension itself as hidden (rather than just its attributes)? Perhaps you forgot to mark it under the Report Model relevant perspective (if you have one)? Maybe you defined a security on that dimension or an MDX which may have defined on that dimension?
    Maybe you should still try to connect the dimension in the DSV (though I admit I’m not sure that’s the answer). You can connect tables on as many keys as you like in the DSV as well.

    All the best,
    Ella

    Reply
  26. Ella MaschiachJanuary 22, 2009 ב 13:47

    Hi Ana,

    I had something like:
    Fact: key 1, key 2, key 3, measure
    Dimension: key1, attribute x
    Dimension: key 2, key 3, attribute y

    The relevant keys were connected and I saw it fine. Perhaps you defined the dimension itself as hidden (rather than just its attributes)? Perhaps you forgot to mark it under the Report Model relevant perspective (if you have one)? Maybe you defined a security on that dimension or an MDX which may have defined on that dimension?

    Maybe you should still try to connect the dimension in the DSV (though I admit I’m not sure that’s the answer). You can connect tables on as many keys as you like in the DSV as well.

    All the best,

    Ella

    Reply
  27. AmbyJanuary 27, 2010 ב 8:55

    Hi Ella,

    I have created 2 calculated members in SSAS 2008 and have associated it with a measure group. When i try to create a report model based on this cube, calculated members are visible in report model, but when i use them to see the data, [MEASURES].[A/B] was showing data and [MEASURES].[Actual] was appeaing blank.

    Calculated members in SSAS are as follows,

    CREATE MEMBER CURRENTCUBE.[MEASURES].[A/B]
    AS CASE
    WHEN ([Measures].[B]<>0) THEN
    ([Measures].[A]/[Measures].[B])
    ELSE NULL
    END,
    VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = ‘VW OR Measure Gregorian FC’ ;

    CREATE MEMBER CURRENTCUBE.[MEASURES].[Actual]

    AS CASE

    WHEN ([VW OR Metric DI].[Formula – Metric Desc].Properties(“Metric Formula”) = “A/B” AND [Measures].[B]<>0)

    THEN([Measures].[A]/[Measures].[B])

    ELSE

    NULL

    END,

    VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = ‘VW OR Measure Gregorian FC’;

    It seems like since im referring to a dimension’s member property in the second calculated member its displaying blank. I just wanted to highlight that if I dont have any dimension to refer within calculated member, its working fine. Once you have any dimension attributes to refer, its not working. I wanted to know if there is any other way to rewrite it.

    Thanks,
    Amby

    Reply
  28. Ella MaschiachJanuary 31, 2010 ב 20:05

    Hi Amby, 

    In general, you cannot use a calculated measure that references a dimension. I have discussed this in my post: Creating a Report Model based on an Analysis Cube – Pros and Cons under point 5 which states that: “it does not allow you to define a calculated member based on a dimension attribute. You can only define calculated members based on your measures.”
    I still tried to think of something to help you pass your problem. Give this a go:
    Define a regular measure you would like to use.
    Let’s say you define: 

    CREATE MEMBER CURRENTCUBE.[MEASURES].[X]

    AS 1 

    Link that measure to a measure group. Now, for the specific member of your dimension you can define the following: 

    Scope

    (

    [VW OR Metric DI].[Formula – Metric Desc].Properties(“Metric Formula”) = “A/B”, [MEASURES].[X]

    );

    This = [MEASURES].[A/B];

    End Scope ; 

    I haven’t tested this on my own cube + report model so you’ll have to check for yourself if it works. Basically, as you want the same calculation of measure A/B for that particular dimension member, I’ll change my regular measure with that calculation when I reach that member. There is also more to read on Scope in MDX if you’d like.
    Come back to tell us if it worked. 

    All the best,

    Ella

    Reply