I have been running into some troubles creating a fact drillthrough dimension. The scenario is as follows:
You have a fact table (in this case with around 50 million rows) holding accounting data. Within that facttable you have a column that holds some information in a free text field (let's say varchar(150) and name "Account_information") that is useful for the business user when they want to do a drillthrough on a cell in the cube.
Now how would you design your cube to be able to drillthrough to that information ?
The solution, the way i see it, is to create fact dimension containing those 50 million rows....
The fact table(Fact_Finance) looks like this
Unique_ID(int), Account_information (varchar(150)), Amount, Column_x....Column_y
In the DSV i then add a view that is a "Select Unique_ID, Account_information from Fact_Finance)
With that view i then create a dimension with
Attribute keycolumn = Unique_ID and
Attribute namecolumn = Account_information
Setting the dimension to processmode ROLAP often results in a timeout in the 3. part front-end tool, so in my case the only option is to use molap for the dimension.
To be able to add the Attribute in the Action i need to add the dimension in the dimension structure, binding the Unique_ID from the dimension to the Fact_Finance table. To me that seems to dramaticly slow down browsing the cube - comments ?
The Molap process of the drillthrough dimension becommes pretty heavy with a select distinct on the varchar column, even with index created though the Data Tuning Wisard.
I hope that someone can comment on their experience with creating a fact drillthrough dimension, since this approach has a big impact on both the processing and browsing of the cube
"To be able to add the Attribute in the Action i need to add the dimension in the dimension structure, binding the Unique_ID from the dimension to the Fact_Finance table. " - are you saying that the fact dimension is configured with a regular relation (not a fact relation) to the measure group? This shouldn't be necessary - for example, the "Reseller Details" action in Adventure Works returns attributes from the fact dimension.
Another option worth trying (if you have Enterprise Edition) is to create a (text) measure with AggregateFunction of "None" on the Account_Information field (rather than configuring it as the name of the fact key attribute). This measure can be included in the drillthorugh action return list.
|||
If I try that i'll get the following error:
Error 1 Errors in the metadata manager. The data type of the 'Account_Description' measure is not valid because the data type of the measure is a string type.
AggregationType set to None
Datatype is WChar
I'm trying to run in the Developer edition.
|||
Looks like you're right - when I set up a test scenario, I got the same error as well. This approach was suggested in an earlier thread here:
the attributes of Fact Table could not be displayed when drill through?
>>
...
Sure you have got a huge dimension.
But you shouldn't create dimension, you can make text measure.
...
You can also create a non aggregated measure and then use that when drilling through.
>>
So scratch that - here's another thread on a similar subject, with an approach from (ahem) a more authoritative source:
String measures in Analysis services cube
>>
...
The usual workaround in both AS2000 and AS2005 for such requirement is to create hidden dimension TextDescriptions with integer key and string name, i.e. you will have dimension table as
Key Name
1 Optimal
2 Needs Improvement
3 Failed
...
You will also have hidden measure DescriptionKey in the cube.
Then you use integers as a measure, and create calculation for it, like following:
CREATE MEMBER CURRENTCUBE.Measures.Description AS
' IIF(DescriptionKey = 0, "",
Filter(TextDescriptions.MEMBERS, Val(TextDescriptions.CurrentMember.Properties("Key"))=Measures.DescriptionKey).Item(0).Item(0).Name)
Mosha - http://www.mosha.com/msolap
>>
Another approach which I've used recently is to add a text attribute to the fact table, with its attribute hierarchy disabled. But in this case, since the client tool is Excel 2003, users don't access this text via drillthrough - instead, the fact dimension is directly added (after appropriate slices have been set, of course) on the rows of a pivot table. The text is then dropped in the data area, using a calculated measure which references the Member Property for the text. Excel 2003 allows a Member Property to be added to a pivot table as well, but I found this to be slower and less obvious than a calculated text measure.
The Analysis Services 2005 Performance Guide discusses when to disable an attribute hierarchy:
...
Reducing attribute overhead
...
Turn off the attribute hierarchy and use member properties
As an alternative to attribute hierarchies, member properties provide a different mechanism to expose dimension information. For a given attribute, member properties are automatically created for every attribute relationship. For the primary key attribute, this means that every attribute that is directly related to the primary key is available as a member property of the primary key attribute.
If you only want to access an attribute as member property, once you verify that the correct relationship is in place, you can disable the attribute’s hierarchy by setting the AttributeHierarchyEnabled property to False. From a processing perspective, disabling the attribute hierarchy can improve performance and decrease cube size because the attribute will no longer be indexed or aggregated. This can be especially useful for high cardinality attributes that have a one-to-one relationship with the primary key. High cardinality attributes such as phone numbers and addresses typically do not require slice-and-dice analysis. By disabling the hierarchies for these attributes and accessing them via member properties, you can save processing time and reduce cube size.
Deciding whether to disable the attribute’s hierarchy requires that you consider both the querying and processing impacts of using member properties. Member properties cannot be placed on a query axis in the same manner as attribute hierarchies and user hierarchies. To query a member property, you must query the properties of the attribute that contains the member property. For example, if you require the work phone number for a customer, you must query the properties of customer. As a convenience, most front-end tools easily display member properties in their user interfaces.
...
