Date range ...

Hi,
I have to count the number of invoices between two dates (starting date and ending date).
I have a time dimension, and a another with the two dates.
I've created a measure :

Code Snippet

CREATE MEMBER [CTNAV].[Measures].[Nb]
AS count(
Filter
(
[Bon Vouchers].[N° Bon Vouchers].[N° Bon Vouchers].Members,
[Bon Vouchers].[Starting Date ].CurrentMember.MemberValue >=
[Time].[Date].CurrentMember.MemberValue
)
)

The problem is that if I display all the dates, each measure is at 0 but the total is ok (>0).

Sorry for my english.

Thanks for your help.

Date matching

I have done this twice now. I am sure I will do it again.

I have a date dimension. Among other things the dimension contains columns for a meaningless id and raw date. If I want to translate a date to a the meaningless id for storage in a fact table, I use a look up component. All fine and dandy until I run the data flow and all of the look up's fail.

Now what could be wrong with that? I look at the redirected rows and for a split second think, what is wrong with that data? Then it hits me. There is time information in the source and not in the date dimension.

Wouldn't it be nice if I could match on just the date! Off I go to put in another data conversion component for the lookup.

I know some of you have written custom components. How hard would that be for me to do?So you need to translate date in the source to raw date which you could then lookup in the lookup table? Can you do it with expression through Derived Column transform? If no, then writing custom component would be right. In C# it's not that difficult, 1 - 2 pages of code in one class. You can find some examples in Books Online.|||Yes, I would have used a derived column as well.

regards,
ash|||

Ash Sharma wrote:

Yes, I would have used a derived column as well.

The only successful way I have found is the following equation

(dt_wstr,2)DAY(dtPeriodStartDate) + "/" + (dt_wstr,2)MONTH(dtPeriodStartDate) + "/" + (dt_wstr,4)YEAR(dtPeriodStartDate)

And setting the result column type to date timestamp.

Lets just say this gets old fast. I am open to better ways.|||Ummm...I'd say this IS the best way. What is the problem that you have with it? Its only 1 extra component after all, and its a whizzy derived column transform as well - it shouldn't cause any perf problems!!

-Jamie|||

Jamie Thomson wrote:

What is the problem that you have with it?

My dislike is the copy paste nature of the work and potential for slight errors.

I actually have a fair number of such translations to do.|||So you'd like to be able to share (i.e. reuse - there's that magic word again )expressions across different places?

I think that's a great idea. Possibly a DCR for betaplace? Ashh/Kirk?

-Jamie|||In CTP 15, the FriendlyExpression property on columns in derived column is settable via dataflow property expressions. So conceivably, you could set this via configurations, or from one string variable that will evaluate to desired expression. Of course, you would need to now configure all the derived column expressions that would recieve this common meta-expression, but at least you would only have to edit the common expression in one place...

Date Formats In Analysis Services 2005

Hello,

I've been having an issue trying to display anything other then a Long Date in Analysis Services. I've used the Server based Time Dimension and I've also tried creating a dimension for a User Created Date Table.

It seems no matter what I do the date attribute always displays as a long date.

Any Ideas?

If you need more flexibility (and most applications), what I typically recommend is to create a standalone time dimension table. For example, if you look at Project REAL, we use surrogate keys for all of our dimensions except time -- and for it we use YYYYMMDD as an integer. And use it as the surrogate key between the fact tables and a standalone time dimension.

See http://www.microsoft.com/sql/bi/ProjectREAL

_-_-_

Date format

Hi,

I have a date format problem with the AS 2005.

I have a Data Mart dimension with a field of the type smalldatetime. I have created a AS2005 dimension with the hierachy "Fulldate" which is based on this field. Due to the language settings of the os and of the cube I would expect that the hierachy would display the date like dd.mm.yyyy

Unluckily it appears as mm/dd/yyyy

Under the DataItems there is a property format which I tried to use to get the correct displayed datetime ...but whatever I use i always display US datetime format.

What can I do to get the dateformat like dd.mm.yyyy?

Thanks,

StSt

