Dimenstion on measure

There is count measure in the cube. I need to create a dimension that should list the range of values based on that count values:
Exp:

0-99
100-199
200-399
...
...
..
and so on.
User wants to pick the any range level in the dimension and cube should show corresponding cell values of count measure which would fall into range of the dimension level value.
For example:
if user picks up level 100-199 then cube would filter cell values of measure that has a value between 100 and 199 within 12 month range. So, it would be year to date value of the measure. My problem is how go about creating Dimension that based on measure in the cube and dimension should have level with range of measure values.
Any hint would be appreciated greatly.
Thank You.you need your Range lookup table

Range_lkp
---
Range_ID (PK)
Range_Desc

containing values

1 0-99
2 100-199
3 200-399

in your Fact table data would look like:

... Measure ..... RangeID (FK)
----------
... 104.56 ..... 1
... 95.47 ..... 3
... 345.77 ..... 3

the point is you have to populate your RangeID's to your Fact table during ETL

1) populate table with RangeID = NULL
2) update table set RangeID = ... use procedure (I don't think you'll handle it by one update statement)|||Well, it would be best solution. But, in my case wont work.
Measure in fact table will be aggregated over 12 month period in the Cube.
So, in your example Range Id = 1 of 104.56
Might be in the cube something like that:

313.68
Sum(
104.56
104.56
104. 56
)

So, 313.68 is no longer Range id 1

Point here is over period of time. Not fact value of that measure and that most of the cases will be aggregations of those facts (104.56).|||OK I know what you mean...

Maybe you could create another few snapshot tables. Weekly snapshot, Monthly snapshot, Quarter snapshot, Yearly snapshot. Set up Range ID's in those tables and use them for reporting. Another option could be solve this somehow on reports level. It's hard to say how, it depends on your business intelligence tool. BTW what tool do you use? Or how you report your data?|||We use MS Analysis.
Doing something like that in report is easy. But, users want it on the cube. I believe it should be done dynamically with MDX. I thought maybe somebody else already done it then I dont have to invent the wheel.
Thanks for response anyway.

Dimensions within Excel Pivot Table

Hi

I have a cube that has dimensions such as year, company, customer, statustext, employee etc.

In the Browse in Analysis Manager - all the dimensions look fine.

When I access the same cube from Excel after dragging and dropping the dimensions during analysis the dimensions in the Page section are not what is show when dragged to the row section.

For example - i have a display Customer as rows, years as columns. I drag the statustext next to customer and shows customer. The filter in the page section for statustext is correct. I have tried moving the dimension back to the Field List and re-adding, refreshing from the cube makes no difference. The only solution I have been able to come up with is rebuild the Pivot table - not what I want users to have to do.

Unfortunately, at teh moment we are limited to Excel for presentation.

Anyone ever seen this or have any suggestions?

Just remembered, I have experienced "Catastrophic Failure" - in Excel not sure if this is related and is server generated or local to Excel.

Steve

What version of SQL Server and what version of Excel are you using?

Also I did not quite understand the problem you are experiencing. Could you be more specific?

|||

Hi

SQL Server 2000 - Analysis Server 2000 SP3

I have a Pivot table from a cube, this pivot table has multiple dimensions.

For example - Account Manager, Status, Customer, Employee

If I display Sales by Customer (row) by Year (col) - that works.

If I then drag Status to the row - either to replace or in addition to customer - the display is actually Account Manager. The dimension Status is no longer in the Page Header but Account Manager is.

However, if I filter in the Page Header for a specific Status this shows correctly.

The only way I can fix it is to go into the Wizard - choose options - remove all dimensions, re-add them.

Thanks

|||

One possibility is that if your Excel file got corrupted (some mixup with dimensions), this would explain current behavior.

Can you re-produce the problem from scratch (blank spreadsheet)? If you can, how long does it take?

|||

It does happen from scratch but I cannot reproduce it intentionally. I did wonder if it may be because the structure of teh cube within Analysis Server changed. I hope this is not the case.

|||

I have seen a thread where somebody said he regularly experiences problems with pivot becoming corrupt when a datasource changes.

He said it's really common in Excel XP, but does not happen as often with Excel 2003. Since I don't know the Excel version you use, one possibility is to go with Excel 2003.

