August 19, 2008 at 12:26 pm
It's difficult to find a lot of posts on this subject...
I'm trying to build a cube.
I have a datasource view with an order date and ship date
The order date is always populated, but the ship date may be null
I have a text-book server time dimension
I can link the time dimension to the order date in the dimension usage tab, and compile just fine.
When I try to add another time dimension and link it to the order date, I always get the error of --- member not found ... "12:00am"
I tried to create a calculated field to convert the date to a null if no value exists...but I keep getting the same error.
My latest attempt in the data source view was:
CASE WHEN SHIP_DT < CAST('01-01-1900' AS DATETIME) THEN NULL ELSE CAST(FLOOR(CAST(SHIP_DT AS float)) AS datetime)
END AS IVER
Still no luck
I'll want to link the Time dimension to many date fields...but I can't get past 2!!! 🙁
Thank you for your help
Jason
August 21, 2008 at 2:50 am
Did my issue stump the ssas world? 🙂
August 22, 2008 at 7:11 am
I ran into a similar issue with a date field in one of my fact tables. I never found a concrete solution, but I have a work around. I converted my fact table in the data source view into a named query and stripped off the time from the datetime in question.
DATEADD(dd, DATEDIFF(dd, 0, some_date), 0)
I'm certain there are better ways to go about it, but this worked for me.
With the time stripped off, the cube handled NULLS and I was able to process.
August 22, 2008 at 8:56 am
Have you tried enabling the UnknownMember option in the dimension?
Enabling this property can be a great little life saver. When your fact table contains information that is not present in the dimension table it directs it to a an Unknown bucket.
To do this go to thte dimension Attributes pane and select the properties of the dimension. Change UnknownMember to either Visible or hidden and give it a name in UnknownMemberName.
Next, and this is not well documented anywhere, in the Dimension Usage Tab, select the dimension in question and select Advanced in the Define Relationship box. From there change the Null Processing to UnknownMember.
Hope this helps.
August 22, 2008 at 9:30 am
wildh,
My SSAS db has a server time dimension. When I add the dimension to my cube and relate the date (key) attribute to a datetime measure group column of my fact table that has date and time I get an error when I process the cube. I've set the unknown member property to visible in the time dimension and set null processing to unknown member in the dimension usage as you suggest.
This is the error: " ...The attribute key cannot be found when processing: Table: 'some_table', Column: 'some_date',Value: '5/2/2008 2:48:40 PM'. The attribute is 'Date'. Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found."
This error is the reason I converted the fact table to a named query in the DSV and stripped off the time from the date field.
I suspect that the attribute key members of the server time dimension do not have hh:mm:ss precision. Looking at the Type property of the server time key attribute "Date" I can see that it is set to days. If I change the Type from days to date, SSAS warns me that "...DimensionAttribute [Time].[Date] : The 'Type' property should be 'Days' for the key attribute of a time dimension." With that in mind, do you know if it is possible to get hour precision from a server time dimension, or will I have to create a time dimension from scratch? I read some article that suggested that a time dimesion should really be a snowflake made up of year, semester, quarter, month, day... tables.
Also, do you know if when the cube is processed in SSAS is SSAS is doing some kind of implicit conversion of the measure group column like from datetime to int or decimal?
sorry for the long winded post
August 22, 2008 at 1:42 pm
wildh (8/22/2008)
Have you tried enabling the UnknownMember option in the dimension?Enabling this property can be a great little life saver. When your fact table contains information that is not present in the dimension table it directs it to a an Unknown bucket.
To do this go to thte dimension Attributes pane and select the properties of the dimension. Change UnknownMember to either Visible or hidden and give it a name in UnknownMemberName.
Next, and this is not well documented anywhere, in the Dimension Usage Tab, select the dimension in question and select Advanced in the Define Relationship box. From there change the Null Processing to UnknownMember.
Hope this helps.
Thank You!!!
August 26, 2008 at 1:20 am
Jay_Noob,
SSAS shouldn’t be doing any implicit conversion if both the data types on your fact table and dimension table are the same, however, saying that I don’t have that much experience with linking on named query’s. I have had an instance of something similar though and this could be related. I created a udf (that stripped out the time part of a date) in the database view that was my fact table and used that as the PK to link to the fact table, anyway, that caused loads of problems because SSAS had an issue liking it with the dimension table. It worked but in the query plan it was multiplying each fact record by the number of dates in the time dimension.
This could be something similar, I would suggest doing a small test and create a table that actually holds the data your named query is generating and try that as a fact table. If that works you know SSAS has an issue with linking named queries.
If it doesn’t work let us know and we can investigate more.
August 28, 2008 at 12:05 pm
I assume you are talking about the Cube in the design view when you are referencing the "Dimension Usage" tab.
Where exactly is the "Advanced" box of the "Define Relationships"...
August 28, 2008 at 12:15 pm
In the dimension usage tab, if you select a cell where a measure group and dimension intersect a "..." button will appear in the cell. Click the "..." button and the "Advanced" button will be in the new dialog box that opens.
August 28, 2008 at 12:56 pm
Got it...thanks.
I have two dimensions referencing each other with missing data on both ends...Trying to get them to overlook that and still process the cube.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply