Dimensions, Table Joins, and Missing Elements

I have situation where I'm using a element in SQL table which down the hierarchy.
Such as:

Table1 primary key
|
Table 2 foreign key primary key
|
Table 3 foreign key

The column I using for a dimension is in table 3. Now the problem is that not every row in table 2 is going need to have data in table 3. In other words, all of table 2 primary keys are not necessarily going to have a reference foreign key in Table 3.
Now that is all correct and is the nature of the project I'm working on. In regular SQL, select statements work just fine... meaning that selecting an element in Table 3 would display that element and also make Table 3 act as a filter via an INNER JOIN.

Now the problem is that AS2005 throws an error when processing a cube like this. Now I can make SQL views and use them instead of tables to eliminate this problem. But is that the best way to overcome this? How would I implement something like an INNER JOIN in an AS cube?

Thanks!

AS2005 has several options in dealing with such RI issues, please read the following useful articles on this subject:

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

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

|||That's exactly what I needed to know. Thanks!

Dimensions, Table Joins, and Missing Elements

I have situation where I'm using a element in SQL table which down the hierarchy.
Such as:

Table1 primary key
|
Table 2 foreign key primary key
|
Table 3 foreign key

The column I using for a dimension is in table 3. Now the problem is that not every row in table 2 is going need to have data in table 3. In other words, all of table 2 primary keys are not necessarily going to have a reference foreign key in Table 3.
Now that is all correct and is the nature of the project I'm working on. In regular SQL, select statements work just fine... meaning that selecting an element in Table 3 would display that element and also make Table 3 act as a filter via an INNER JOIN.

Now the problem is that AS2005 throws an error when processing a cube like this. Now I can make SQL views and use them instead of tables to eliminate this problem. But is that the best way to overcome this? How would I implement something like an INNER JOIN in an AS cube?

Thanks!

AS2005 has several options in dealing with such RI issues, please read the following useful articles on this subject:

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

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

|||That's exactly what I needed to know. Thanks!

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!

Dimensions with Startdate / Enddate fields

Hi,

I would like to know what is the best practice to manage Dimensions with StartDate / EndDate fields, in SSAS.

Regards

Ayzan

Well first, is it possible to build dimensions with time dependance ?

Regards

Ayzan

Dimensions with complex hierarchies ?

Please advise...

I have a dimension hierarchy that each member could have multiple parents and therefore the out-of-box ParentKey support in dimension would not be sufficient.

