Search code examples
ssasmdx

New Column with numbers

Goal:
Create a new column, named test , containing numbers in SSAS only inside of DSV design view.

If StatePrinceCode's column has value NSW it shall have value 1 etc.

StateProvinceCode    test
------------------------------
NSW                  1
QLD                  2
SA                   3

Problem:
How should I enable to do it? I tried using IIF and case statement but it doesn't want to do it due to text value. It only works with numbers only.

Information:
- I strongly believe that you need to create a named calculation as a extra column to add the value

  • The new column shall apply in the table Geography in SSAS.
  • In this context no usage of SQL server database

enter image description here

enter image description here

Solution

  • The syntax you use in SSAS DSV computed columns is that of the underlying relational database, as actually, this expression is included in the select statement when reading teh data from the relational database. Hence, if you use Oracle as the relational source, it would be Oracle SQL. If you use SQL Server for the relational source data, it would be Transact SQL syntax.

    In Transact SQL, you could use a CASE expression (http://msdn.microsoft.com/en-us/library/ms181765.aspx) as the expression for the test calculated column evaluating the StateProvinceCode in the CASEs.