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?
Who is Participating?
Tim HumphriesDirectorCommented:
I would explicitly have an unknown member in my dimension table with an id of -1.
When pre-processing your data (in a staging environment or in a named query in your data source view), you can effectively perform a left join between the tables and if there is no corresponding row in Table B then map the Table A row to the unknown member by returning a value of -1.

This way, from an SSAS point of  view, there is ALWAYS a link between the two, although in some cases the link is to a member called 'Unknown'.

Note that this approach uses a manually created unknown member, rather than relying on the internal unknown member.

Why you have one to one relation in a OLAP Model!?
badabing1Author Commented:
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.
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Tim HumphriesDirectorCommented:
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.

badabing1Author Commented:
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).
Tim HumphriesDirectorCommented:
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.

badabing1Author Commented:
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.