Also another solution is to always build pivots from scratch. You can even automate this by writing some VBA logic. Hope this helps!

Dimension writeback: does it update the datasource as well or only the cube?

Dimension writeback: does it update the datasource as well or only the cube?

I cannot change the underlying data but want to fix the data in a dimension, so I enabled writeback.. only I get errors as the writeback tries to update the underlying data source. (the user has readonly writes by design).

Hello. I am not sure about what version of Analysis Services you are talking about, but the answere is yes. The members you create with dimension writeback are written in your source dimension table.

HTH

Thomas Ivarsson

Dimension won't add to cube?

Hi, I built a new dimension with the dimension wizard while working with a cube that has dimensions pre-exisiting. The relationship is defined and I don't see anything different about it when I look at the other dimensions.

However, when I look at the cube editor, all of the other related dimension tables are blue and the fact is yellow, but this table is white. Then I process the cube and it's doesn't show up in the cube browser as a dimension. It's like it doesn't exist!!! How can I add a new dimension so that it's visible AND usable?

Also, how do I create shared dimensions or make private dimensions shared?

THanks in advance.

Mike

mreese@.satorigroupinc

which version of SSAS r u using?|||

I figured it out. When you add a new dimension, you still have to manually add it to the dimension usage screen. Pointless and non-intuitive, but it worked.

I am using SSAS 2005. Thanks for your response.

|||

The UI will automatically set the appropriate dimension usage so that a dimension is included in appropriate measure groups when it can be determined. However, as you've discovered, there are situations where the UI is unable to figure out the correct way to join a dimension to a measure group in which case you must manually specify the relationship in the dimension usage tab as you have done.

To answer you're other question, Analysis Services 2005 no longer contains the notion of private dimensions. You can get this effect if desired by simply prefixing your dimensions with the cube name and then renaming them in the cube. However, it is generally desirable to re-use dimensions for consistency whenever possible

sql

Dimension Range

I have a simple cube based on just 2 DB tables. I have 1 measure and 5 Dimensions. I need to create some ranges out of 2 numeric Dimensions. Can anyone suggest how to go about it. It may be a simple thing- but I am just a learner...I have tried to use DiscretizationMethod but my ranges are very specific so it does not help.

What are the numeric dimensions, and what are the ranges you want to create on the numeric dimensions? Your answers will point us in the direction of a solution. An example would be very helpful.

PGoldy

|||

The Ranges I want to create for the numeric Dimensions are something like this:

< 0, 0-300, 300-500, 500-520, 520-540, 540-560,....640-660, 660-680, 680-700, 700-900, >900

And for another Dimension:

<0, 0-70, 70-80, 80-90, 90-100, 100-125, > 125.

Thanks.

|||

Have you tried the TSQL CASE in the data source view.

Regards

Thomas Ivarsson

|||

Hi.

Thomas has a good suggestion (thanks Thomas). I'll give you a little more detail.

In the Data Source View (DSV) within BI Studio you can create a "named query". The named query allows you to define any valid SQL statement. Think of it like using a view in the SQL db, but it's part of the project in BI Studio so you're not bothered with maintaining a view in the SQL db. To create a named query open the DSV in Bi Studio. Right click in the layout pane and select New Named Query. The Create Named Query dialog lets you pick tables, columns, or enter your own SQL. Your choice. I recommend you enter your own SQL with a CASE statement which defines the dimension buckets you want. As an example (this code works on Adventure Works DW):

SELECT
CASE
WHEN SalesAmountQuota > 0 AND SalesAmountQuota < 5000 THEN '0-4999'
WHEN SalesAmountQuota >= 5000 AND SalesAmountQuota < 10000 THEN '5000-9,999'
WHEN SalesAmountQuota >= 10000 AND SalesAmountQuota < 20000 THEN '10000-10,999'
ELSE '20000+'
END AS SalesAmountBucket
FROM FactSalesQuota

The above statement generates the "keys" into your buckets for each record in the fact table. Don't forget to include the rest of your fact table columns in the SELECT and you get a new fact table as a named query.

The new column you've generated in the named query also serves as the attribute hierarchy for your range.

Good Luck.

