What does the DIMENSION TYPE do in SSAS?

1
votes

I am more experienced with SSAS Tabular, but in my role at work I must support SSAS Multidimensional cubes. Earlier today I tried to add a time intelligence calculation (Parallel Period) to SSASMD connection in Excel pivot table. But the evaluation error-ed out regardless of the syntax I used.

After struggling with the error I came across this article at SQL Server Central (Stairway to MDX - Level 13: MDX Time/Date Series Functions: LastPeriods() and ParallelPeriod() Functions).

My question: Does the DATE dimension have to have Type 'TIME' in order for date functions to work? Here is the syntax for my YOY calculation.

Lbs Sold YOY Actuals-v1

(
[DATES].[INVOICE_DATE].[INVOICE_DATE].CurrentMember.PrevMember
,[Measures].[Lbs Sold]
)

Lbs Sold YOY Actuals-v2

(ParallelPeriod(
    [INVOICE_DATE].[DATES].CurrentMember.Level,
    1,
    [INVOICE_DATE].[DATES].CurrentMember
    ),
    [Measures].[Lbs Sold])

I am more experienced with DAX than MDX, so any insights would be appreciated. My MDX calculation sources...

Dimension Types

Also I am curious, what are all of the other Dimension Types used for? Does changing that from 'Normal' to any of the others give the cube extra functionality? I found some descriptions online (pasted below), but I am curious what this actually does?

From the Microsoft Docs...

The Type property setting provides information about the contents of a dimension to server and client applications. In some cases, the Type setting only provides guidance for client applications and is optional. In other cases, such as Accounts or Time dimensions, the Type property settings for the dimension and its attributes determine specific server-based behaviors and may be required to implement certain behaviors in the cube. For example, the Type property of a dimension can be set to Accounts to indicate to client applications that the standard dimension contains account attributes.

+-----------------+---------------------------------------------------------------------------+
| Type            | Description                                                               |
+-----------------+---------------------------------------------------------------------------+
| Regular         | Default for dimensions that are not set to a specified type               |
+-----------------+---------------------------------------------------------------------------+
| Time            | Used for dimensions whose attributes represent time periods               |
+-----------------+---------------------------------------------------------------------------+
| Geography       | Used for dimensions whose attributes represent geographical inform-ation  |
+-----------------+---------------------------------------------------------------------------+
| Organization    | Used for dimensions whose attributes represent organizational information |
+-----------------+---------------------------------------------------------------------------+
| BillOfMaterials | Used for dimensions whose attributes represent inventory and              |
|                 |   man-ufacturing information                                              |
+-----------------+---------------------------------------------------------------------------+
| Accounts        | Used for dimensions whose attributes represent information used for       |
|                 |   financial reporting                                                     |
+-----------------+---------------------------------------------------------------------------+
| Customers       | Used for dimensions whose attributes represent information about          |
|                 |   customers                                                               |
+-----------------+---------------------------------------------------------------------------+
| Products        | Used for dimensions whose attributes represent information about products |
+-----------------+---------------------------------------------------------------------------+
| Scenario        | Used for dimensions whose attributes represent information about plans    |
|                 |   and strategies                                                          |
+-----------------+---------------------------------------------------------------------------+
| Quantitative    | Used for dimensions whose attributes represent quantitative inform-ation  |
+-----------------+---------------------------------------------------------------------------+
| Utility         | Used for dimensions whose attributes represent utility information        |
+-----------------+---------------------------------------------------------------------------+
| Currency        | Used for dimensions whose attributes represent currency inform-ation      |
+-----------------+---------------------------------------------------------------------------+
| Rates           | Used for dimensions whose attributes represent currency rate inform-ation |
+-----------------+---------------------------------------------------------------------------+
| Channel         | Used for dimensions whose attributes represent channel information        |
+-----------------+---------------------------------------------------------------------------+
| Promotion       | Used for dimensions whose attributes represent marketing pro-motion       |
|                 |   information                                                             |
+-----------------+---------------------------------------------------------------------------+
1
ssasmdx

1 Answers

1
votes

You can give a try and experiment it by yourself. Let's pick the ParallelPeriod function:

select 
    ParallelPeriod(
        [Customer].[Customer Geography].[Country],  
        3,
        [Customer].[Customer Geography].[State-Province].[Hamburg]
    ) on 0  
from [Adventure Works]

It returns South Australia. It makes no sense, but it works. So the answer to your question "Does the DATE dimension have to have Type 'TIME' in order for date functions to work?" is no.

In my experience, the only reason to change dimension type is when you are using semi-additive aggregation functions (i.e. LastNonEmpty) it requires a Time dimension within the cube in order to determinate the set of values per all intervals of the period.

Other than that, it's simply the client-side info. Clients can befit using different icons for different types and so on.