You can create a named calculation in the table in the DSV that formats the date however you desire using the SQL syntax of the provider. Then use this column as the name column for the date attribute. If you are using a server based time dimension because you don't have a time table, you won't be able to do this. In this case you may want to consider generating a time table so that you can have better control over it's contents. This can be done in the Dimension Wizard by choosing the option to build a dimension without a source and not using a template. Then when you are done you will need to run "Generate Relational Schema..." from the database and the system will actually create and populate a time table in your data source.|||

Unluckily this doesn't help me. The data source is a SQL Server 2005 Data Mart which has all language settings for german date format which means

dd.mm.yyyy

Even in the DSV it display the date correctly but as soon as I use it in the dimension it will only be display like

mm/dd/yyyy

Independent from the DimensionAttribute Type or the DataItem Format.

What else can I do?

Regards,

StSt

Matt Carroll wrote:

You can create a named calculation in the table in the DSV that formats the date however you desire using the SQL syntax of the provider. Then use this column as the name column for the date attribute. If you are using a server based time dimension because you don't have a time table, you won't be able to do this. In this case you may want to consider generating a time table so that you can have better control over it's contents. This can be done in the Dimension Wizard by choosing the option to build a dimension without a source and not using a template. Then when you are done you will need to run "Generate Relational Schema..." from the database and the system will actually create and populate a time table in your data source.

Date Filtering vs Row placement misbehaviour

I am using the classic Add Business Intelligence date dimension hierachy and getting correct results when I had my calendar hierachy on the rows expanded to the day level and my dimension calculations on the columns. The results were wrong when I moved the calendar hierachy to the filter area and selected one date.

In particular, the [Date].[Date Calculations].[Current Member Name] = [Date].[Reporting Calendar].CurrentMember.Name shows that when you filter the Hierachy down to the day, the Current Member is always incorrectly showing the week (in every cell). It is correct when filtered to all other higher levels than date (week, month...).

I have tested this on multiple date dimensions for some days now and it is very consistent. Also If you bring the Date Attribute Hieracy onto the rows, it jumps to the correct Current Member.

Anyone seen anything like this - filtering by a Hierachy member does not work at the lowest level (the day level) but it does work as a row heading? It may be related to the lowest level being the Dimension Key?

Weird.

See my post at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=517552&SiteID=1 with TOPCOUNT and many hierarchies. I am also getting inconsistent results.

Are we missing somehting when we set a 'conformed' hierarchy? I did not have the lowest level as the dimesion key. Then I set it to the DImension key for my 11 hierarchies in the Product dimension. It worked for a small sample data set and then it started failing when I introduced it to the main project with 10+ dimensions and many hierarchies.

Let me have your views on this.

|||

I looked at your issue. It is not obvious to me that the problem I had with .CurrentMember being wrong under a lowest level filter member is the same as your TOPCOUNT problem. I had to move my formulae out to a different dimension and put them on an attribute hierachy to work.

However, by lots of trial and error with the display of simple diagnostic formulae, it is likely that you can identify a key simple value that is not correct. We need more such simple problem examples because I am convinced there is a basic structural problem in the implimentation of MDX against UDM and it will show up as the wrong answer in many different circumstances.

Good luck with finding a structure where the formulae works - at least UDM had many choices.

|||

If you have defined date hierarchies such as Fiscal or calendar, have you ensured that the key element of the attributes used in the hierarchy is unique? You might have to use the key collection to define a combined key of Year, Quarter for the quarter in the attribute hierarchy.

I think that is why you maybe seeing the wrong results. I am looking through all my attribute hierarchies to ensure that I have these set-up correctly.

|||

I am using unique running integers as the key of the attributes in the hierachy.

Also I have repeated the problem in any other dimension hierachy where the lowest level used as a filter member causes the .Currentmember to get the incorrect value (it gets the parent member of the filter value)

So it seems to be an issue on even the simplest dimension hierachy when the formulae is added to a hierachy within the filter dimension. I fixed it by moving all the formulae out to a completely different dimension.

Date Filtering vs Row placement misbehaviour

I am using the classic Add Business Intelligence date dimension hierachy and getting correct results when I had my calendar hierachy on the rows expanded to the day level and my dimension calculations on the columns. The results were wrong when I moved the calendar hierachy to the filter area and selected one date.

In particular, the [Date].[Date Calculations].[Current Member Name] = [Date].[Reporting Calendar].CurrentMember.Name shows that when you filter the Hierachy down to the day, the Current Member is always incorrectly showing the week (in every cell). It is correct when filtered to all other higher levels than date (week, month...).

