Module Group (SSAS database)

Module Group can be of the following main types:

  • Data Warehouse only modules, that create data sets in the data warehouse database for use in e.g. InVision, Sim or WebPlan.
  • Modules that targets SSAS databases (multidimensional or tabular).
In relation to SSAS database, an SSAS based module is like the SSAS database with schema information of the tables/views from the data warehouse, callback connection for the SSAS to the read data from the data warehouse database and shared dimensions.

Main section

Name This is the name of the Module. The name can have translations.
Description User defined description.
Comments User defined comment (supports RTF-format). See more here.
Start/End Date This is used as the span for the module. The result table will contain on row per day within this period.
Note - These are used in filters in Module Definitions/Measure Groups on Transdate column.
Language(s) Lists the languages selected in solution settings. Check the languages to use in e.g. target SSAS database. Not supported in Tabular SSAS.

Status section

This contains status of last operation. Click here for more details and usage.

For SSAS type modules there are two statuses, one for the data warehouse and one for the target SSAS database. Also, there are a detailed status that needs to be load/refreshed.

Click on [Load detailed SSAS status] to get a detailed status. The table shown tells if each part is processed in the data warehouse and the SSAS database.

Dimension section

This section lists all dimensions used in a module. Dimensions are added through module definitions, but they are shared among all and located in the module group (and target database as shared dimension).

Most settings are related to how the dimension is handled in the data warehouse (what type, view or table, scans).

Click on [Delete] to remove dimensions that no longer are in use. Click [Refresh] to update dimension list with status etc.

The dimension table has the following fields:

Type Type of dimension; Time Dimension, Wide Dimension, Dimension Tree (Parent/Child), Parent/Child Sub Dimension, Wide version of Dimension Tree, Wide version of Sub Dimension Tree
Source Name of the source dimension.
Name Name of the dimension used in the module (override name). For Tabular SSAS edit the names in Module Definition (since also dimensions are implemented as measure groups/tables).
Use wide Parent/Child Dimensions and Sub Dimensions can have wide versions. Set checked to use this feature. All module used by InVision must have this checked.
Reduce scan Set checked to reduce/remove dimension members that are not used in the fact tables used in a module definition (measure group). This will make it easier for the end user to e.g. filter dimensions since only used items are shown.
Member scan Set checked to scan the source tables for unknown members (members that do not exist in the dimension).
As view Normally dimensions are copied to a table in the module layer. Check this if the dimension should be used through a view directly on the dimension layer. This cannot be combined with Reduce scan.
Default Member The default member is used in the SSAS Multidimensional database to set the default member item.
Result, Status Status of the resulting dimension table in the module layer. Right click to browse result.
Source, Status Status of the source dimension table in the dimension layer. Right click to browse source.
Settings Indicates with a symbol if the dimension has advanced settings attached to it.

Right click to get a menu with following items:

Note!
  • Do not use member or reduce scan on dimensions like Time Function, Report or other 'static' dimensions. 
  • Member scan will add *Empty and *Null items that may affect result.

Connection section (SSAS)

From this section you can:

  • 'Disable changes to the SSAS database'. Check to protect the database form changes and processing.
  • 'MS SSAS Database specification' lets you select the SSAS Server and Database name.
    • Note - in PS6 the database name is not necessarily the name of the module.
    • From PS6.4 there are new field for username/password to connect to SSAS (windows user).
    • From PS6.4 the SSAS Database ID can be overridden with a new Id. Use the checkbox and enter a unique valid id. This is useful if you restore the same solution and wants to use the same SSAS server.
  • 'Callback connection' that the SSAS service uses to connect to and read data from the data warehouse database. If no user name or password is set, the SSAS service's user will be used. Here you also enter the timeout that the connection uses.
    • Note - username/password is for MS SQL users. Use Advanced SSAS Settings to impersonate windows user account.   

Advanced SSAS settings section

This section has advanced settings for the SSAS database and the SSAS databases Data Source.

Use this with care and read Microsoft's documentation for Database and Data Source.

Notes on Connections vs Advanced SSAS Data Source settings:

  1. Username/password in the DWH callback connection: The credential specified here is the database windows user account that SQL server will accept, allowing to read the rows on its tables.
  2. Impersonation Info under advanced SSAS Settings : This the Windows credential/user account under which Analysis Services will be running when it tries to connect to server to talk to the database.

DW Connection Settings

From PS63, a module can be stored in a separate data warehouse database. Create the database externally and select the connection per module. 

Note - module definitions/measure groups are normally views and dimensions can be views. Views addresses source with [database][schema].[table], so either:
 - define results as tables.
 - ensure access rights across source and target database.

Script Extensions section

Scrips can be created as SQL or PowerShell against the data warehouse database, or as XMLA scripts against the SSAS database. In this section scripts can be connected to processing of the module.

For more information on adding scripts click here, and for more on linking script extensions click here.

SQL and PowerShell scripts can be run on results in DW before and after following commands:

  • creation of the module (full module DW processing)
  • creation of fact source views (with DW BI)
  • creation of specific fact source views per module definition
  • creation of all shared dimensions
  • creation of specific dimension
  • creation of fact snapshots or aggregations
  • creation of specific fact snapshot or aggregation per module definition
XMLA scripts can be run against the SSAS modules before and after following commands:
  • deletion of SSAS database
  • create/update SSAS database
  • process SSAS database
  • after setting standard roles and permissions 
  • Multidimensional: 
    • create/update dimension
    • process dimension
    • create/update cube
    • process cube
    • create/update measure group
    • process measure group
    • process measure group partitions
    • process 'current' measure group partition
  • Tabular:
    • create/update Table
    • process Table
Note - PowerShell scrips can be used against SSAS. You can use queries or use AMO objects. You hav to use Dataflows to e.g. run a script before/after SSAS operations listed above.