PGoldy

|||

Thanks Paul for guiding with more details than in my Zen approach.

That is a good explanation of CASE.

Kind regards

Thomas Ivarsson

|||Thanks a TON to PGoldy and Thomas. Yeah, it was really that simple...can't believe it.

Dimension Range

I have a simple cube based on just 2 DB tables. I have 1 measure and 5 Dimensions. I need to create some ranges out of 2 numeric Dimensions. Can anyone suggest how to go about it. It may be a simple thing- but I am just a learner...I have tried to use DiscretizationMethod but my ranges are very specific so it does not help.

What are the numeric dimensions, and what are the ranges you want to create on the numeric dimensions? Your answers will point us in the direction of a solution. An example would be very helpful.

PGoldy

|||

The Ranges I want to create for the numeric Dimensions are something like this:

< 0, 0-300, 300-500, 500-520, 520-540, 540-560,....640-660, 660-680, 680-700, 700-900, >900

And for another Dimension:

<0, 0-70, 70-80, 80-90, 90-100, 100-125, > 125.

Thanks.

|||

Have you tried the TSQL CASE in the data source view.

Regards

Thomas Ivarsson

|||

Hi.

Thomas has a good suggestion (thanks Thomas). I'll give you a little more detail.

In the Data Source View (DSV) within BI Studio you can create a "named query". The named query allows you to define any valid SQL statement. Think of it like using a view in the SQL db, but it's part of the project in BI Studio so you're not bothered with maintaining a view in the SQL db. To create a named query open the DSV in Bi Studio. Right click in the layout pane and select New Named Query. The Create Named Query dialog lets you pick tables, columns, or enter your own SQL. Your choice. I recommend you enter your own SQL with a CASE statement which defines the dimension buckets you want. As an example (this code works on Adventure Works DW):

SELECT
CASE
WHEN SalesAmountQuota > 0 AND SalesAmountQuota < 5000 THEN '0-4999'
WHEN SalesAmountQuota >= 5000 AND SalesAmountQuota < 10000 THEN '5000-9,999'
WHEN SalesAmountQuota >= 10000 AND SalesAmountQuota < 20000 THEN '10000-10,999'
ELSE '20000+'
END AS SalesAmountBucket
FROM FactSalesQuota

The above statement generates the "keys" into your buckets for each record in the fact table. Don't forget to include the rest of your fact table columns in the SELECT and you get a new fact table as a named query.

The new column you've generated in the named query also serves as the attribute hierarchy for your range.

Good Luck.

PGoldy

|||

Thanks Paul for guiding with more details than in my Zen approach.

That is a good explanation of CASE.

Kind regards

Thomas Ivarsson

|||Thanks a TON to PGoldy and Thomas. Yeah, it was really that simple...can't believe it.sql

Dimension formula

In a sales cube I have added dimension formulas to an account dimension. Example: NetRevenuePer100kg. The formula is:

NetRevenue / Quantity * 100

When I use this in a MDX query like

select

{[DIM_ACCOUNT].[NetRevenuePer100kg]} on columns,

{[DIM_PRODUCT].[WOOD]} on rows

from cube

where ([DIM_TIME].[2005].[Q1])

everything is fine.

But using the following calculated member in the where clause:

... member [DIM_TIME].[MY_TIME] as 'Sum([DIM_TIME].[2005].[Q1].[Jan]:[DIM_TIME].[2005].[Q1].[Mar]) ...

... where ([DIM_TIME].[MY_TIME])

I get a value which is about 3 times greater than the correct one.

I think it is a problem about the calculation order. It seems that first the value for each month is calculated and then they are summed up. Using the AVG function is not the solution. It gives only a almost correct value.

When I use a calculated member instead of a dimension formula everything is ok.

But I wanna get it right with a dimension formula.

Thanks for any help

Cornelius,

You are correct about your issue being related to solve order. In the example you gave you should be able to get the result you are looking for by replacing the SUM() function with AGGREGATE().

Try:

... member [DIM_TIME].[MY_TIME] as 'AGGREGATE([DIM_TIME].[2005].[Q1].[Jan]:[DIM_TIME].[2005].[Q1].[Mar]) ...

HTH,

- Steve

|||

