Create a time dimension

Hi !
I have a question : my dataBase contains a table which have an attribute startTime.
The startTime give the day and the hour of an event.
I want to 'display' the event for a period time (one day or one week, or between to date...).
But I don't know how to do... Because if I add a dimension with this startTime, and in my report I put parameter with two date, I can only choose in the date exit in the attribut StartTime, but as I have a lot, an with a small range, I can't display all days...

I would like that the user choose a date as 27/03/07 and the report display all event in this day...
but I don't know what to do, because StartTime is as 27/03/07 10:50:42...

Can anybody help me ? Thank you !!!

In a named calculation(data source view in SSAS2005) or in a view/table(AS2000) you can try this: http://blog.mike-obrien.net/PermaLink,guid,f3363145-8753-4604-8314-855012a00400.aspx

This works with TSQL

HTH

Thomas Ivarsson

|||Hi Thomas,
Thank you for your answer...
But I have a problem not with named calculation but with the dimension, I didn't understand how to do !
As I explain last time, I have a table with a dateTime value, so I have a list of a lot of event with all this dateTime value, I want to organize them by day, So i want a time dimension that give just the day (as your answer) and which it is link with my dateTime, for example I have 5 event the 25/03/2007, 14 event the 26/03/2007...
so I don't what to do...
May I have to create a time dimension from server and my TimeDate are linked to this table ?

|||

Hello! Maybe I am wrong but I understand your problem as that you have time fragments on your date columns in the fact table and that you would like to change that?

The link I have sent you have examples that transform dates with 2007-03-29:13:30:06:01 to 2007-03-29:00:00:00 . If you build a time dimension with a datetime-column and you enter 2007-03-29 you will get 2007-03-29:00:00:00 in that column and a match between the time dimension and the fact table on dates.

Regards

Thomas Ivarsson

Create a datetime dimension table with start and end dates

Hi

I want to create a table that has a datetime column, data type=datetime and I want the date to start form 01/01/2004 00:00:00 to 01/01/2008 00:00:00 the increment will be by minute like

01/01/2004 00:01:00

01/01/2004 00:02:00

01/01/2004 00:03:00 and so on up to 01/01/2008 00:00:00

I will use this time dimension in bussiness objects.

please provide me with the SQL sript to do this.

CREATE TABLE DimensionTable

(

IdCol INT IDENTITY(1,1),

Timecol DATETIME

)

GO

DECLARE @.Startdate DATETIME

DECLARE @.Enddate DATETIME

SET @.Startdate = '20040101'

SET @.Enddate = '20080101'

WHILE @.Startdate < @.Enddate

BEGIN

INSERT INTO DimensionTable

(

TimeCol

)

SELECT @.Startdate

SET @.Startdate = DATEADD(mi,1,@.Startdate)

END

That could take some time :-)

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks a lot

Create a calculated member on one measure.

I have a Version dimension which is used to break out the Net measure by 'Actual' or 'Budget'. Net is the only measure I'm using. By not using the Version dimsion. The net is incorrectly totaled by both values. Both are not used together.

'Actual' and 'Budget' are the only two members of the Version dimension. I want to add a third member which subtracts 'Budget' Net measure from the 'Actual' Net measure and call it variance. A caculated measure isn't working for me because obviously subtracting Actual - Budget doesn't work so well.

Can anyone help me with that?

First, you need to mark your Version attribute as Not Aggregatable (set IsAggregatable property to false) - this will prevent summing up Actual and Budget.

I am not sure why is it "obviously" not working well, the following should work well for you:

CREATE Version.Variance = Version.Actual - Version.Budget;

HTH,

Mosha (http://www.mosha.com/msolap)

|||Where do this statement go? Sorry, I'm new to this.|||You add this statement to the MDX Script of the cube. It is under the Calculation tab in the Cube window. You may want to switch to Script view from Form view in order to copy it verbatim.|||Thanks for your prompt response. So I should add a 'New Script'?|||

I created a new script and added this to it by dragging the objects to the form and substituting in "Variance", but it didn't work:

CREATE [Version Dim V].[Version].&[Variance] = [Version Dim V].[Version].&[Actual] - [Version Dim V].[Version].&[Budget];

MdxScript(X_GL_Data) (6, 133) Parser: The syntax for ';' is incorrect.

Any ideas?

Thanks!

|||

OK I got it to the point where there are no errors, but the Variance member will still not show up in the Version dimension. WHat could be the cause?

/*

The CALCULATE command controls the aggregation of leaf cells in the cube.

If the CALCULATE command is deleted or modified, the data within the cube is affected.

You should edit this command only if you manually specify how the cube is aggregated.

*/

CALCULATE;

CREATE MEMBER CURRENTCUBE.[Version Dim V].[Version].[Variance]

AS [Version Dim V].[Version].&[Actual]-[Version Dim V].[Version].&[Budget],

FORMAT_STRING = "Currency",

NON_EMPTY_BEHAVIOR = { [Net Amount] },

VISIBLE = 1 ;

Thanks!

|||I see what it's doing. Its showing up in the Version_Heirarchy on the same level as 'All', not as a sibling of 'Budget' and 'Actual'. It's unusable there. How can I add it as a sibling?|||

First - please remove NON_EMPTY_BEHAVIOR - it is defined absolutely wrong.

Second, how exactly it doesn't show up ? If you connect with SSMS to the cube and browse the dimension - don't you see this calculated member ?

|||According to your original posting, you didn't want to have All to begin with. Therefore I advised you to set IsAggregatable=false - this way you won't have level/member All in the hierarchy.|||

At the time, 'all' was allowed. I didn't have it turned off. Everytime I try to set IsAggregatable to False for the Version_Key, Version_Description and the hierarchy, I get an error:

Error 1 Errors in the metadata manager. The 'Version Key' attribute with IsAggregatable=false must not have any relationship to 'Version Description' attribute that has AttributeHierarchyEnabled=true. 0 0

If I have AttributeHierarchyEnabled=False, I get two new errors:

Error 1 Dimension 'Version Dim V' > Attribute 'Version Key' : Attribute hierarchy must be enabled if attribute relationships are used. 0 0
Error 2 Dimension 'Version Dim V' > Attribute 'Version Key' : The key attribute requires attribute hierarchy enabled. 0 0

Version_Description is the only value I want to use. It contains 'Actual' and 'Budget', and should also include the 'Variance'

Thanks

|||

I finally got 'Variance' to show up with Actual and Budget, but when I try to apply it as a filter expression, I get the error below:

A set has been encountered that cannot contain calculated members.

The other two members of Version work fine.

Here is the setup:

Attribute:
Version Description: IsAggregatable=False, AttributeHierarchyEnabled=True

VersionKey: Key, AttributeHierarchyVisible=False, AtrributeRelationship to VersionDescription

Here is how the member is created:

CREATE MEMBER CURRENTCUBE.[Version Dim V].[Version Hierarchy].[Variance]

AS [Version Dim V].[Version].&[Actual]-[Version Dim V].[Version].&[Budget],

FORMAT_STRING = "Currency",

NON_EMPTY_BEHAVIOR = { [Net Amount] },

VISIBLE = 1 ;

The relationship between dimension columns and measure group columns is VersionKey>VersioKey with a granularity attribute of 'version key'. Using Version Description as the granularity attribute fails indicating it can not find the Version_key attribute key.

Thanks

|||

Finally, I got it by changing the hierarchy to the Attribute hierarchy for VersionDescription, not the hierarchy with Version Description as the only attribute/level in the hierarchy pane.

Thanks for your help in walking me through it.

|||

Mike,

I have a very similiar problem, but didn't fully understand your solution. Can you explain how you have your dimension structured and what properties you have set again descriptively in one post? I'm a little lost trying to follow the thread.

Thanks,
-Nimitt

Create a calculated member on one measure.

I have a Version dimension which is used to break out the Net measure by 'Actual' or 'Budget'. Net is the only measure I'm using. By not using the Version dimsion. The net is incorrectly totaled by both values. Both are not used together.

'Actual' and 'Budget' are the only two members of the Version dimension. I want to add a third member which subtracts 'Budget' Net measure from the 'Actual' Net measure and call it variance. A caculated measure isn't working for me because obviously subtracting Actual - Budget doesn't work so well.

Can anyone help me with that?

First, you need to mark your Version attribute as Not Aggregatable (set IsAggregatable property to false) - this will prevent summing up Actual and Budget.

I am not sure why is it "obviously" not working well, the following should work well for you:

CREATE Version.Variance = Version.Actual - Version.Budget;

HTH,

Mosha (http://www.mosha.com/msolap)

|||Where do this statement go? Sorry, I'm new to this.|||You add this statement to the MDX Script of the cube. It is under the Calculation tab in the Cube window. You may want to switch to Script view from Form view in order to copy it verbatim.|||Thanks for your prompt response. So I should add a 'New Script'?|||

I created a new script and added this to it by dragging the objects to the form and substituting in "Variance", but it didn't work:

CREATE [Version Dim V].[Version].&[Variance] = [Version Dim V].[Version].&[Actual] - [Version Dim V].[Version].&[Budget];

MdxScript(X_GL_Data) (6, 133) Parser: The syntax for ';' is incorrect.

Any ideas?

Thanks!

|||

OK I got it to the point where there are no errors, but the Variance member will still not show up in the Version dimension. WHat could be the cause?

/*

The CALCULATE command controls the aggregation of leaf cells in the cube.

If the CALCULATE command is deleted or modified, the data within the cube is affected.

You should edit this command only if you manually specify how the cube is aggregated.

*/

CALCULATE;

CREATE MEMBER CURRENTCUBE.[Version Dim V].[Version].[Variance]

AS [Version Dim V].[Version].&[Actual]-[Version Dim V].[Version].&[Budget],

FORMAT_STRING = "Currency",

NON_EMPTY_BEHAVIOR = { [Net Amount] },

VISIBLE = 1 ;

Thanks!

|||I see what it's doing. Its showing up in the Version_Heirarchy on the same level as 'All', not as a sibling of 'Budget' and 'Actual'. It's unusable there. How can I add it as a sibling?|||

First - please remove NON_EMPTY_BEHAVIOR - it is defined absolutely wrong.

Second, how exactly it doesn't show up ? If you connect with SSMS to the cube and browse the dimension - don't you see this calculated member ?

|||According to your original posting, you didn't want to have All to begin with. Therefore I advised you to set IsAggregatable=false - this way you won't have level/member All in the hierarchy.|||

At the time, 'all' was allowed. I didn't have it turned off. Everytime I try to set IsAggregatable to False for the Version_Key, Version_Description and the hierarchy, I get an error:

Error 1 Errors in the metadata manager. The 'Version Key' attribute with IsAggregatable=false must not have any relationship to 'Version Description' attribute that has AttributeHierarchyEnabled=true. 0 0

If I have AttributeHierarchyEnabled=False, I get two new errors:

Error 1 Dimension 'Version Dim V' > Attribute 'Version Key' : Attribute hierarchy must be enabled if attribute relationships are used. 0 0
Error 2 Dimension 'Version Dim V' > Attribute 'Version Key' : The key attribute requires attribute hierarchy enabled. 0 0

Version_Description is the only value I want to use. It contains 'Actual' and 'Budget', and should also include the 'Variance'

Thanks

|||

I finally got 'Variance' to show up with Actual and Budget, but when I try to apply it as a filter expression, I get the error below:

A set has been encountered that cannot contain calculated members.

The other two members of Version work fine.

Here is the setup:

Attribute:
Version Description: IsAggregatable=False, AttributeHierarchyEnabled=True

VersionKey: Key, AttributeHierarchyVisible=False, AtrributeRelationship to VersionDescription

Here is how the member is created:

CREATE MEMBER CURRENTCUBE.[Version Dim V].[Version Hierarchy].[Variance]

AS [Version Dim V].[Version].&[Actual]-[Version Dim V].[Version].&[Budget],

FORMAT_STRING = "Currency",

NON_EMPTY_BEHAVIOR = { [Net Amount] },

VISIBLE = 1 ;

The relationship between dimension columns and measure group columns is VersionKey>VersioKey with a granularity attribute of 'version key'. Using Version Description as the granularity attribute fails indicating it can not find the Version_key attribute key.

Thanks

|||

Finally, I got it by changing the hierarchy to the Attribute hierarchy for VersionDescription, not the hierarchy with Version Description as the only attribute/level in the hierarchy pane.

Thanks for your help in walking me through it.

|||

Mike,

I have a very similiar problem, but didn't fully understand your solution. Can you explain how you have your dimension structured and what properties you have set again descriptively in one post? I'm a little lost trying to follow the thread.

Thanks,
-Nimitt

create a "Yesterday", "This Week", "Last Week" calculated member

Hi all,

I'd like to add a yesterday dimension member to a new dimension, like a "Time Utility" dimension, that references the second last day of non empty data in a cube.

At the moment, I'm doing this:

Code Snippet

create member [MIA DW].[DATE TIME].[Date].[Yesterday]
as [DATE TIME].[Date].&[2007-01-01T00:00:00]

select [Measures].members on 0,
non empty [DATE TIME].[Date].members on 1
from [MIA DW]

But the [yesterday] member does not seem to belong to [DATE TIME].[Date].members?

So I guess there's two questions:

1) Can I have a new empty dimension which contains all these special members like "Yesterday" or "This Week" and "Last Week" (these last two obviously refer to a set of Dates)

2)How come the Yesterday member is not returned by the .members function?

