I have recently discovered a brand tool to help me better understand my work. It's a free tool from
I know I had written of the simplicity and ease of creating a report model based on a cube, but I too
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
Hi Ricky,
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
Creating a Report Model based on an Analysis Cube – Pros and Cons If you follow my blog, you probably
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?
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,
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!!
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,
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!
Thank you for coming back to update us all on what you did :)
hi ,
how to view hierarchy in report builder.
Jagadish
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.
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
VISIBLE = 1;
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:
[Period].[Year].CurrentMember.Lag(1),
[Period].[Month].CurrentMember
)
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:
TopCount(Descendants(Ancestor([Period].[Year].CurrentMember, 1), [Period].[Year].CurrentMember), 1): [Period].[Year].CurrentMember
,
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.
I tried modifying the calculations to this:
[Month].[Month].CurrentMember.Lag(12)
,[Measures].[Sales]
NON_EMPTY_BEHAVIOR = { [Saes] },
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.
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.
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,
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?
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:
msdn.microsoft.com/.../ms365304.aspx
msdn.microsoft.com/.../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….
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/
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.
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.
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.
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
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?
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,
I had something like:Fact: key 1, key 2, key 3, measureDimension: key1, attribute xDimension: 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.
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]
WHEN ([VW OR Metric DI].[Formula - Metric Desc].Properties("Metric Formula") = "A/B" AND [Measures].[B]<>0)
THEN([Measures].[A]/[Measures].[B])
ELSE
NULL
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.
Amby
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.
Pingback from Creating a Report Model based on an Analysis Cube « A Blog for SQL Server Reporting Services (SSRS) Programmers