Module Definition (SSAS Measure Group)

Module Definitions is where one or more fact source tables are combined into one result with additional business logic columns (calculated dimensions and measure columns) and joined with dimensions in the data warehouse. The fact result can be set as a view or a table, have aggregations to reduce it and/or used to create a *-model.

For SSAS Multidimensional modules, the Module Definition is used to create a SSAS Measure Group. A Measure Group combines fact result tables/views (or measure table) with dimensions and additional business logic.

For SSAS Tabular modules, the Module Definition is used to create a SSAS Tabular Table (=Measure Group). The module definition is used either for dimensions or for fact. You can only add a new fact-based measure groups, but when a dimension is used, a dimension measure group will be added automatically. The measure group equals a tabular table and it will have its own column editor. From SSAS tabular 2019, also Calculation Group tables are supported - see advanced tutorail for tabular model for details. 

Main section

Name	This is the name of the module definition. The name can have translations.
Description	User defined description.
Comments	User defined comment (supports RTF-format). See more here.
Enable	Checkbox to enable/disable this module sub group w/module definitions. Available from PS6.4 release 7.

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.

Structure section

This contains the graphical overview of the module definitions structure with its columns, BI and use of dimensions.

This is not used for SSAS Tabular tables.

Fact Source Tables section

From here, the data source fact tables are selected. Each source table will be given a default filter. 

Settings/info per source is:

Enabled	Check to use the source table in the result
Data Source	Name of the Data Source.
Name	Name of the Data Source Table. Click here to select a new Data Source Table.
Use table	Per default, the data source table's key view is used. Set Checked to use the source table directly. The table will not include columns like data source id, data source table id and natural keys for surrogate keys.
With No Lock	When reading from the Data Source Table the select expression will include WITH (NOLOCK). Read Microsoft's documentation before using this feature. It is usually ok to use this with transactional data (non committed data will be included, but usually transactions is not updated but inserted).
Use Direct in Target	This is an SSAS option to bypass regular module definition fact processing. The Module Definitions source view with column padding and BI will be created and used directly in the SSAS Measure Group using its own partition (DirectQuery). This is e.g. useful in the following situations: if you have a separate source table with latest transactions (incremental transactions) the needs to be processed e.g. per hour. The SSAS Measure Group partition with this source, can be processed fast on a relatively small data set. if you have historic data that do not change, you can process once and 'forget'. Note - The source with this option will not be include in the module definitions result. It will not use the selected partitioning settings. Note - Fact dimensions will not work with this.
Target Settings	This is used in combination with Use Direct. Click the cell to bring up an dialog to edit target settings for sources used in SSAS directly. In this version this contains settings for Slice of the direct query partition.
Filter	This is the filter used against the source. Note - The default filter is used against the TransDate column and the start / end data of the module group. If not TransDate column exist, change to relevant column or remove the filter. This can also be used against other columns/filters.
Description	This shows the description of the data source table.
DW Source	This shows the table/view name. Right click to browse the source.
Status	This shows the source's status.

Available commands:

• [New] Adds a new row for adding a Data Source Table.
• [Delete] Removes selected source.
• [Reset] resets source editing changes since last save.

Fact Definitions section

This section lists the items (columns) from source tables and those added to DW by business logic. 

Enabled	Check to use the column.
Item Name	Name of the item (not the id that is used as column name).
Standard	If a standard item (PS5.2 template related, now only for information).
Mandatory	If item is mandatory
Type	Type of item, e.g. measure, dimension id, business logic item etc.
Indicator	Indicates from where this item arises from and is present in, e.g.: green check: present in all source tables green diagonal: present in one or more, but not all sources red X: not present in any sources or BI. Item will have no value and is not included. blue check: item from BI. brown check: dimension attribute linked through dimension usage.

Available commands:

• [Reset] resets editing changes since last save.
• [Synchronize] checks source tables and business logic for new items.

Column Definitions (SSAS Tabular tables only)