Thanks Steve for your reply!

I tried it. But there is no difference between Sum and Aggregate.

|||

Cornelius,

I should have confirmed this to begin with, but is your "NetRevenuePer100kg" calculation in the cube? Can you provide the MDX for both calulations and the query?

- Steve

|||

Hi Steve,

formula in the account dimension [NetRevPer100kg]:

iif(([ACCOUNT_Sales].[Quantity]) = 0, NULL, [ACCOUNT_Sales].[NetRev] / [ACCOUNT_Sales].[Quantity] * 100)

The complete MDX Query (please copy and enlarge in your editor):

with
member [ACCOUNT_Sales].[Test_FINE] as 'iif(([ACCOUNT_Sales].[Quantity]) = 0, NULL, [ACCOUNT_Sales].[NetRev] / [ACCOUNT_Sales].[Quantity] * 100)'
member [TIME_CalendarYear].[BasisZeitraum] as 'SUM({[TIME_CalendarYear].[All].[2005].[1. Halbjahr].[1. Quartal].[Jan.]:[TIME_CalendarYear].[All].[2005].[1. Halbjahr].[1. Quartal].[M?r.]})'
--member [TIME_CalendarYear].[BasisZeitraum] as '[TIME_CalendarYear].[2005].[1. Halbjahr].[1. Quartal]'
member [PRODUCT_GROUP].[sy_Slicer] as 'AGGREGATE({[PRODUCT_GROUP].[ZZ_All].[Eingekaufte Artikel].[Handelsware].[Handelswaren].[Emcat TC 30 (80441)].[Emcat TC 30 (8044125)],[PRODUCT_GROUP].[ZZ_All].[Eingekaufte Artikel].[Handelsware].[Handelswaren].[Emcat TC 30 (80441)].[Emcat TC 30 (8044115)]})'
select
{
([ACCOUNT_Sales].[Quantity]),
([ACCOUNT_Sales].[ContributionMargin].[NetRev]),
([ACCOUNT_Sales].[ContributionMarginPer100kg].[NetRevPer100kg]),
([ACCOUNT_Sales].[Test_FINE])
} properties [ACCOUNT_Sales].[DISPLAY_CAPTION], [ACCOUNT_Sales].[DISPLAY_FORMAT], [ACCOUNT_Sales].[DISPLAY_STYLE] on columns, non empty
{ [SALES_MANAGER].[Alle Bereichsleiter].Children
} on rows
from Sales
where ([TIME_CalendarYear].[BasisZeitraum], [CATEGORY].[Rechnung],[PRODUCT_GROUP].[sy_Slicer])

The calculated member [Test_FINE] gives the correct result. The member based on the dimension formula [NetRevPer100kg] only when I use the time member [Quartal] (uncommented).

It is a strange thing for me, that a calculated member and a member based on a dimension formula returning different results allthough using the identical mdx expression. And it only happens when I use a Sum (or Aggragate function) on an other member in the query.

Thanks for a reply

|||

Cornelius,

I noticed in your MDX you still have a background member defined using SUM:

member [TIME_CalendarYear].[BasisZeitraum] as 'SUM({[TIME_CalendarYear].[All].[2005].[1. Halbjahr].[1. Quartal].[Jan.]:[TIME_CalendarYear].[All].[2005].[1. Halbjahr].[1. Quartal].[M?r.]})'

The reason I think you are running into this issue is related to solve order. The AGGREGATE function will correctly sum up the numerator and denominator before performing the division. SUM will simply add up the percentages. In AS2K5 all calculations in the cube script are evaluated before any query or session scoped calculated members. This is why you are probably seeing a difference in the cube based calculated member results versus your query scoped calculated member. It is important that any aggregated member in the WHERE clause be calculated using AGGREGATE rather than SUM.

HTH,

- Steve

|||

Hi Steve,

with
member [ACCOUNT_Sales].[ContributionMarginPer100kg].[CM_NetRevPer100kg] as '[ACCOUNT_Sales].[ContributionMarginPer100kg].[NetRevPer100kg]'
member [ACCOUNT_Sales].[Test_FINE] as 'iif(([ACCOUNT_Sales].[Quantity]) = 0, NULL, [ACCOUNT_Sales].[NetRev] / [ACCOUNT_Sales].[Quantity] * 100)'
member [TIME_CalendarYear].[BasisZeitraum] as 'AGGREGATE({[TIME_CalendarYear].[All].[2005].[1. Halbjahr].[1. Quartal].[Jan.]:[TIME_CalendarYear].[All].[2005].[1. Halbjahr].[1. Quartal].[M?r.]})'
--member [TIME_CalendarYear].[BasisZeitraum] as '[TIME_CalendarYear].[2005].[1. Halbjahr].[1. Quartal]'
member [PRODUCT_GROUP].[sy_Slicer] as 'AGGREGATE({[PRODUCT_GROUP].[ZZ_All].[Eingekaufte Artikel].[Handelsware].[Handelswaren].[Emcat TC 30 (80441)].[Emcat TC 30 (8044125)],[PRODUCT_GROUP].[ZZ_All].[Eingekaufte Artikel].[Handelsware].[Handelswaren].[Emcat TC 30 (80441)].[Emcat TC 30 (8044115)]})'
member [SALES_MANAGER].[Alle B] as 'AGGREGATE({[SALES_MANAGER].[Alle Bereichsleiter]})'
member [CATEGORY].[Rechn.] as 'AGGREGATE({[CATEGORY].[Rechnung]})'
select
{
([ACCOUNT_Sales].[Quantity]),
([ACCOUNT_Sales].[ContributionMargin].[NetRev]),
([ACCOUNT_Sales].[ContributionMarginPer100kg].[CM_NetRevPer100kg]),
([ACCOUNT_Sales].[Test_FINE])
} properties [ACCOUNT_Sales].[DISPLAY_CAPTION], [ACCOUNT_Sales].[DISPLAY_FORMAT], [ACCOUNT_Sales].[DISPLAY_STYLE] on columns, non empty
{ [SALES_MANAGER].[Alle B]
} on rows
from Sales
where ([TIME_CalendarYear].[BasisZeitraum], [CATEGORY].[Rechn.],[PRODUCT_GROUP].[sy_Slicer])

In my case there is no difference using the SUM or AGGREGATE function.

I played with the solve_order.

with
member [ACCOUNT_Sales].[ContributionMarginPer100kg].[CM_NetRevPer100kg] as '[ACCOUNT_Sales].[ContributionMarginPer100kg].[NetRevPer100kg]', solve_order=1
member [ACCOUNT_Sales].[Test_FINE] as 'iif(([ACCOUNT_Sales].[Quantity]) = 0, NULL, [ACCOUNT_Sales].[NetRev] / [ACCOUNT_Sales].[Quantity] * 100)', solve_order=1
member [TIME_CalendarYear].[BasisZeitraum] as 'AGGREGATE({[TIME_CalendarYear].[All].[2005].[1. Halbjahr].[1. Quartal].[Jan.]:[TIME_CalendarYear].[All].[2005].[1. Halbjahr].[1. Quartal].[M?r.]})', solve_order=0
--member [TIME_CalendarYear].[BasisZeitraum] as '[TIME_CalendarYear].[2005].[1. Halbjahr].[1. Quartal]', solve_order=0
member [PRODUCT_GROUP].[sy_Slicer] as 'AGGREGATE({[PRODUCT_GROUP].[ZZ_All].[Eingekaufte Artikel].[Handelsware].[Handelswaren].[Emcat TC 30 (80441)].[Emcat TC 30 (8044125)],[PRODUCT_GROUP].[ZZ_All].[Eingekaufte Artikel].[Handelsware].[Handelswaren].[Emcat TC 30 (80441)].[Emcat TC 30 (8044115)]})', solve_order=0
member [SALES_MANAGER].[Alle B] as 'AGGREGATE({[SALES_MANAGER].[Alle Bereichsleiter]})', solve_order=0
member [CATEGORY].[Rechn.] as 'AGGREGATE({[CATEGORY].[Rechnung]})', solve_order=0
select
{
([ACCOUNT_Sales].[Quantity]),
([ACCOUNT_Sales].[ContributionMargin].[NetRev]),
([ACCOUNT_Sales].[ContributionMarginPer100kg].[CM_NetRevPer100kg]),
([ACCOUNT_Sales].[Test_FINE])
} properties [ACCOUNT_Sales].[DISPLAY_CAPTION], [ACCOUNT_Sales].[DISPLAY_FORMAT], [ACCOUNT_Sales].[DISPLAY_STYLE] on columns, non empty
{ [SALES_MANAGER].[Alle B]
} on rows
from Sales
where ([TIME_CalendarYear].[BasisZeitraum], [CATEGORY].[Rechn.],[PRODUCT_GROUP].[sy_Slicer])

Applying the solve_order 1 to the Account members and the solve_order 0 to the other dimension members I receive the same results ([Test_FINE] is correct, [CM_NetRevPer100kg] not).

member [ACCOUNT_Sales].[ContributionMarginPer100kg].[CM_NetRevPer100kg] as '[ACCOUNT_Sales].[ContributionMarginPer100kg].[NetRevPer100kg]', solve_order=0
member [ACCOUNT_Sales].[Test_FINE] as 'iif(([ACCOUNT_Sales].[Quantity]) = 0, NULL, [ACCOUNT_Sales].[NetRev] / [ACCOUNT_Sales].[Quantity] * 100)', solve_order=0

Setting the solve_order of the Account members to 0 and the solve_order of the other dimension members to 1 also the calculated member [Test_FINE] returns a incorrect value. This behaviour I understand: in this case first the calculation is performed and then the calculation results are aggregated.

But I still see no way to influence the aggregation of the member based on the dimension formula.

|||

Cornelius,

I am sorry that this is still not working. When you say you are using a dimension formula, can you be more specific. Are you using a "custom rollup column" or are you using a MDX statement in the cube script? I am curious as to the source of this problem and if possible it would be helpful if you could email me a copy of the project files.

spontello@.proclarity.com

- Steve

|||

Thanks to Steve there is a solution.

When using custom member formulas (what I called dimension formulas) there is no way to override the default aggregation behaviour of the cube. That issue is discussed in a previous post listed here:

http://groups.google.com/group/microsoft.public.sqlserver.olap/browse_thread/thread/885149d85c381cb0/a0bc768a4066d4fe?q=custom+rollup+division&rnum=2#a0bc768a4066d4fe

The solution here is the ussage of calculated members.

But my calculations are regarding to members in an account dimension. In this account dimension I had declard properties like custom_caption, custom_style or custom_format I use in my client application.

On calculated members I cannot declare these properties.

So the next issue was to join an accurate calculation to the members in the account dimension. For this issue Steve proposed the ussage of calculated cells.

Using the Calculated Cells Wizard you can define a MDX Expression for the calculation and specify a specific member (in my case from the account dimension) to apply the calculation to.

It works wonderful.

Dimension Displayed

Product Dimension Table

ProdID

Prod A

Prod B

Prod C

Fact Table

Key ProdID Measure

1 ProdA 100

2 ProdB 200

When I process the cube the and drag the measure and Product dim to view the result, the result show as below only ProdA and ProdB only.

The result sure correct and no error.

Just dont know why the ProdC not show as expected at SSAS2000.

I try the setting for the "Show Empty Cells" , It just for showing purpose only, not for permenant at that cube.

Anyone know why ProdC not show at the result as permenantly?

Do I need to do any setting to able ProdC show at the result.

Thanks .

This is not controlled by the cube, it is upto the client tool whether or not it includes empty cells or not. SSAS2000 was the same. By default a lot of browsers do not show empty cells and you have to explicitly turn this option on. There is nothing you can set at the database/cube level to control this.

Dimension Design using attributes?

I would like to design a dimension as part of a cube to enable all survey results to be displayed in a single excel chart.

I have 10 questions each with answer choices (A, B, C, D, NO Response)

The excel chart would have a bar for each question, and each bar would be composed of 4 colors representing either A,B,C or D.

I can generate 10 dimensions (one for each question), but I can't get it to fit in the same chart. I would like to create one dimension encapsulating the entire survey..by possibly using attributes.

Is there a bettwer way to do this...using excel and as2005?

Any help or advise would be appreciated!

The "Survey" section of this paper on many-to-many dimensional modelling discusses how to incorporate the answers to multiple questions of a survey in a single dimension:

