badabing1
asked on
Modelling optional one to one for SSAS
I currently have a situation where there is an option one to one relationship between Table A and Table B, such that for each record in Table A there MAYBE one record in Table B. What I am finding is that when it comes to SSAS I am getting 'The attribute key cannot be found' error as there are of course entries in Table A which do not appear in Table B. What is the correct way to model this? Is there a way to force these missing attributes into an unknown member?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
PedroCGD
Why you have one to one relation in a OLAP Model!?
ASKER
Thanks for your replies, TimHumpries that may be the solution I go with but first I'll answer PedroCGD in case I am approaching this the wrong way.
This may be difficult to explain but I have to model the following: Company Assets, CLIs and Calls.
Company Assets is a table of all assets some of which are telephone lines with CLIs.
CLIs is a list of all numbers called from and to with a description of the destination.
Calls is a fact table of the cost and duration of the calls along with keys to dimensions.
Each call has an originating CLI and a destination CLI.
Either of these CLIs could be a company owned Asset. Therefore I decided that the relationship between CLIs and Assets is an optional one-to-one. Please correct me if I am wrong in this assumpsion.
This may be difficult to explain but I have to model the following: Company Assets, CLIs and Calls.
Company Assets is a table of all assets some of which are telephone lines with CLIs.
CLIs is a list of all numbers called from and to with a description of the destination.
Calls is a fact table of the cost and duration of the calls along with keys to dimensions.
Each call has an originating CLI and a destination CLI.
Either of these CLIs could be a company owned Asset. Therefore I decided that the relationship between CLIs and Assets is an optional one-to-one. Please correct me if I am wrong in this assumpsion.
I think this is an optional one-to-many, not one-to-one: each CLI may be involved in many facts as either destination or origin, but each fact can only have one origin or destination either of which could be a company CLI.
So if your CLI dimension table has a unknown member you can map any non-company CLI to your unknown member.
Tim
So if your CLI dimension table has a unknown member you can map any non-company CLI to your unknown member.
Tim
ASKER
You are quite right Tim but you are missing out the Asset table.
Each Asset MAYBE a CLI
Each CLI will have many Call Facts
An asset will only ever have one CLI. A CLI may not necessarily be a company CLI (it could just be an external number).
I am considering combining the Asset table with the CLI table but this would mean that I have a table of all company lines and equipment plus external lines (which are not company assets).
Each Asset MAYBE a CLI
Each CLI will have many Call Facts
An asset will only ever have one CLI. A CLI may not necessarily be a company CLI (it could just be an external number).
I am considering combining the Asset table with the CLI table but this would mean that I have a table of all company lines and equipment plus external lines (which are not company assets).
You could maybe model the CLI as an attribute of the asset table, with an additional attribute of 'IsCompanyCLI' as a yes/no / boolean to distinguish between them.
You could then build a hierarchy of Asset -> CLI. So a CLI would always have a parent Asset, and an Asset may have a child CLI. Again you could use the IsCompanyCLI to distinguish between internal and external.
However, the problem here could be external CLIs that are not Assets as you would have to create a dummy External Asset. Perhaps you could have ONE external Asset that was parent to many external CLIs. Seems a bit of a cludge, though.
Tim
You could then build a hierarchy of Asset -> CLI. So a CLI would always have a parent Asset, and an Asset may have a child CLI. Again you could use the IsCompanyCLI to distinguish between internal and external.
However, the problem here could be external CLIs that are not Assets as you would have to create a dummy External Asset. Perhaps you could have ONE external Asset that was parent to many external CLIs. Seems a bit of a cludge, though.
Tim
ASKER
Tim I have been pondering over that very idea for a while now but I just wasnt happy with having all my CLIs (internal and external) in my Asset table, as it would no longer be a company owned asset table.
I have decided to link my two fact tables (fact call and fact service charge) to the asset table by asset id.
I have then linked the destination number of the fact call table to the CLI table.
I have then joined the CLI and Asset tables twice (using a foreign key in both tables)
Where the CLI and Asset foreigns keys are null I have put a -1 as you suggested, this allows me to see asset specific information for the destination CLI and CLI specific information for the originating asset. I have built the cube and it all seems to work perfectly even though it may be a little cumbersome.
Nevertheless as I have used your earlier suggestion I will award you the points. Thanks for your help in this matter.
Graham.
I have decided to link my two fact tables (fact call and fact service charge) to the asset table by asset id.
I have then linked the destination number of the fact call table to the CLI table.
I have then joined the CLI and Asset tables twice (using a foreign key in both tables)
Where the CLI and Asset foreigns keys are null I have put a -1 as you suggested, this allows me to see asset specific information for the destination CLI and CLI specific information for the originating asset. I have built the cube and it all seems to work perfectly even though it may be a little cumbersome.
Nevertheless as I have used your earlier suggestion I will award you the points. Thanks for your help in this matter.
Graham.