Based on Mr. Kimball recommendation ( http://www.dbmsmag.com/9809d05.html ) I tried to use a linked helper dimension that has only ParentKey and ChildKey. However I am having difficulty utilizing it in the cube browser. The similary user experience we have with the out-of-box hierarchy is missing in this solution.

I am wondering if anyone in this forum has done something similar or know a better way of doing it.

Appreciate any help or comment.

Best Regards,

This is not natively supported by AS, but you can make it work as follow:

Create a duplicate member with same member name but different member key: ie: Member 1 (Key=1) and Member 1 (Key=2)

The original member is the one linked to your fact data Member 1 (Key = 1)

Associate a custom member formula to the shadow member (Key = 2): Member formula = Dimname.hierarchyname.$[1]

You’re set.

|||

In AS2K5, you created this via a many-to-many dimension. The "linked helper dimension" in Kimball terminology is implemented as a measure group.

_-_-_ Dave

sql

Dimensions with complex hierarchies ?

Please advise...

I have a dimension hierarchy that each member could have multiple parents and therefore the out-of-box ParentKey support in dimension would not be sufficient.

Based on Mr. Kimball recommendation ( http://www.dbmsmag.com/9809d05.html ) I tried to use a linked helper dimension that has only ParentKey and ChildKey. However I am having difficulty utilizing it in the cube browser. The similary user experience we have with the out-of-box hierarchy is missing in this solution.

I am wondering if anyone in this forum has done something similar or know a better way of doing it.

Appreciate any help or comment.

Best Regards,

This is not natively supported by AS, but you can make it work as follow:

Create a duplicate member with same member name but different member key: ie: Member 1 (Key=1) and Member 1 (Key=2)

The original member is the one linked to your fact data Member 1 (Key = 1)

Associate a custom member formula to the shadow member (Key = 2): Member formula = Dimname.hierarchyname.$[1]

You’re set.

|||

In AS2K5, you created this via a many-to-many dimension. The "linked helper dimension" in Kimball terminology is implemented as a measure group.

_-_-_ Dave

Dimensions from Fact tables

Most of the Fact tables I'm building seem to have a bunch of categorization type columns in addition to the numeric columns that naturally fit into the Fact tables. However it feels "wrong" to create dimensions from a Fact table, so instead I'm getting the ETL to split the tables: for every FactX table I get a DimXInfo table that has the non-numeric/non-key data.

E.g.
FactDeal includes dealID, price, quantity, product key, customer key (and other keys that relate to shared dimensions.
DimDealInfo table includes dealID, ContractNumber, DealType

Then in the SSAS cube DimDealInfo naturally forms a Dimension and FactDeal naturally forms a measure group. And you don't have to create a SSAS Dimension from the Fact table.

Question is does this way of doing it make sense. It makes life easier at cube level but at ETL level you're getting two tables that are one to one relationship which also feels kind of "wrong". Any suggestions ?

Yes, in my case it does make sense. I use it with weblogs, and if I want to parse these into a datacube, I need to make keys for every occurence with the global @.@.IDENTITY variable.
Wich I think is really wrong.|||

Degenerate dimensions are not evil!

Indeed there are some business scenario where they are useful and sometimes the only way to model your data.

Using it is a "simple" choice if you have some alternatives (but if you have alternatives maybe you should not use DD!) or a "must" if you haven't.

In my opinion if it seems your unique solution and you don't have performance or other kind of problem (during ETL and process)...why not?

|||

Hello. This is a link to a good article about a method of what to do with fact or degenerate dimensions. It will be appropriate for SSAS2005:

http://www.intelligententerprise.com/000320/webhouse.jhtml

I think that you are on the right track.

HTH

Thomas Ivarsson

|||

Thanks everyone. I think I'm getting closer to the answer now! Yes it is degenerate dimensions I'm dealing with. In some cases I have followed Ralph Kimball's suggestion of a "Junk Dimension" to group together a few apparently unrelated items and get them out of the fact table. This works for items where there are few distinct values. (I think I should move e.g. DealType to a "Junk Dimension")

I guess the remaining item really is "ContractNumber". There is going to be a separate distinct value for every row in the Fact Table. My reasons for taking it out to a separate Dimension table are 1) Remove all text/character based columns from Fact Table to make it run faster and 2) because I need users to be able to drill right down to individual deal level when they browse a cube and the only way I can see to do that is inlcude it as a dimension.

I'm guessing that my reason 1 is not valid because we're only talking one or maybe a few columns, so probably won't have significant performance hit. But what about reason 2, can you some how drill down to a descriptive field of individual row without having to make that field a dimension ? There was something in Analysis Services 2000 that let you view all the rows that made up a specific value but can't see it in SQL 2005 and besides didn't have a client to handle it, so in the end I added ContractNumber as a dimension so any client can drill down to single row level. (Clients I'm using are : SSRS and Excel 2003).

|||

Hello.

Perhaps you are thinking about drillthrough? You can find them under actions in the cube editor.

From a contract number/id you can probably create artifical levels above the single contract by using the first two/three/four characters as levels. It is better to build these levels in a dimension.

You can create these levels in named calculations by using the TSQL function LEFT().

Regards

Thomas Ivarsson

Dimensions and Fact tables

If you have two fact tables that share a dimension is it better to duplicate
the dimension by adding a copy of it to the warehouse or should you share
the single dimension between the fact tables?based on what you are asking - in my opinion i would opt to share the
dimension. trying to keep a second copy in sync seems like it would be added
overhead with very little benefit.
time dimension would be a good example of this - no sense in having multiple
time dimensions.
there are some good books out there on this stuff - look for books from
ralph kimball.
hth
Sal
"Stephen" <swrothfuss@.hotmail.com> wrote in message
news:%23JNCPSVAEHA.392@.TK2MSFTNGP12.phx.gbl...
> If you have two fact tables that share a dimension is it better to
duplicate
> the dimension by adding a copy of it to the warehouse or should you share
> the single dimension between the fact tables?
>

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 loading best practice?

It has been suggested that when loading dimensions, the first step should be to clear the current dimension table and reload in total from the data source. I use integer identity PK fields in all my dimension tables.

When loading my fact table I'm only loading rows that have been added or changed since the last SSIS run.

Wouldn't this introduce the possibility of mis-matched key relationships if, for some reason, the order of the rows in the dimension table changed and a fact row that doesn't get modified in this SSIS run pointed to a surrogate key in a dimension table that was changed?

I suppose in most cases a change in the OLTP that caused a reordering of the dimension table - say a customer was dropped - would show up as a modification in the fact load and cause the row to be updated. However if someone changed the indexing in the OLTP it might cause the customer order to chagne causing them to be loaded in a different sequence with different surrogate keys in the dimension table.

It just concerns me that a possibility for corruption seems to exist in this type of scenerio. It seems like one should load the dimensions and facts in the same manner.

Who suggests truncating dimensions before loading them? This is not good practice. New rows should be inserted and existing rows should be updated. If you need to track changes (Slowly Changing Dimension), then you should use the SCD wizard inside SSIS.|||