http://www.sqlbi.eu/Portals/0/Downloads/M2M%20Revolution%201.0.93.pdf

>>

...

Survey

The survey scenario is a common example of a more general case where you have a lot of attributes associated to a case (one customer, one product, and so on) and you want to normalize the model because you do not want to change the UDM each time you add a new attribute to data (as adding a new dimension or changing an existing one). One common scenario is a questionnaire consisting of questions that have predefined answers with both simple and multiple choices.

...

>>

Dimension design

I have a cube, with a handset dimension. The facts I am measuring are counting the subscribers, their revenue and their usage. In the handset dimension, I have an attribute "score" which actually scores the features of the handsets. Now I would also like to measure the average handset score. Do I have to add a sum aggregated measure which sums up the handset score and create a calculated measure to divide this score measure by the subscriber count? Or is there another way to have the same result?

Thank you in advance

Joos

I think Sum/Count for this case.

SSAS 2005 also has Aggregation function AverageOfChildren, that specifies average of leaf descendants in time. Read about that one here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=548837&SiteID=1

Vidas Matelis

|||Could you explain the granulairy of the fact table - is it a periodic snapshot, or is there a record per handset owned by a subscriber? And when computing the average handset score for a given selection of data, is the average across all handsets in thst selection, or across all distinct handset types selected - could you illustrate with an example?|||

I have 2 fact tables. The first one is used to count subscribers and to measure their revenue. The second fact tabel measures the usage of subscribers.

In each fact table, I do have a record per subscriber/handset combination. Each handset has a score. This score is an attribute of the handset dimension. Of course, I do have other dimensions, but this does not matter for this problem.

For a given selection of data, I should sum up the scores of all corresponding handsets, and divide it by the nr of subscribers. To get a real average, I will not sum up the scores of all distinct handsets.

So for example:

Subscriber 1, handset A, score 5

Subscriber 2, handset B, score 4

Subscriber 3, handset A, score 5

This should result in an average score of (5+4+5)/3

Thanks

Joos

|||

Assuming that there is a [SubscriberCount] measure from the 1st fact table, and that there are [Handset] and [Score] attributes in the [Handset] dimension, where [Score] has a numeric MemberValue defined (like 4, 5 .. above):

Sum([Handset].[Handset].[Handset],

[Measures].[SubscriberCount] * [Handset].[Score].MemberValue)

/ [Measures].[SubscriberCount]

Or, for better performance, you could add the Handset Score to the 1st fact table using a Named Query, and create a "sum" measure on it like [HandsetScore]. Then, as Vidas suggested, you could use Sum/Count, like:

[Measures].[HandsetScore] / [Measures].[SubscriberCount]

dimension design

In my telco cube, I want to analyze usage. Eg how much sms's a subscriber has sent. The fact table is completely denormalized and has lots of columns with different types of usage. For example, following columns occur:

MO_VOI_PEAK

MO_VOI_OFFPEAK

MO_VOI_WEEK

MO_VOI_WEEKEND

Now the sum of these fields overlap. Evenings during the week are also offpeak... This is only an example. Different categories occur for different products, like voice, sms, mms and so on. And not all categories apply to all products. Eg, mms does not have the peak and offpeak distinction.

Is it possible to put this into one dimension, without aggregating double?

I have already normalised the fact table, meaning that for each usage type, I have one record. So the 4 types mentioned above, all occur in 4 records, instead of 4 columns.

I have also built up a table to link this usage type to its attributes. You can find an extract below.

UsageType

OrigDesc

ApplCode

PeakCode

RoamingCode

ScopeCode

WeekCode

PersCode

MO_VOI_NAT_ONNET

MO

VOI

NON-ROAM

NAT_ONNET

MO_VOI_NAT_OTHER

MO

VOI

NON-ROAM

NAT_OTHER

MO_VOI_NAT_FIXNET

MO

VOI

NON-ROAM

NAT_FIXNET

MO_VOI_INT

MO

VOI

NON-ROAM

INT

MO_VOI_ROAM

MO

VOI

ROAM

MO_VOI_OTHER_DEST

MO

VOI

NON-ROAM

OTHER_DEST

