I have a reference dimension that is linked to a regular dimension that is linked to the fact table. For some reason, when I process the database with some small sample data, it take about 5 minutes to process, where it should take 10 seconds. I am using a reference dimension because it is a child-parent hierarchy, and I don't think I can merge it into the regular dimension. My work-around is to just include the link to this reference dimension in the fact table, so I can create the child-parent dimension as regular. Now is processes in 10 seconds. It doesn't seem ideal since I have what seems to me an unessary dimension now.
If I turn "materialize" off, it processes in 10 seconds, but I don't know the implications of this.
Does anyone have any idea of what is going on or have any advice?
Thanks,
Ernie
Are you using a referenced dimension here because the parent column is in another table from the child column? If so, you could create a view on top of them that encapsulates the relationship. What are the other issues you see regarding not being able to merge these columns into a regular dimension?|||Thanks for the reply Scott.
The child and parent are in the same view. I've created another dimiension because the child attribute has to be a key. The child attribute, or key of this dimension, is not what joins directly to the fact table. There is an intermediate "field" that has to create this link, which is a foriegn key in the regular dimension, which has a key to link to the fact table. Hmmm, did I say that right? I may not be explaining things well, as I'm a bit new at this. Anyway, this is why I "think" I can't merge them.
Maybe this is a valid example:
Fact table has column SubAccount
SubAccount table has Account column (multiple sub-accounts per account)
Account table has AccountParent column which created the child parent relationship.
In this example, I would create a regular SubAccount dimension and a reference dimension called Account. My "workaround" is to put both the Account and SubAccount columns in the Fact table (or view).
Ernie
|||It sounds like you should merge the sub account and account into a single dimension (make it a parent-child). You'll use a snowflake schema which will describe the join between the account and sub account tables as a source for the dimension, yet only have a single dimension.
Without doing this, if you tried going down the road of having two account dimensions (subaccount and account), then you'd end up with cases where you would get confusing results (like subaccounts that are not really related to an account showing up together, etc.)
Hope that helps
|||Scott (or anyone else):
I am running into a problem that seems to be related to the one described by Ernie above. Here is the situation:
1) I have a referenced dimension (dimA) that joins to the fact table (factB) through an intermediate dimension table (dimC).
2) The referenced dimension (dimA) has a parent-child heirarchy. The primary key of this table (keySelf) and the parent key (keyParent) are in the same table (dimA) i.e. the parent-child hierarchy is stored in the same table and not in a separate table.
3) I am able to deploy the cube and it gives correct results.
4) I am currently in the design stage so I am dealing with fairly small data sets. Therefore, I donot know if it is performing poorly during deployment.
5) My problem occurs when I try to design aggregations. I get the error at the end of the wizard when I am deploying the aggregations. The error is on the Measure group that is based on factB. The error states:
"Errors in the high-level relational engine. The binding for the 'standardId' column is not a ColumnBinding type."
Note that 'standardId' is the primary key (keySelf) in the dimA table. i.e. it is the key that maps factB to dimA (through dimC).
6) The error goes away if I uncheck the "Materialize" option in the Define Relationship window for the relationship between factB and dimA.
7) I have other referenced dimensions in the cube that relate to factB through dimC that do not have a parent-child hierarchy and they do not give this problem.
So it seems that if a referenced dimension has a parent-child hierarchy and is materialized then the aggregations run into problem. I realize that I can eliminate the issue by making dimA a regular dimension of factB but I am curious to understand why this is happening.
Is this expected behaviour or am I doing something wrong?
Thanks beforhand.
Regards,
Amardeep
|||Amardeep, this sounds like it could be a bug that you're hitting. I'll forward this on to our test team to verify.
One thing you could do to work around the issue that you're seeing though (since not materializing the dimension will be slower at query time) would be to not use a referenced dimension and instead use a regular dimension. To do this you'll need to modify your data source view (DSV) to include additional relationships in order to allow the join to work properly.
Hope this helps.
No comments:
Post a Comment