John:

I am a student of the Kimball method of data warehousing. In most of my experience I have never completely rebuilt a dimension during each update, for precisely the same reason you mention. In my experience most changes in dimensions are handled using the Slowly Changing Dimension process, and SSIS provides a very elegant tool to do this for you.

I am not sure what your fact is, but unless you have scheduled a regular roll-out of data from your warehouse, say you only wanted to maintain a specific time period of data in your warehouse, you should not really be deleting rows from your dimension. If you have a very large dimension that you wish to purge occassionally I suggest that you devise a process that will account for the fact relationships that exist with the dimension records that are marked for deletion.

On a regular basis though it is not typical to completely refresh dimensions during each update.

|||

Thanks! I thought it came from the Kimball video posted here but I could have misunderstood something I saw on the screen that was being used for something else or it might have been from one of my books. It seemed too easy to be workable. However it's not that big a deal to treat dimension loading the same as facts.

I'm lucky in that I don't have any slowly changing data (yet) so I can treat all my modifications as simple updates. And every row in my OLTP has a CreateDate, ChangeDate, DeleteDate attached to it.

|||The only thing I truncate in my data warehouses are aggregated fact tables. The detail fact tables undergo inserts only, while aggregates (monthly data, for instance) are just roll ups of the detail tables. Instances like this are perfect for truncations since they need to be rebuilt every night.|||

Now that I'm looking at incremental updates of my dimensions I forsee another problem.

My sales date comes from the same OLTP table as most of my facts. I don't have a separte date table. So to load the date dimension I'm going to read the same rows as I would for loading my fact table. My OLTP row will alert me that the row has been modified but NOT whether the date column has been modified.

Also, I assume I'm going to be picking up several thousand sales each day so there will be several duplications. Is this why some designs, including some from microsoft, leave the date field in the fact table and let SSAS generate date dimensions from there?

|||

You do not need to have a data source of a Time Period table. You can very simply create a time period dimension using SSIS or a stored procedure for a time period that spans the entire history and then several years into the future, and the appropriate grain of your fact.

Again, it is typical that you have a very robust Time Period dimension that has all the attributes users would need to slice the data along.

|||

John:

I suggest you start a new thread on this topic of Time Dimension as you have already marked this post as being answered. This way others will look at the post.

Dimension Access for Roles in AS 2005

When creating a role in AS 2005 and specifying dimenison access (on the Dimensions tab of the role definition in BI Dev Studio), why do some dimensions have the option of "None" as access level while others only have "Read" and "Read/Write"?

For example, in looking at the Adventure Works sample solution for AS 2005, if you add a role and then go to the Dimensions tab of the role definition, you'll see that Account, Department, Organization, and Scenario only have "Read" and "Read/Write" as access options. All the others have those two as well as "None".

At first, I thought that it was because of some unique setting for those four dimensions. But I haven't found anything just by browsing their definitions. Then I thought it was perhaps due to some unique usage of those four dimensions within the cube design. They are all used with the Financial Reporting measure group, but so is the Date dimension and it has "None" as an access option.

Anyone have any ideas? Probably something simple that I'm just overlooking, but I'd like to know why I can't set any of these to "None".

Thanks!

Dave Fackler

Did you ever find an answer to this? I'm having the same issue.

Dimension Access for Roles in AS 2005

When creating a role in AS 2005 and specifying dimenison access (on the Dimensions tab of the role definition in BI Dev Studio), why do some dimensions have the option of "None" as access level while others only have "Read" and "Read/Write"?

For example, in looking at the Adventure Works sample solution for AS 2005, if you add a role and then go to the Dimensions tab of the role definition, you'll see that Account, Department, Organization, and Scenario only have "Read" and "Read/Write" as access options. All the others have those two as well as "None".

At first, I thought that it was because of some unique setting for those four dimensions. But I haven't found anything just by browsing their definitions. Then I thought it was perhaps due to some unique usage of those four dimensions within the cube design. They are all used with the Financial Reporting measure group, but so is the Date dimension and it has "None" as an access option.

Anyone have any ideas? Probably something simple that I'm just overlooking, but I'd like to know why I can't set any of these to "None".

Thanks!

Dave Fackler

Did you ever find an answer to this? I'm having the same issue.

Different reportparameters for same dimension in different cube

Hi,

I have 1 report with 2 charts, both charts have their own dataset. The two datasets are mdx queries on 2 different cubes, but some dimensions have the same name.

Now I want to have 2 differenent selectable parameters for the [dim time] dimension. One for the first query in the first cube and the second for the other query in the other cube .

So I check in the mdx query builder, both dimensions as parameter, but because both dimensions have the same name, i have only one selectable [dim time] -parameter in my report.

How can i solve this?

Thanks,

Dennis

Dear Dennis