MO_VOI_FLATRATE

MO

VOI

NON-ROAM

FLATRATE

MO_VOI

MO

VOI

MT_VOI_NAT_ONNET

MT

VOI

NON-ROAM

NAT_ONNET

MT_VOI_NAT_OTHER

MT

VOI

NON-ROAM

NAT_OTHER

MT_VOI_NAT_FIXNET

MT

VOI

NON-ROAM

NAT_FIXNET

MT_VOI_INT

MT

VOI

NON-ROAM

INT

MT_VOI_ROAM

MT

VOI

ROAM

MT_VOI_OTHER_DEST

MT

VOI

NON-ROAM

OTHER_DEST

MT_VOI

MT

VOI

MO_VOI_PEAK

MO

VOI

PEAK

MO_VOI_OFFPEAK

MO

VOI

OFFPEAK

MO_VOI_WEEK

MO

VOI

WEEK

MO_VOI_WEEKEND

MO

VOI

WEEKEND

MT_VOI_PEAK

MT

VOI

PEAK

MT_VOI_OFFPEAK

MT

VOI

OFFPEAK

MT_VOI_WEEK

MT

VOI

WEEK

Is there a way to include all this information into 1 dimension, without aggregating in a wrong way, so without summing up double?

Thanks a lot

Joos

Could you clarify why there are multiple rows for some UsageTypes above, but with the same data in the columns shown? For example, these 6 rows are repeated later:

UsageType

OrigDesc

ApplCode

PeakCode

RoamingCode

ScopeCode

WeekCode

PersCode

MO_VOI_NAT_ONNET

MO

VOI

NON-ROAM

NAT_ONNET

MO_VOI_NAT_OTHER

MO

VOI

NON-ROAM

NAT_OTHER

MO_VOI_NAT_FIXNET

MO

VOI

NON-ROAM

NAT_FIXNET

MO_VOI_INT

MO

VOI

NON-ROAM

INT

MO_VOI_ROAM

MO

VOI

ROAM

MO_VOI_OTHER_DEST

MO

VOI

NON-ROAM

OTHER_DEST

|||

They are not the same...you have MO and MT, meaning originating and terminating.

Regards

Joos

|||

Do you want each UsageType to be independently aggregated, with no aggregation of usage across UsageTypes? If so, you could create a UsageType dimension, where the "IsAggregatable" property of the UsageType attribute is set to false. Otherwise, could you explain how you want usage to be aggregated in more detail, with an example?

http://msdn2.microsoft.com/en-us/library/ms174497.aspx

>>

SQL Server 2005 Books Online

Configuring the (All) Level for Attribute Hierarchies

...

The presence of an (All) level in an attribute hierarchy depends on the IsAggregatable property setting for the attribute and the presence of an (All) level in a user-defined hierarchy depends on the IsAggregatable property of the attribute at the top-most level of user-defined hierarchy. If the IsAggregatable property is set to True, an (All) level will exist. A hierarchy has no (All) level if the IsAggregatable property is set to False.

>>

|||

I do want aggregation of usage types, but I want to control aggregation...

I would like to include all attributes of the usage types as displayed in the table. With these attributes, I would like to build hierarchies, like for example origdesc - appl code - peak code, and origdesc - appl code - weekcode. However, for example for Voice, this is not the sum of all week, weekend, peak and offpeak usage types. Moreover, for example for mms, I do not have this distinction and ideally, I would only like to show the appl codes, for which the hierarchy applies, meaning that MMS should not occur in the given hierarchy. This last remark is impossible, I guess...

Most importantly, I would like to control which usage types should be taken to get eg the voice aggregate and eg the MO aggregate. If I would just sum up all voice usage types, this would not be correct...I would be counting more than double.

|||Well, you've given examples of how usage aggregates in some specific cases, but there's no systematic pattern that I can discern. One approach that comes to mind is a fact table where each row indicates (non-overlapping) usage, so that values there can always be aggregated. Then multiple UsageTypes could be related to each fact row via a bridge table/intermediate measure group, using the SSAS Many-to-Many dimension modelling feature.|||

I have chosen to include only those usage types that aggregate towards a real total. If there will be a need to include the others, I will do this in a seperate measure group.

Joos