none
Using string values as measures RRS feed

  • Question

  • <!-- [if !mso]> <mce:style> <!-- [if gte mso 10]> <mce:style>

    First I will give you a little background of the data we have in our cube.

    We have a COMPANY (we call it an OPERATOR) which has several FEATURES attached to it. Each FEATURE has FEATURE ATTRIBUTES, i.e. the FEATURE “Network Management/Maintenance” has the FEATURE ATTRIBUTES “Contract Date”, “Contract Value”, and “Currency”, while the FEATURE “Licence” has the FEATURE ATTRIBUTES   “Licence Value”, “Award Date”, “Licence Award Type”.

    We ultimately want the data displayed as follows:


    Operator,Feature,Start Date,End Date,Supplier,Contract Date,Contract value,Currency,License Value,Award Date,Licence Award Type
    Operator A,Network management/ maintenance,null,null,Supplier E,01/12/2005,1500000,GBP,null,null,null
    Operator A,Service management,04/02/2006,null,Supplier V,07/11/2003,1000000,GBP,null,null,null
    Operator A,Licence,27/04/2000,26/04/2022,null,null,null,GBP,20000,27/04/2000,Auction
    Operator B,Service management,05/07/2001,null,Supplier M,null,null,null,null,null,null
    Operator B,Service management,05/02/2006,null,Supplier H,02/09/2006,3100000,AUD,null,null,null
    Operator B,Mediation,24/08/2001,null,Supplier G,09/08/2004,1900000,GBP,null,null,null
    Operator B,Billing,19/02/2005,null,Supplier B,01/11/2005,2500000,EUR,null,null,null
    Operator B,Licence,01/07/1992,null,null,null,null,GBP,null,01/07/1992,Granted
    Operator B,Licence,27/04/2002,26/04/2022,null,null,null,GBP,500000,27/04/2000,Auction

     

    Our main problem is that some FEATURES share FEATURE ATTRIBUTES, however many FEATURE ATTRIBUTES only apply to one FEATURE., thus resulting in a table with many columns, and with many columns not containing any values. To give you an indication of how many columns we have, we have got 37 FEATURES and approx. 100 FEATURE ATTRIBUTES. This is not very manageable as a denormalised table with each FEATURE ATTRIBUTE as a measure – it will increase greatly with the addition of new FEATURES, and FEATURE ATTRIBUTES will change frequently, thus resulting in continual cube changes to add/remove the FEATURE ATTRIBUTES measures.

    I had a google around and came across the blog http://www.sqlserveranalysisservices.com/default.htm . This basically uses cell annotations as a way of using strings as measures. So my intention was to treat all the FEATURE ATTRIBUTE values as distinct string entries in a new dimension called FEATURE VALUES (i.e. there would be a FEATURE_VALUE_ID as the primary key and unique entries for “01/12/2005”, “07/11/2003”, “02/09/2006”, “ 1500000”, “1000000”, “GBP”, “AUD”, “EUR”, “Auction”, “Granted” etc) .

      Then our fact table would essentially be a factless fact as follows:

    FACT_COMPANY_FEATURES

    (OPERATOR_ID

    FEATURE_ID

    FEATURE_ATTRIBUTE_ID

    FEATURE_VALUE_ID)


    This seemed good in theory, however I could not get the cell annotations working successfully.

    So, the question is this – by looking at the data structure/data, is there a more obvious way of solving the underlying problem? If not, and the best solution is to store all of the values in a dimension and treat them all as string measures, is there a better way of doing this than using cell annotations?

    Thanks in advance for your help,

    Vaughan.

    Tuesday, June 15, 2010 4:48 PM

Answers


  • This seemed good in theory, however I could not get the cell annotations working successfully.


    I don’t know other good workaround for this. Why it doesn’t work in your cube, some error message or nothing return? Is it possible caused by the granularity of the query? Since you set the aggregate function to be None, then you can only see the unaggregated value, like this thread:

    http://social.msdn.microsoft.com/forums/en-us/sqlanalysisservices/thread/2691C396-B592-40E6-8CF1-BA8E63C0BB3B

     

    You have to ensure the query is located at leaf-level, simply change the aggregate function None to Sum to give another try. The concept is very simply, since you cannot create a string measure, then create numeric measure to indentify the string, like this:

    fact table:

    date    string  StringID

    2007    abc     1

    2007    def      2

    2008    abc      1

    2008    ghl       3


    After that, create a dimension use StringID as keycolumn String as Namecolumn, use StrToMember to find the member in the dimension. However, if you query 2007, then nothing will return, because of the granularity.

     

    Hope this helps,

    Raymond


    Raymond Li - MSFT
    Thursday, June 17, 2010 5:54 AM
    Moderator