Please help me to pass a parameter thru reportbulder to get drill down from report1 to jump into report2 . I created report1 and report 2. I want to jump into report 2 using parameter . I am getting one error when I run the report1 after giving drill throu in property page of the report in reportBulder

"Query Parameter missing " . Please help me

regards

Polachan

|||

Hi Dennis,

You can solve this by mapping a second parameter to the dataset.

Under Report > Report Parameters, Add a new parameter.

Create a new name and copy over the rest of the information from the parameter created by the MDX designer.

Within the properties of the second dataset (click the ellipses next to the DataSet name), modify the Parameters Value to point at the new parameter you just created.

That should give you two parameters, each mapped to the appropriate dataset.

HTH,

Jessica

Different operations along dimensions

I am having a stupid problem with my cube...

The data I have is a floating point number between 0 and 1, representing the utilization of an item (machine, production line), in percent for a time period. Let's say, it was 0.5 for Monday and 0.1 for Tuesday.

Not really the 99.9% reliability we usually look for, hehe - but that is another story. For the examples these low numbers are better and easier.

Anyway, if I want to see the average for Monday and Tuesday I use exactly that function: AVG as aggregation in my cube, and I am done. With the example above, I would get a 30% usage of my production machine. (0,5 + 0,1) / 2 in math speak.

My question now is this: I have not one machine, but several. Along this "machine number" dimension, I do NOT want to average, but really sum up the values: Let's say my 2nd machine did 0,8 for Monday, together with the #1 machine that did 0,5 I'd like to see the Monday really as 1.3, or 130% - the actual display of the number is no issue.

Sounds easy, but somehow I have no idea how to do that. Can I have different functions for each dimension that aggregates a value? Or is that a custom MDX script?

Have you tried changing your measure to use the AverageOfChildren aggregate function (instead of the Sum function)? (This is a measure property, not something in the calc script.) What you have is a semi-additive measure... it should be summed across all dimensions except for the Timem dimension. That's what the AverageOfChildren, LastNonEmpty, etc. aggregate functions are for.|||

Spot on! THANKS!

Defining the dimension as Time and using AverageOfChildren did solve it.

But...

What if I have several time dimensions? I might be doing something wrong, but it seems the AverageOfChildren does the Avg only on the FIRST time dimension in the cube - the other time dimensions are treated as the regular ones... does that make sense?

In my case, having date (year/date) and time (hour/minutes) in separate dimension I think I can solve by cross joining the tables and use that as source for one dimension. But what in a case where you have truly different dates in your cube, say an order date, one delivery date and maybe one invoice date, yet you need your counter to be "avg" or "last" or so along EACH of these datetime dimensions?

|||

You're correct that semi-additive measures sum across all dimensions except the first time dimension. Usually this is what you want... for instance, take an Inventory Status measure group which is a daily snapshot of all your inventory. Besides your main Date dimension, you might also have a Received Date dimension. If you sliced by Received Date, you're wanting to slice down to inventory which was received on that date, but the semi-additive behavior should operate on the main Date dimension not the Received Date dimension.

Your situation is probably not the common one, so you may have to resort to using the calc script to detect which date dimension you have sliced by.

I would avoid putting the days and time of day within the same dimension if you can. It's a simple dimension size concern... a normal Date dimension which goes down to day has 365 members per year. But if you go down to second, it has 31,536,000 members.

Different Aggregation Function for Single Measure

BOL alludes to being able to set a different aggregation function for a measure for different dimensions/hierarchies. In the June CTP, the link in BOL is as follows:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uas9/html/c359b4c1-9c3f-41bc-a585-de7c934e2c11.htm

It states that an aggregation function can be set on the measure (as the default, using the AggregateFunction property) in the Properties pane of the Cube Designer. Which is fine.

But, it also states that an aggregation function can be specified for a particular measure when aggregated along a specific hierarchy. The problem is, it doesn't state where this might be done in the Cube Designer and I can't seem to find any property setting or other setting that might lend itself to doing this.

Being able to specify a different aggregation function for a measure based on the hierarchy involved would be very useful. For example, a dimension with multiple date dimensions or hierarchies using different aggregation functions to apply slightly different additive or semiadditive aggregations.

Anyone know how to do this?

Thanks...

Dave Fackler

A way to do this is in the calculations for the cube. Set a scope (for your measure), set a scope for your dimension, the change the value of the calculation. For example:

where the aggregation method for [myMeasure] is SUM:

SCOPE [Measures].[myMeasure];
SCOPE leaves([Region]);
this = [Measures].[myMeasure] * 2;
END SCOPE;
END SCOPE;

Note: the specifics depend strongly on the aggregation effect you're trying to achieve. I've often found that I needed to approach the problem in reverse, to get the results I wanted.

Good luck.