Cube Tables OuterJoin: How can i make my "UnKnown" Bar in proclarity-chart a meaningful BAR. RRS feed

  • Question

  • Dear Experts, I have two tables which have to be joined as outer join i.e. table-A (say Dept) only creates row in table-B (say Employee) when required and all un-joined rows in A are NOT junk. I have changed UnKnownMember to Visible for "both" tables/dimensions through Dimension (Tab) -> Dimension Structure -> Attrubutes (Pane)

    But when i browse the cue through Proclarity Bar Chart i get a bar labeled UnKnown, its count is meaningful/desired as Table-A but drilling down keeps its count statatic and every level as UnKnown. I need help that bar chart (Attribute Hierarchy) should show All table-A (Depts i.e.) rows and shuold NOT get same count etc when drilling down that BAR, also every level should NOT keep showing UnKnown.

    I have seen some posts describing soething to do with NullProcessing but which NullProcessing should i change its available in atleast following three different places of BI Studio.

    1. Cube (Tab) -> Dimensions (Tab) -> Measure Group Binding (Regular) -> Null Processing (Grid)

    2. Cube (Tab) -> Cube Structure (Tab) -> Measure (aggregatin) -> NullProcessing

    3. Dimension (Tab) -> Dimension Structure -> Attrubutes (Pane) -> <any attribute> -> Key Columns -> (elipses button) -> NullProcessing

    I have made "count" measures for both tables and i tried with all cominations of pivoting hierarchies and switching count measures.

    How can i make my UnKnown Bar in proclarity-chart a meaningful BAR.

    Thank you.

    Tuesday, January 27, 2009 9:54 AM

All replies

  • I don't see where you have defined the UnknownMemberName property.  If this step hasn't been taken, the way you currently describe the configuration, the unknown member will appear, but will not have a custom name.  You will likely also need to change the NullProcessing property to UnknownMember in the dimension structure tab.  Also, if you haven't already done so, the error configuration properties will need to be be changed.

    Here's a good article on the subject, complete with steps and examples:  http://msdn.microsoft.com/en-us/library/ms170707.aspx

    Microsoft ProClarity | This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, January 29, 2009 5:20 PM
  • Very thanful Ben, for reply. Actually i dont need to custom label the unknow bar. I need to resolve this bar. The bar should not be there. If we take the Dept, Emp example... i am not getting orfan Depts and i am also not getting orphan Emps. These orphans are being pulled under the carpet of UnKnow. If i have to put my whole post in single linner, it will be... I am unable to emulate Full-Outer-Join (not even Left or Right) in BI-Studio-2005.

    I did see that MSDN post earlier, its using BI Studio 2008 and i am on 2005. Means i am not sure "Attribute Relationships" is way to go. if yes i have to do it in BI Studio 2005 way. Even then i am confused that if simple plain Full-Outer-Join-SQL reguire just 2-3 lines of code instead of this much fuss, then why i have to do this "Attribute Relationships" in GUI design.

    Thank you.

    Friday, January 30, 2009 6:07 AM
  • In addition to prev post i see its matter of many-to-many relationship. For solution many people ask to make another bridge-table hosting the join keys from both tables. I see its ridiculus of why VS2005 is so immature to ask my custom fittings/tailoring. I just need implement plain old Full-Outer-Join with orphans of both side visible when i browse cube. http://msdn.microsoft.com/en-us/library/ms345139.aspx (Many-to-Many Dimensions in Analysis Services 2005).

    Useful articles but still, why should i create bridge table, or use API or custom view or MDX !?!

    Ben can you guide to some GUI-Design only based solution !?!
    Thank you.
    Saturday, January 31, 2009 12:44 PM
  • Ben, to explicitly answer that quesstion... yes i have UnKnown property both Dimention's node to "visible". I did change NullProcessing property but it doesnt make any difference to my situation.

    Sunday, February 1, 2009 5:52 PM
  • I don't think I'm completely sure as to problem, but here are some thoughts.

    If this is a question about the design of Business Intelligence Development Studio, and perhaps your feelings that it could be done differently or better, I'd suggest the Analysis Services forum on MSDN.  That group will also better be able to tell you if VS/BIDS 2008 will help with your question (but I don't believe it will).

    Regarding your data model, it would seem the appearance of the unknown member is appropriate for the situation you describe, but let me be sure I've got it right.  You have two tables (Dept and Emp, for example).  For the sake of the example, you can have an employee record that does not have a deptid and you can have a dept record that does not have an empid, correct?  So, when you do an outer join of these two tables, the results will contain records that have null values for empid and deptid, correct?

    When building the dimension for the cube, you have based the dimension off of the outer join results from the two tables.  So, when the dimension is processed, there will be null values for the "Department" attribute and for the "Employee" attribute, correct?  That is, since the outer join of the two tables has orphans, the resulting dimension data source will contain nulls for empid and deptid, and so the attributes that are added to the dimension will by necessity be empty strings for those records that have null values for either empid or deptid, correct?  If the dimension processing can't find a value for an attribute (the value is null), it can't give it a meaningful name by default.  You can change this by changing the unknown member settings and such, but by default these will just be unknown.

    In my opinion, the best way to handle this is by building a clean star schema on which the cube will be built.  This schema will have a dimension table that will contain the joined information from the employee and dept tables.  In building this table the designer will update it with the desired information for existing null values, so that when the dimension is processed in the cube, there are no unknown values because those have been taken care of in the design and implementation of the dimension table.

    So, in your example, you'd do the outer join of the two tables and load the results into a new table (or view or whatever).  You'd then go back with an update statement or something of the like and update all the null values in the table to a meaningful name based on your knowledge of the business.  So, for a null deptid you might check the employee id and then update the dept to "Contract".  For a null empid you might update it to "N/A".  Then, when this table is used for the dimension, the processing doesn't have to deal with any null values because those have already intelligently been dealt with prior to the dimension being built.  The dimension just takes the value for the attribute, and adds it to the dimension.

    I hope this helps, or at least gets a little further to discovering what your problem is.
    Microsoft ProClarity | This posting is provided "AS IS" with no warranties, and confers no rights.
    Sunday, March 8, 2009 4:04 PM