From the Column Definitions tab, the columns and measures used in the Tabular SSAS module are defined.

There are 3 types of columns:

• Regular Columns - These are columns that are in the data warehouse table/view (either from selected fact source tables or from the dimension table). Regular columns can be of any type, e.g. a dimension id, name or a measure.
• Calculated column - These are columns that are based on a DAX formula that e.g. combines two regular columns or a relates to column expression.
• Measure Column - These are measures based on a DAX formula.

Settings/info per source is:

Enabled	Check to use the column.
Hidden	Check to hide the column for clients of the SSAS Tabular model.
Name Definition ID	This is the id of the Name Definition used.
Display Name	This is the name of the column used in the Tabular SSAS model.
Item Description	Here the column type etc. is displayed.
Data Type	The selected data type for the column.
Formula	The formula used for Calculated ad Measure columns.

Available commands:

• [New] brings up the column detail dialog to add a new column.
• [Edit] or double click in grid, brings up the column detail dialog.
• [Reset] resets editing changes since last save.
• [Synchronize] checks source tables for new columns.

Dimension Usage section

From the Dimension Usage tab, the relationships between dimensions and Fact result are set.

Following type of links are supported:

Direct (regular)	All modules	Here the key column for the dimension is linked (joined) directly to the fact result.
Link via (referenced)	SSAS Multidim. only	Here the key column for the dimension is joined indirectly to the fact result through a key in another dimension. PS is limited to on dimension linked (joined) to a regular dimension linked to the fact result.
Via Measure Group (many-to-many)	SSAS Multidim. only	Here a single dimension can be associated with multiple fact results. An already linked dimension in another Measure Group can be added linked to a second Measure Group.
Time Dimension (regular)	All modules	This is the same as a direct/regular dimension link, but only for time dimension types.
Fact Dimension	SSAS Multidim. only	Here a fact column can be selected and used to create a dimension. Only one column per Measure Group is possible.

Note - for regular/direct and time dimension usage types, a dimension can be used as a Role-Playing dimension if it already is used and bound to a different column. A Role-Playing dimension must be set when editing in the usage dialog. The result is that the SSAS cube will have a new instance of the shared SSAS dimension bound to the column in the measure group. Use this if you e.g. want to bind different date columns to different time dimensions in order to get correct aggregations etc.

Settings/info per dimension usage is:

Enabled	Check to use the dimension.
Visible	Check to make the dimension visible in SSAS.
Type	Type with binding information.
Seq#	Sequence when applied to SSAS.
Description	User defined description.
Result	Module DW result table. Right click to browse.
Status	Result status symbol.
Source	Source dimension view. Right click to browse.
Status	Source status symbol.

From the dimension usage dialog, options for on how dimension hierarchy and members are named, can be set (PS62+). Hierarchy Name Style and Member Name Style can include (SSAS default) or exclude dimension names, e.g. [Time].[YMD] (exclude) vs [Time].[Time].[YMD] (include). 

SSAS Tabular (from 6.4): Cross filtering behavior can be set to Automatic (default, engine will analyze), Both directions (filters work in both directions, e.g. financial fact->accoun<->account to report map<->report) or One direction (only one direction).

Available commands:

• [New..]
  • [New Dimension Link] brings up a dialog to select dimension (or sub dimensions), set usage type and binding.
  • [New Time Dimension Link] brings up a dialog to select a time dimension and its binding.
  • [New Drill Through] brings a dialog to select column (item) for use in drill through.
• [Edit] brings up a dialog to change source, type and binding (or double-click row in grid).
• [Delete] removes selected dimension.
• [Reset] resets source editing changes since last save.

Measures section

This section lists the items (columns) from the source tables that is of type measure. 

