Search code examples
variablesconstantsssas

Is there such a thing as a 'constant' in Analysis Services?

Is there such a thing as a constant in SSAS?

Example (this is really happening where I'm at) everyone agrees that gigs to mb be converted by 1000 (not 1024) and tb to mb by 1000000.

Where would you store a number like that used across the board?

Solution

  • If it's inside the cube, could you create a calculated member that stores it? Define it in the cube's calculation script, it's fine with constants in there.

    In cube calculation script:
    CREATE MEMBER CURRENTCUBE.Measures.MBtoGigs AS 1000

Query against the cube:
    SELECT Measures.MBtoGigs ON COLUMNS FROM [Cube]

    One possible pitfall I would point out, is that using constants like this can alter the way you'd expect the NON EMPTY behaviour to work in your queries - As a constant is never 'empty'.

    Having said that, you can define you own non-empty-behaviour for calculated measures, so remember to try that with any calculated measures that involve constants if you experience any issues.