Modelling optional one to one for SSAS

Posted on 2011-04-20
Last Modified: 2016-02-14
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?
Question by:badabing1
    LVL 15

    Accepted Solution

    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.

    LVL 22

    Expert Comment

    Why you have one to one relation in a OLAP Model!?

    Author Comment

    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.
    LVL 15

    Expert Comment

    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.


    Author Comment

    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).
    LVL 15

    Expert Comment

    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.


    Author Comment

    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.


    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
    Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now