Enabled	Check to use the column.
Visible	Check to make the dimension visible in SSAS.
Item Name	Name of the item (not the id that is used as column name).
Standard	If a standard item (PS5.2 template related, now only for information),
Mandatory	If item is mandatory
Format string	Select between predefined or add user defined formats on how to display the measure in SSAS clients.
Aggr.function	Select what aggregation function to use in SSAS. The default is Sum, other options is: Count Distinct Count Max Min Last Child First Child (SSAS enterprise only) First Non-Empty (enterprise) Last Non-Empty (enterprise) Average Of Children (enterprise) By Account (enterprise) None (enterprise)
[Details]	Brings up a dialog to select optional display folder and mdx expression for the measure. Display folders is added through adding name definitions within the display folder category.
[Edit Name]	Brings up a dialog to edit the measures name definition with translations.
Translations..	Name/description of the measure per language selected in module group.

Available commands:

• [New] adds a new row for manually added measures. These requires mdx expressions.
• [Reset] resets editing changes since last save.
• [Delete] removes selected measure.

Business Logic section

This section lets you add Business Logic. Click here for more on the different types.

The added business logic has following settings:

Enabled	Check to use the BL item.
Visible	Check to make the BL visible in SSAS.
Type	Type of BL.
Fact ID	Dimension ID/Measure ID the BI results in.
Description	User defined description.
Translations..	Name/description of the BL item per language selected in module group.

 Available commands:

• [New..] shows an dialog to add BL dependent on selected BL type.
• [Edit] brings up a dialog to edit the BL item.
• [Delete] removes selected dimension.
• [Copy] copies selected BL item to clipboard.
• [Paste] pastes BL item from clipboard.

Storage and Partition Settings section

This section has settings that decides how the module definitions data is stored and used in the data warehouse and the target SSAS database.

Data Warehouse Module Aggregation and Storage settings

Per default, the result from a module definition is a view with data from sources and added fact BL. This can optionally be set to be a direct or aggregated table.

Following options can be set and combined:

• Aggregate TransDate to - selects aggregation on TransDate and other dimension columns with following options
  • None - no time aggregation (default)
  • Year -  aggregation to year.
  • Quarter - aggregation to quarter.
  • Month - aggregation to month (default for Sim7x and WebPlan modules).
  • Day - aggregation to day level.
• Always aggregate Fact tables - check to aggregate by dimension columns.
• Create as table (take snapshot) - check to create result as table.
• Compress - this option is only available if the result is set as a partitioned table. The table will be created with MS SQL page compression.
• Create *-schema - this option is only available if result is set as a table. Foreign keys will be added between used dimensions and the fact table result within the module.
• Skip casting will be used when creating views with bi and column padding on each fact source table used. The cast will be skipped for dimension and measure columns. If the result is view the datatype may change etc.
• Skip *NULL/*Empty check will use the source data direct. By default a syntax like this is added: 'CASE WHEN AccountID <> '' THEN ISNULL(AccountID, '*NULL') ELSE '*Empty' END'. This ensures less problems with use in SSAS.

Data Warehouse Module Partition settings

If the result is set as a table (snapshot/aggregation), partitioning can be used.

Select an existing partition setting from the drop-down box. Click here for more on partition settings.  Information is displayed for the selected partition definition. 

Data Warehouse 'Current' settings

If the result is set as a table (snapshot/aggregation), data can be updated in a 'current' sliding window. Days before/after current date can be set and a Module DW command can be called to merge data from source in to resulting module definition table.

SSAS Measure Group Partition settings

If target is an SSAS measure group and the SSAS service supports partitioning, SSAS partitioning can be used. The SSAS service can then use parallel reading and aggregation of data.

Select an existing partition setting from the drop-down box. Click here for more on partition settings.  Information is displayed for the selected partition definition. 

SSAS Measure Group Current Partition settings

Check to use a current partition to limit time span for, and speed up, processing. Enter days before and after current date. Use this if you repeat processing 'last data' multiple times each day.

 

This is not used for SSAS Tabular dimension tables.

Advanced Measure Group Settings section

This section has advanced settings for the SSAS measure group and the Create Perspective property.

Use this with care and read Microsoft's documentation before setting properties.