none
Measure as a string RRS feed

  • Question

  • Hi all, could some one please offer a suggestion of how to have a measure as a string. 

    I've tried searching all over but can't find a definitive solution, I'm wanting to avoid having to create a new dimension manually in the actual database.

     I was under the impression that in SSAS you could create a Hidden FactDimension directly from the fact and insert an Id in place of the actual data item then just use a calculated measure to translate the ID key in the fact to the actual value in the Hidden fact dimenion using something like

    CREATE MEMBER CURRENTCUBE.Measures.Description AS 
    ' IIF(DescriptionKey = 0, "",
    Filter(TextDescriptions.MEMBERS, Val(TextDescriptions.CurrentMember.Properties("Key"))=Measures.DescriptionKey).Item(0).Item(0).Name)
    

     

    This is a sample of the data, I have tried simply changing the datatype on the measure to WChar and setting Agregation to 'None' but nothing is working.

    FactAssetKey dimAssetKey AssetInstalled AssetDisposed
    1 8 5  
    2 8 4 4.5km
    3 5 1.5km 0.5pk
    4

    3

    35Ph  

    Any help much appreciated!

    Tuesday, January 11, 2011 5:52 PM

Answers

  • Thanks Darren, sorry if I'm being a bit stupid, maybe I'm going around the houses but essentially all I want to do is based on the sample Fact table above is to return a measure in the cube to display AssetsInstalled which is a string, but because it is a string the cube won't process.


    Cubes are an aggregation technology and strings don't aggregate. If you want to display a string value you really need to have that in a dimension not as a measure. I'm guessing that what you are trying to do is to show a list of Assets Installed - you need a dimension to do this.

    If my guess is not correct then maybe it would be better to explain your business need because it does not look to me that a calculated measure is the correct approach here.


    http://geekswithblogs.net/darrengosbell - please mark correct answers
    • Marked as answer by rockyboy1 Tuesday, January 18, 2011 9:51 AM
    Sunday, January 16, 2011 11:36 PM
    Moderator

All replies

  • If you have the fact dimension why not just unhide it and use that?
    http://geekswithblogs.net/darrengosbell - please mark correct answers
    Wednesday, January 12, 2011 5:13 AM
    Moderator
  • Hi, thanks for the response, I've now simply added a FactDimension called [Fact Capex Assets Dim] and I've tried adding a calculated measure to the fact like below

    IIF(FactCapexAssetsKey = 0, "",
    Filter([Fact Capex Assets Dim].MEMBERS, 
    Val([Fact Capex Assets Dim].CurrentMember.Properties("Key"))=Measures.FactCapexAssetsKey).Item(0).Item(0).Name)

    But when I process the cube I'm getting #Value! errors, I'm just trying to use to retrieve the Name column (AssetsInstalled) from the [Fact Capex Assets Dim] based on the FactAssetKey in the Fact.

    Hope that makes sense!

    Thanks for your help

    Wednesday, January 12, 2011 10:00 AM
  • But where is the translation from an Id to a value going to come from if it's not in a table? If the value is already in the fact table you could use that in your fact dimension. Unless your dimension will have a similar cardinality to your fact table you would be better to just create a separate dimension table. I had one client where we almost halved the time of their cube processing time but pulling out a fact dimension that resolved to only a small number of members and replacing it with a dimension table.
    http://geekswithblogs.net/darrengosbell - please mark correct answers
    Wednesday, January 12, 2011 8:12 PM
    Moderator
  • Hi, you're correct the Id being translated will simply be the FactKey which is also the Key used in the FactDimension, so I simply need the calculated measure to then translate the Key in the FactTable to the corresponding value from the Dimension table, I really want to avoid having to create an extra dimension for various reasons.

    IIF(FactCapexAssetsKey = 0, "",
    Filter([Fact Capex Assets Dim].MEMBERS, 
    Val([Fact Capex Assets Dim].CurrentMember.Properties("Key"))=Measures.FactCapexAssetsKey).Item(0).Item(0).Name)
    

    This is a basic example, please let me know if I'm missing something, I can't seem to work out why this code isn't working,

    FactTable FactDimension
    AssetFactKey DimSchemeKey AssetsInstalled AssetFactKey AssetsInstalled
    1 5 40ke 1 40ke
    3 6 40fh 3 40fh
    Thanks!
    Wednesday, January 12, 2011 10:40 PM
  • I don't think that your expression will work unless [Fact Capex Assets Dim] is on one of the query axis. If it's not the call to .CurrentMember will always return the "All" member. As I said before, I think you are better off just using the column returned by the [Fact Capex Assets Dim]. If you REALLY need to get a measure with the name of the current member you could just use the following, but it seems a little redundant to do that.

    [Fact Capex Assets Dim].CurrentMember.Name


    http://geekswithblogs.net/darrengosbell - please mark correct answers
    Friday, January 14, 2011 2:54 AM
    Moderator
  • Thanks Darren, sorry if I'm being a bit stupid, maybe I'm going around the houses but essentially all I want to do is based on the sample Fact table above is to return a measure in the cube to display AssetsInstalled which is a string, but because it is a string the cube won't process.

    I can't work out how to return AssetsInstalled from [Fact Capex Assets Dim] as you suggest.

    Thanks

    Friday, January 14, 2011 10:20 AM
  • Thanks Darren, sorry if I'm being a bit stupid, maybe I'm going around the houses but essentially all I want to do is based on the sample Fact table above is to return a measure in the cube to display AssetsInstalled which is a string, but because it is a string the cube won't process.


    Cubes are an aggregation technology and strings don't aggregate. If you want to display a string value you really need to have that in a dimension not as a measure. I'm guessing that what you are trying to do is to show a list of Assets Installed - you need a dimension to do this.

    If my guess is not correct then maybe it would be better to explain your business need because it does not look to me that a calculated measure is the correct approach here.


    http://geekswithblogs.net/darrengosbell - please mark correct answers
    • Marked as answer by rockyboy1 Tuesday, January 18, 2011 9:51 AM
    Sunday, January 16, 2011 11:36 PM
    Moderator
  • Hi Darren, thanks for the reply.

    I totally understand cubes should just be used for aggregation purposes, however this is just one of those exceptions where we just need to display the data rather than do any form of aggregation or analysis.

    95% of the values in AssetsInstalled column are figures but a few of them include some form of unit measure (specific to the company) which I need to display in the measure group rather than a seperate dimension, as the unit measures are few it doesn't make sense to have seperate dimenion for them hence the need just to return the value as is.

     

    Hope that helps!

    Many thanks

    Tuesday, January 18, 2011 9:50 AM