I have tested this on multiple date dimensions for some days now and it is very consistent. Also If you bring the Date Attribute Hieracy onto the rows, it jumps to the correct Current Member.

Anyone seen anything like this - filtering by a Hierachy member does not work at the lowest level (the day level) but it does work as a row heading? It may be related to the lowest level being the Dimension Key?

Weird.

See my post at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=517552&SiteID=1 with TOPCOUNT and many hierarchies. I am also getting inconsistent results.

Are we missing somehting when we set a 'conformed' hierarchy? I did not have the lowest level as the dimesion key. Then I set it to the DImension key for my 11 hierarchies in the Product dimension. It worked for a small sample data set and then it started failing when I introduced it to the main project with 10+ dimensions and many hierarchies.

Let me have your views on this.

|||

I looked at your issue. It is not obvious to me that the problem I had with .CurrentMember being wrong under a lowest level filter member is the same as your TOPCOUNT problem. I had to move my formulae out to a different dimension and put them on an attribute hierachy to work.

However, by lots of trial and error with the display of simple diagnostic formulae, it is likely that you can identify a key simple value that is not correct. We need more such simple problem examples because I am convinced there is a basic structural problem in the implimentation of MDX against UDM and it will show up as the wrong answer in many different circumstances.

Good luck with finding a structure where the formulae works - at least UDM had many choices.

|||

If you have defined date hierarchies such as Fiscal or calendar, have you ensured that the key element of the attributes used in the hierarchy is unique? You might have to use the key collection to define a combined key of Year, Quarter for the quarter in the attribute hierarchy.

I think that is why you maybe seeing the wrong results. I am looking through all my attribute hierarchies to ensure that I have these set-up correctly.

|||

I am using unique running integers as the key of the attributes in the hierachy.

Also I have repeated the problem in any other dimension hierachy where the lowest level used as a filter member causes the .Currentmember to get the incorrect value (it gets the parent member of the filter value)

So it seems to be an issue on even the simplest dimension hierachy when the formulae is added to a hierachy within the filter dimension. I fixed it by moving all the formulae out to a completely different dimension.

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 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 dimension Sort order

We created Time Dimension from Dimension Wizard , the months default sort by alphabetically, I try to set ‘order by attributeName’ in BI Studio, but ‘OrderByAttribute’ from Properties is an empty dropdown box and can not enter the word.

What I'm doing wrong?

Thanks.
In Time Dimension created from Dimension Wizard, there is a month_name attribute which is sorted by alphabetically 'April, August...', and another attribute month_of_year_name. I want to display month_name, but order by 'month_of_year_name', how can I do this? or has another way to sort month name?

Any idea?

Thanks in advance.
|||

To solve this problem you will have to add a second column for the month number in the data source view.

Use the TSQL function DatePart() for this.

Use this month number as the key column and the month name as the name column in the properties pane for the attribute.

For the month number you will have to add year in the key as a collection because a month number(1-12) is not unique over years.

This is also found in the properties for the month attribute.

SSAS2005 sorts by key as a default so this should work.

HTH

Thomas Ivarsson

|||Great! It works.

Thank you very much Thomas for the help.

Date Dimension out of a simple table

Hi all,

I have a very simple table were I want to have a Time dimension.

Table --> Date | Country | Region | City | Office..........

Time Dimension --> Year, Semester, Quartal, Month

Region Dimension --> County, Region, City, Office

This is one fact table and I have no Problems in creating the Region Dimension but problems with the Time Dimension. The Date Column contains Date and time.

If I use Time dimension I need to add columns into my table with Year, Semester, Quartal, Month. I don't want to do that.

If I use Serverbased time dimension I have to enter the start Year and the end Year, but the table is growing and I don't want to devide my Time column into one Date column and into one Time column.

In SQL 2000 it was possible to use one Column to create this simple dimension. What I'm doing wrong?

How can I solve this?

Thanks in advance

Hello. First, here is a link on how to create dates for a time dimension.

http://blogs.conchango.com/jamiethomson/archive/2007/01/11/T_2D00_SQL_3A00_-Generate-a-list-of-dates.aspx

