December 23, 2008 at 3:30 pm
We have a star schema with a few hundred of different metrics (it's a different discussion... so let's just bare with me) .. how we currently handle it is to have a single fact column, and the hundreds of metrics are modeled as a dimension.
A star schema with 3 dimensions for example... something like below:
store dimension
month dimension
measure dimension
a single fact value
store_pkmonth_pkmsr_pkvalue
11179
11269
1137896789
114798
115798
11679
1177
11898
1199
1213
1229
123989
124679
12597
1266897
1279
12878
We are going to report it in a cross tab format.. where month is column header.. and measure (msr) is row header.. like this:
Page: store1 (out of x)
Month 1Month 2Month 3Month 4
MeasureA793125130
MeasureB 6 976126
MeasureC7896789989130153
….
…
But we also want to add dynamic calculations in between the measure dimension values .. as well as add blank rows / formating dividers .. etc .. in the row level..
like this:
Page: store1 (out of x)
Month 1Month 2Month 3Month 4
Section 1 (measure A-F)
MeasureA793125130
MeasureB69976126
Calculated Value (B / C)8.73773E-060.00910.5846150.823529
MeasureC7896789989130153
….
…
I have used other OLAP / reporting products before, that i can easily add derived values into the report.. both column / row level (we are adding to row in this case).. but I haven't used SSRS as much, and don' tknow where I can do that
Any help is GREATLY appreciated
Thanks a lot!
December 24, 2008 at 1:55 pm
Looks like you might just have some aggregations going on that could be part of the hierarchy or some custom members that you can create and reference in the cube to get at the calculations you are wanting. Doing this logic in the cube or your query is going to be much easier than trying to do this in SSRS with the matrix. Just being able to insert rows between members like you are doing isn't natural for an OLAP source.
Here is a reference to a posting that might provide you some additional insight into some techniques with customizing the matrix report item in SSRS - Advanced Matrix Reporting Techniques.[/url]
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
December 24, 2008 at 3:23 pm
hmm.. sorry. but i am a little new to the SSAS / SSRS stack .... creating a custom member calculation sound good .. but i am not sure how it's done in practice that can help me...
i am really to add new calculations to a member.. instead of adding new member...
another example would be reporting thru months...
say you have a month dimension, with the month name attribute as the row headers
jan.
feb...
march..
april...
...
..
and now I want to add a custom data called "mymonth" .... in the same month_name attribute .. which is (Jan + March), or anything I want...
can I do something like that??
December 25, 2008 at 5:32 am
You can add custom members and aggregations that do no exist in the cube on the fly or in the calculations tab of the SSAS cube designer.
So for your date dimension if you want to add an aggregated member for Jan+March you would do something similar to the following (depending on your dimension and attribute names in your database):
WITH MEMBER [Date].[Month Name].[Jan+March] as Aggregate({[Date].[Month Name].[Jan],[Date].[Month Name].[March]})
SELECT [Measures].[Sales] on 0,
{[Date].[Month Name].[Jan],[Date].[Month Name].[March],[Date].[Month Name].[Jan+March]} on 1
FROM Cube
So if you wanted to create this in the cube in the calculations tab then you would do the following in the script view:
Create Member CurrentCube.[Date].[Month Name].[Jan+March] as Aggregate({[Date].[Month Name].[Jan],[Date].[Month Name].[March]});
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
December 29, 2008 at 12:07 pm
Thanks a lot so far!!! it's getting closer!!!!
you gave some example on creating new dimension member like [jan+march] ...
I apologize, but I am very new to SSAS, and I need a little more pointers...
I am able to create a new calculated member like [jan+march], and have the calculations come out fine. however, SSAS always place it at the bottom of the dimension value list ... instead of a specific location in the dimension... for example, I would like to place [jan+march] right after march before april.. but it always put it at the end
Another question is that ... how do i assign calculations to an existing dimension member.. say [jan+march] is already created in the relationsional database, and is moved to the cube... already have a sortID that I can sort it on. I just need to create custom calculations for it (it's NULL in the relational db) .. how is that created?? is that also in the calculation tab on the cube? or do it on the dimension ?
Dan English (12/25/2008)
You can add custom members and aggregations that do no exist in the cube on the fly or in the calculations tab of the SSAS cube designer.So for your date dimension if you want to add an aggregated member for Jan+March you would do something similar to the following (depending on your dimension and attribute names in your database):
WITH MEMBER [Date].[Month Name].[Jan+March] as Aggregate({[Date].[Month Name].[Jan],[Date].[Month Name].[March]})
SELECT [Measures].[Sales] on 0,
{[Date].[Month Name].[Jan],[Date].[Month Name].[March],[Date].[Month Name].[Jan+March]} on 1
FROM Cube
So if you wanted to create this in the cube in the calculations tab then you would do the following in the script view:
Create Member CurrentCube.[Date].[Month Name].[Jan+March] as Aggregate({[Date].[Month Name].[Jan],[Date].[Month Name].[March]});
December 29, 2008 at 12:17 pm
You could Sort the members based on the name of the attribute if needed using the Sort function in your query. If it is a physical member or you create this within the DSV or database view you are referencing then you can reference a column like SortID or SortOrder, depending on what you have available to use.
If it is a physical member already you can overwrite the calculation for that member using a SCOPE statement within the calculation tab of the cube.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply