Date Range for Report built against cube using MDX query

Hi,

I am trying to filter data within my report by a date range (FromDate - ToDate), which is using a cube as a datasource.

My Issue:

I have the filtering working ok but if i select a date which is outside the range of the data within my cube for example if i select the starting date for the range as 1/Jan/1965 but by data starts from 15/Jan/1965 then no data is returned.

Within the MDX query within the STRTOSET function i am using 'constrained' which is around the date parameter i.e. StartDate for Range.

My question is has anyone or is it possible to use date values outside of the range of the data within my cube and get a correct dataset returned. If so could you please explain how with an example.

Thanks

MDX sets do not really work like this, they are made up of sets of discrete members and while they are ordered and you can get ranges of members, the idea of a between predicate does not really map well. I usually try to constrain my report parameter so that users cannot pick values outside of the valid member range.

If you can't do this you could probably use the filter function, provided that you had the actual date value stored somewhere (the MemberValue property is ideal for this). Which would make your range statement something like this:

filter( <date dim>.<hierarchy>.<date level>.Members

, <date dim>.<hierarchy>.MemberValue >= <fromDate>

and <date dim>.<hierarchy>.MemberValue <= <toDate>))

But this is going to be a lot slower than directly specifying a start and end member directly.|||

Hi,

Thanks for your reply. I managed to solve this issue by creating a time dimension with all the possible date combinations for the next five years and previous year which could occur in my data. Performance was an issue that is why this solution implemented.

|||Yeah, that's what I do aswell. And 5 years is only 1500 members which is not big as dimensions go.

Date Range for Report built against a cube using MDX query

Hi,

I am trying to filter data within my report by a date range (FromDate - ToDate), which is using a cube as a datasource.

My Issue:

I have the filtering working ok but if i select a date which is outside the range of the data within my cube for example if i select the starting date for the range as 1/Jan/1965 but by data starts from 15/Jan/1965 then no data is returned.

Within the MDX query within the STRTOSET function i am using 'constrained' which is around the date parameter i.e. StartDate for Range.

My question is has anyone or is it possible to use date values outside of the range of the data within my cube and get a correct dataset returned. If so could you please explain how with an example.

Many Thanks

Absolutely it is possible. Have you tried removing the CONSTRAINED flag? Also do a search of this forum, i have posted several items regarding using dates and MDX within reports.

|||

Hi,

Thanks for your reply. I managed to solve this issue by creating a time dimension with all the possible date combinations for the next five years and previous year which could occur in my data. Performance was an issue that is why this solution implemented.

Date filter in MDX

Hi guys,

I have a cube where I want to make filter based on date range. I've created a cube with appropriate measures and I have Dimension with date field ( I want to filter on that field).

I've found two solutions:

FILTER([Reservation Search Log].[Reservation Search Log].ALLMEMBERS,
CDate([Reservation Search Log].[Reservation Search Log].Properties( "Simple Insert Date" ))>= "2/6/2007" --'2/6/2007'
AND CDate([Reservation Search Log].[Reservation Search Log].Properties( "Simple Insert Date" )) <= "2/7/2007" --'2/7/2007'

but this stopped working after I applied SP2 - i just get null values for all measures

and this one

[Reservation Search Log].[Simple Insert Date].&[February 1, 2007] :
[Reservation Search Log].[Simple Insert Date].&[February 10, 2007]

but in I would like to pass these dates as parameters. and if both dates are outside the range I get no results. In real case I should get all rows like this
data in SSAS February 1, 2007 to February 10, 2007
I pass January 1, 2007 to March 10, 2007
and no result are returned

If there is no appropriate records for both date parameters no rows are returned...

Any ideas are highly appreciated.Filter is not really an efficient way to do this, if possible I usually try to populate drop down lists of members in reports, but sometimes this is not always practical. Have you tried converting your comparison values to dates as well?

FILTER([Reservation Search Log].[Reservation Search Log].ALLMEMBERS,
CDate([Reservation Search Log].[Reservation Search Log].Properties( "Simple Insert Date" ))>= CDate("2/6/2007") --'2/6/2007'
AND CDate([Reservation Search Log].[Reservation Search Log].Properties( "Simple Insert Date" )) <= CDate("2/7/2007") --'2/7/2007'|||Hi Darren,

Thank very much for helping me with this....

I don't get what do you mean with converting comparison values to dates...

the example I posted is from real MDX query in which I got that doesn't work on SP2 and both sides are converted using CDate before comparison is made

Any other ideas? What is the best way to achieve such goal?|||

The sample you posted only had one side converted to a date, the other was a string value. The example you posted has

>= "2/6/2007"

as the first comparison, which is a comparson to a string value where as if you look at what suggested in my last post it was to use something like:

>= CDATE("2/6/2007")

I tested this in SP2 and it works for me.

|||

this is interesting...

I had this filter clause in where clause and I had the (null) problem...

I moved filter clause in select in subquery and everything is fine now...

Sorry for missing the changes in your post. I've made so many tests and I thought I posted same as you

Thanks a lot, Darren

Date Dimensions

Hi,
I am new to this OLAP stuff.
I am trying to create a cube which has a count of sales per month per year.
How do you make a datetime field a dimension of a cube?
I seem to specify the dimension OK and get the Year Month heirarchy I want
(simple star dimension and a MOLAP) but when I process the cube it gives a
datasource provider error Initialising the dimension.
'Data Source provider error: ; Time:16/03/2005 22:31:32 '
I have tried making a view with the date cast to 101 and creating the cube
on that view but no difference.
thanks
Bob
Hi,
Problem is more general than a date dimension. It is a general failure. I
was misinterpreting the error message.
I shall do some more investigation.
"Bob" <bob@.nowhere.com> wrote in message
news:%23Ja74vgKFHA.3392@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am new to this OLAP stuff.
> I am trying to create a cube which has a count of sales per month per
year.
> How do you make a datetime field a dimension of a cube?
> I seem to specify the dimension OK and get the Year Month heirarchy I
want
> (simple star dimension and a MOLAP) but when I process the cube it gives a
> datasource provider error Initialising the dimension.
> 'Data Source provider error: ; Time:16/03/2005 22:31:32 '
> I have tried making a view with the date cast to 101 and creating the cube
> on that view but no difference.
> thanks
> Bob
>
|||How is your date dimension designed? The standard is -
PK Year Month Week Date
Then when you set the dimension up in AS, set it as a star, and set the levels,
year down to date. That way you can drill down to the day.
-- Brian
[vbcol=seagreen]
> Hi,
> Problem is more general than a date dimension. It is a general
> failure. I
> was misinterpreting the error message.
> I shall do some more investigation.
> "Bob" <bob@.nowhere.com> wrote in message
> news:%23Ja74vgKFHA.3392@.TK2MSFTNGP10.phx.gbl...
> year.
> want

Date Dimensions

Hi,
I am new to this OLAP stuff.
I am trying to create a cube which has a count of sales per month per year.
How do you make a datetime field a dimension of a cube?
I seem to specify the dimension OK and get the Year Month heirarchy I want
(simple star dimension and a MOLAP) but when I process the cube it gives a
datasource provider error Initialising the dimension.
'Data Source provider error: ; Time:16/03/2005 22:31:32 '
I have tried making a view with the date cast to 101 and creating the cube
on that view but no difference.
thanks
BobHi,
Problem is more general than a date dimension. It is a general failure. I
was misinterpreting the error message.
I shall do some more investigation.
"Bob" <bob@.nowhere.com> wrote in message
news:%23Ja74vgKFHA.3392@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am new to this OLAP stuff.
> I am trying to create a cube which has a count of sales per month per
year.
> How do you make a datetime field a dimension of a cube?
> I seem to specify the dimension OK and get the Year Month heirarchy I
want
> (simple star dimension and a MOLAP) but when I process the cube it gives a
> datasource provider error Initialising the dimension.
> 'Data Source provider error: ; Time:16/03/2005 22:31:32 '
> I have tried making a view with the date cast to 101 and creating the cube
> on that view but no difference.
> thanks
> Bob
>|||How is your date dimension designed? The standard is -
PK Year Month Week Date
Then when you set the dimension up in AS, set it as a star, and set the leve
ls,
year down to date. That way you can drill down to the day.
-- Brian
[vbcol=seagreen]
> Hi,
> Problem is more general than a date dimension. It is a general
> failure. I
> was misinterpreting the error message.
> I shall do some more investigation.
> "Bob" <bob@.nowhere.com> wrote in message
> news:%23Ja74vgKFHA.3392@.TK2MSFTNGP10.phx.gbl...
> year.
>
> want
>

Date dimension Sort order

Hi-

I have a date dimension with year (number), month(Month name), and day(number) as part of a cube which I'm generating an offline cube from.

While browsing the cube in either VS or in the offline cube, through Excel, the months end up sorted alphabetically (April, August...) and the days of the month are sorted in the fashion of 1,10,11...2,20,21...

Anyone have any suggestions or ideas of what I'm doing wrong?

Thanks,

Tristan

You should mark your days as 01, 02, 3... to avoid the order issue you are seeing.

For the month, you should create a Month of year attribute (Jan - 01, Feb - 02, etc...) and then use the sort property ont he month attribute to sort it by the Month of Year attribute....

|||That worked perfect! Thanks much!

date design issue in my cube

I have a simple fact_service table: service_type, start_date, end_date, service_charge. If the service is not done yet, the end_date is null. I can't process the cube because I don't have a null record in my dim_date dimension.

I tried to create a vew v_fact_service_by_end_date, that processes the cube but it doesn't give me what I needed. I would end up with one time dim and two measures: charge_amt and charge_amt_from_view.

I need to have two time_dim (roll play) and one measure (charge amt) so that I can see the start_date and end_date of each service_type (to determine how long it takes to perform the service).

How should I model this? Thanks for any input.

This is a relatively common issue. You have what we refer to as an accumulating snapshot fact table wherein some dimensions are not populated at the time a transaction is first loaded. One question, do you have a transaction identifier? Is that the Service_Type reference?

Regarding the date, is this a date time field or an integer pointer to a Date dimension table? If you had a Date dimension table such as below, you could simply add a NULL date and set a reference to that record (DateID = -1):

Code Snippet

create table DateDimension (

DateID int not null identity(1,1),

Date datetime null,

....

)

alter table DateDimension add

constraint PK_DateDimension primary key (dateid),

constraint AK_DateDimension unique (date)

go

set identity_insert DateDimension ON

insert into DateDimension (DateID, Date) values(-1, NULL)

set identity_insert DateDimension OFF

go

If you aren't using a Date dimension table, your other option is to use the UNKNOWN MEMBER functionality in SSAS. You have to configure an unknown member in the dimension and also configure how you want your cube to handle missing references. Check out Books Online topic "Defining the Unknown Member and Null Processing Properties" for more details.

Hope that helps,
Bryan

|||That worked! I added a dummy record of "-1" in the time_dim. Thanks much.

date design issue in my cube

I have a simple fact_service table: service_type, start_date, end_date, service_charge. If the service is not done yet, the end_date is null. I can't process the cube because I don't have a null record in my dim_date dimension.

I tried to create a vew v_fact_service_by_end_date, that processes the cube but it doesn't give me what I needed. I would end up with one time dim and two measures: charge_amt and charge_amt_from_view.

I need to have two time_dim (roll play) and one measure (charge amt) so that I can see the start_date and end_date of each service_type (to determine how long it takes to perform the service).

How should I model this? Thanks for any input.

This is a relatively common issue. You have what we refer to as an accumulating snapshot fact table wherein some dimensions are not populated at the time a transaction is first loaded. One question, do you have a transaction identifier? Is that the Service_Type reference?

Regarding the date, is this a date time field or an integer pointer to a Date dimension table? If you had a Date dimension table such as below, you could simply add a NULL date and set a reference to that record (DateID = -1):

Code Snippet

create table DateDimension (

DateID int not null identity(1,1),

Date datetime null,

....

)

alter table DateDimension add

constraint PK_DateDimension primary key (dateid),

constraint AK_DateDimension unique (date)

go

set identity_insert DateDimension ON

insert into DateDimension (DateID, Date) values(-1, NULL)

set identity_insert DateDimension OFF

go

If you aren't using a Date dimension table, your other option is to use the UNKNOWN MEMBER functionality in SSAS. You have to configure an unknown member in the dimension and also configure how you want your cube to handle missing references. Check out Books Online topic "Defining the Unknown Member and Null Processing Properties" for more details.

Hope that helps,
Bryan

|||That worked! I added a dummy record of "-1" in the time_dim. Thanks much.

date calculations using a time hierarchy

Hi There

I am a relative newbie at SSAS 2005, but I've created a working cube and populated it with data.

I have a fact table with three date fields:
[record date]
[start date] - date the product started
[end date] - date the product was withdrawn
+ some other data fields
I use the [record date] to analyse my data with a server-populated time dimension. So far so good, it works fine.
However I need to look at how many days the products were active during the current time period. For exampe, if I'm in Q1 of 2005 and my product ran from 1st december 2004 to 15th january 2005, the answer is 15 days. This needs to be totaled across various other dimensions too.
I have tried a lot of different ways of doing it without much success. Any ideas on how to implement something like this?

ThanksCould you explain the schema, and the granularity of the fact table - for example, is there only 1 record per product, or 1 record per sales transaction? In the latter case, shouldn't the product start and end dates be associated with a Product dimension, rather than with each transaction?|||It's a simplified proof-of-concept project, so I can understand how it works before trying it out on the full database which is more complex.
These are financial products which have a start and end date. There is one record per product and they are to be analysed by various dimensions, for example product type, which would be the maximum granularity (not the products themselves). I also need to look at them by other time dimensions, for example when the records appear in the database, which may be any time after they have actually incepted. So I could have product type on rows and time on columns, and the cells would be the sum of all the days of activity for a given product type for that time period. Or I could have 'appearence time' on rows, so each cell would be the sum of active days for products during the colum time, for all products appearing in a given time frame.
Hope this is clear enough? I realise this is maybe a bit general but I'm only after a general approach strategy rather than an in-depth analysis! Thanks
oh and Merry Christmas!