Next. In SSAS2005 you will have to create the levels in the time hiearchy on you own. It is not that hard. In the dimension table or in the data source view(as a named calculation) you can use the TSQL-functions like YEAR(), QUARTER(), MONTH() and DATEPART() . They are all listed in Books On line under time functions for the database engine.

HTH

Thomas Ivarsson

Date Dimension in SSAS2005

Dear all,

In AS2000 when we use a date field of the dimesion table as a date dimension and defines the level (ex:Year,Month and dat) AS2000 formats the date field as given format.

In SSAS2005 i used the same methord but SSAS2005 does goes in that way.It simply gives me all the date as in database (10/10/2005)

How can i do it in level and hirarchy (year,month and day)

Hope u understand the Question

from

sufian

Dear all,

AS i studied in and tested i come to know that we can use two type of time dimesnion in SSAS2005 beased on the user requirement.

(1) Time Dimension (2) Server Time Dimension

Time Dimesion : Is similar to other dimension and supplies the attributes of the dimension.Each column with define an attribute for a specific time period.

Server Time Period: This is the one which defines the attribute on Year,Month Day and much more.

Thanx

Regards

Mohd Sufian

date business intelligence

Hi,

I have a date dimension with the following attributes:

datekey

fulldate

weeknumber

month

quarter

semester

year

I have also created Named Calculation for Quarter, Semester and Month to make them more descriptive and associated them with the name column of each attribute.

e.g. Month: January 2005

Quarter: Q1 2005

However, I notice that the dates that were displayed are not correct. Sometimes I would have January 2017 even the records doesn't show any year 2017.

Why is this so? and how can i resolve this?

cherriesh

Take a look at the relationships between your attributes in the Date dimension. This may be the source of your problem.

To illustrate, consider the two month-related attributes in your dimension. You have both Month of Year, e.g. January, and Month, e.g. January 2007, attribute hierarchies. The Month Of Year attribute hierarchy is related to date but itself has no other relations. The Month attribute hierarchy is also related to date and has a relation to Quarter, e.g. Q1 2007, which has a relation to Year.

B.

|||

hi,

sorry but how do i do the checking? i went to Dimension Structure of my Date Dimension and in Quarter attribute (this has the named calculation 'QuarterDesc' on the Name Column) I dropped the Year attribute on the <New Attribute Relationship> and reprocess the cube. It's still the same.

anything i missed?

thanks a lot.

cherriesh

|||

No problem. For a first step, let's verify the TYPE property of each of your attribute hierarchies. Right-click each of the hierarchies, select Properties, and record the TYPE property value. Here are the attribute hierarchies you identified:

datekey

fulldate

weeknumber

month

quarter

semester

year

Once we have that info, we'll look at the relationships.

Thanks,
Bryan

|||

thanks a lot!

cherriesh

Date Attribute in Dimension

My dimension table has a date field that foreign keys into my time dimension table. The relational tables look something like this:

ProductRelease table

ReleaseId int (pk)

ReleaseDate datetime (fk)

TimeDay table

DayDate datetime (pk)

YearId int

In the dimension designer, my Product Release dimension has an attribute relationship between ReleaseDate and YearId. The problem is when I process the dimension I get an error like this:

Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_ProductRelease, Column: ReleaseDate, Value: 1/15/2007. Errors in the OLAP storage engine: The record was skipped because the attribute key was not found. Attribute: Product Release of Dimension: Product Release from Database: MyWarehouse, Record: 2.

But the TimeDay table does have a record whose primary key is 1/15/2007 (actually, 2007-01-15 00:00:00.000), so I'm not sure why I'm getting the error.

What's interesting is that if I use another Time dimension table whose primary key is an integer instead of a date field, and change my ProductRelease table to fk into that using an int field, everything works fine.

I may end up using the integer pk Time table anyway (although it is nice to see the date field in the foreign key table -- more convenient when browsing the data), but why would I get this error using the date data type? Does the OLAP engine have trouble with using date foreign keys?

Thanks for any help with this.

-Larry

It can be as simple as that the other table have a different time(but the same date) as the TimeDay table.

This Date(2007-01-15 00:00:00.000 ) is not the same as 2007-01-15 23:02:02.002.

HTH

Thomas Ivarsson

|||

I'm fairly certain all the values are pure dates w/o a time, but I'll double-check to make sure.

Thanks.