Thanks

Greg
1.

Yes. In the DataSourceView add a named column called SpecialDates with the value 'Normal'. Then add that column as an attribute in the time dimension, and then create the calculated members on that dimensionattribute. Another possibility is to create a Named Set with the same functionality.

2.
i think calculated members are not returned by .MEMBERS. This will work I think

create member [MIA DW].[DATE TIME].[Date].[Yesterday]
as [DATE TIME].[Date].&[2007-01-01T00:00:00]

select [Measures].members on 0,
non empty ADDCALCULATEDMEMBERS([DATE TIME].[Date].members) on 1
from [MIA DW]
|||Awesome thanks.

Counting the rows in a dimension

This should be extremely simple, but I haven't found the answer yet in any MDX reference.

How do you count all the rows in a dimension using MDX?

I need to do the equivalent of this SQL below. (CustomerID is NOT the primary key by the way.)

SELECT COUNT(DISTINCT CustomerID)

FROM DimCustomer

My eventual goal is to find the count of all customers over any given time period like so:

SELECT COUNT(DISTINCT CustomerID) FROM DimCustomer WHERE

CustomerOpenDate <= CONVERT(DATETIME, '5/1/2006') AND CustomerCloseDate >= CONVERT(DATETIME, '5/31/2006')

I believe I can use Time.CurrentMember for the comparrison, correct?

Thanks,

Terry

Hi Terry,

From your problem description, you may be able to just use the standard "DistinctCount" measure aggregation function (assuming that you're using AS 2005): suppose you create a Measure Group on the DimCustomer table, with CustomerCount as a Distinct Count on the CustomerID field. If you have a Date dimension down to the daily level (similar to Adventure Works), it can be related to the this measure group via 2 roles: CustomerOpenDate and CustomerCloseDate. The query could then be like:

select {[Measures].[CustomerCount]} on 0

from CustomerCube

where (NULL:[CustomerOpenDate].[Day].[05/01/2006],

[CustomerCloseDate].[Day].[05/31/2006]:NULL)

Of course, depending on the day range sizes, these could be optimized to use higher levels of the hierarchy.

|||

Thanks for your help. I think what I'm really trying to get to is a calculated measure with the where clause for the CustomerOpenDate and CustomerCloseDate built into it so that the customer count will total correctly whatever the the current date granularity is.

I think I can can handle the where clause part (although your syntax of "NULL:" and ":NULL" is something I haven't seen before), but I can't get the syntax for doing a distinct count on Customer.CustomerID in the calculated measure. I get error messages with the various ways I've tried it saying that it's either not a tuple or that no hierarchy has been specified.

|||

Hi Terry:

I think you're looking for something like the following the calculated member. The following query works against the Adventure Works DW so you can exercise it and see the results. The calculated member, Measures.[My Customer Count], counts the distinct number of customers who had sales activity during the period selected. I imply sales activity by filtering on the [Internet Sales Amount].

Hope this helps.

PGoldy

WITH
MEMBER Measures.[My Customer Count] AS
'
DISTINCTCOUNT(FILTER([Customer].[Customer Geography].[Customer].Members * [Date].[Calendar].CurrentMember, NOT ISEMPTY([Measures].[Internet Sales Amount])))
'

SELECT
{Measures.[My Customer Count]}
ON COLUMNS
,{[Date].[Calendar].[Month].&[2003]&[4]
,[Date].[Calendar].[Month].&[2003]&[5]}
ON ROWS
FROM [Adventure Works]

|||Thank you for your help. I still haven't pulled it all together yet, but I'll look into it some more using the help you've provided.

Counting siblings

I have an "instrument" dimension [aka RIC], and each instrument has an "underlying" attribute (aka RIC 1). I'd like to list all instruments, and for each instrument, show the count of other instruments with the same underlying.

this is what I've got so far - sad to say it's not working - what should I be doing here?

with member [measures].[x] as count(filter([wm instrument].[RIC].[RIC],[wm instrument].[RIC 1].CurrentMember)

select

[measures].[x] on 0,

[wm Instrument].[RIC].[RIC].Members on 1

from [itdev1 hk]

Here's an example from Adventure Works which shows how to count the number of Products in the same Category as the current Product:

with member measures.prodsinsamecat as

count(

exists([Product].[Product].[Product].members,

exists([Product].[Category].[Category].members, [Product].[Product].currentmember)

)

)

select measures.prodsinsamecat on 0,

[Product].[Category].[Category].members

*

[Product].[Product].[Product].members on 1

from [Adventure Works]

What it does is first of all finds the member(s) on the Category attribute that exist with the currentmember on the Product attribute, then finds and counts the members on the Product attribute that exist with these Categories.

HTH,

Chris

|||

And improving a little over Chris's suggestion, given the fact that there is attribute relationship defined between Product and Category, a simpler and more efficient way to do it would be

with member measures.prodsinsamecat as

count(exists([Product].[Product].[Product].members, [Product].[Category].currentmember))

select measures.prodsinsamecat on 0,

[Product].[Category].[Category].members

*

[Product].[Product].[Product].members on 1

from [Adventure Works]

|||

thanks, that helps. any idea how i could order the output by measures.productsinsame cat? I tried this, no joy:

with member measures.prodsinsamecat as

count(exists([WM INSTRUMENT].[RIC].[RIC].members, [WM INSTRUMENT].[UL RIC].currentmember))

select

measures.prodsinsamecat on columns,

order([WM INSTRUMENT].[UL RIC].[UL RIC].members*[WM INSTRUMENT].[RIC].[RIC].members, measures.prodsinsamecat) on rows

from [itdev1 hk]

|||

also, I actually want to show JUST the "products", rather than the crossjoin of products and categories.

however, if I take the category out the crossjoin operation, I get a list of all my "products", with the same number of prodsinsamecat

with member measures.prodsinsamecat as

count(exists([WM INSTRUMENT].[RIC].[RIC].members, [WM INSTRUMENT].[UL RIC].currentmember))

select

measures.prodsinsamecat on columns,

order([WM INSTRUMENT].[RIC].[RIC].members, measures.prodsinsamecat) on rows

from [itdev1 hk]

interestingly, if I take out the product out of the crossjoin operation, I get a list of all my "categories", with the correct number of prodsinsamecat along side each one

with member measures.prodsinsamecat as

count(exists([WM INSTRUMENT].[RIC].[RIC].members, [WM INSTRUMENT].[UL RIC].currentmember))

select

measures.prodsinsamecat on columns,

order([WM INSTRUMENT].[UL RIC].[UL RIC].members, measures.prodsinsamecat) on rows

from [itdev1 hk]

any insights gratefully received.