MDA Database Build
(with the Element Selector)

The Menu item to build MDA Databases from 'External Sql Data Sources' (data that is not currently in an MDA Database) 'with Element Selector' is available in MDAccess from the Menu Bar:

After selecting the Data Source, the user needs to create a Data Table from that Data Source before an Element Table can be built. The Element Table uses that Data Table as its principal component. The data for the Data Table is selected from the Data Source thru a Sql statement.

The Sql Statement method

The Sql Statement method allows the user to enter a Select Sql statement or stored procedure call. The 'Run Sql Script' button can be used to determine if the syntax of the statement is correct. When a syntactically sound statement has been entered, the user then can select the field to be used for the index, the field to be used for the title of the Data Table row, and all fields that should be included in the Data Table.

Once the query is processed, the Data Table is build, and consequently the Element Table is also built.

The Element Table can be used to build an MDA Database from either the available or selected rows in the Element Table.

The Element Table Source Rows for the MDA Database Build:

The Data Source Rows are determined as follows:

If any Rows are Selected in the Right pane, Otherwise,

When the 'MDA' or 'MDA Out' button is clicked from the Element Table Presentation Menu, the 'MDA Database Output Specifications' form is then presented that allows the user to select:

• The Output Database File
• The Fields that define Dimensions
• The Fields that contain Numeric Values
• A Dimensional Attribute Option

The Fields that define Dimensions:

The Dimensional fields are used to create the Dimensions by giving each MDA database Dimension the title of the Dimensional field, and each Dimension a list of Elements that are the distinct values within those Dimensional fields.

The number of dimensions in the resultant MDA database is equal to the number of dimensional fields selected, plus one more dimension for the List of Numeric fields selected. The List of Numeric fields dimension is by default titled the 'Measures' dimension. This additional Measures Dimension will contain an Element for each selected Numeric field. The Element titles in the Measures Dimension will be the titles of the selected Numeric fields. Additional Calculation Measure Elements, unique to the Measure Dimension can be created later in the MDAccess program. (Calculations are available in MDAccess for all dimensions, but an additional set of Calculations are available for the Measure Dimension.)

The Fields that contain Numeric Values:

When the MDA Database is created, the fields that contain Numeric Values are collapsed (aggregated) to create one distinct row for each distinct Dimensional index combination of the selected Dimensional fields. The data value, in a particular Numeric field, can then be accessed by defining the dimensional indices and the Numeric field to access.

This is an optional enhancement that adds the ability to do the following:

• Create Hierarchical menus (by Attributes)
• Search, Sort, Subset, and Select Elements (rows) by Attributes
• Add Subtotals of Elements by Attributes or Attribute combinations (with Calculations)

To simplify database creation select 'Add No Dimensional Attribute Fields' on the 'MDA Database Output Specifications' form.

Dimensional Attributes are values associated with a Dimensional Element. For instance, if the dimension were a list of Cities, each city in the list would be an Element of that dimension. An Attribute Field might be the State field, or also a Size field, or some other Element Table 'Attribute' Field that might be considered an Attribute of the Cities Dimension.

After the above selections are made, and the user clicks the 'OK' button, the user has the option to possibly add Attributes to the Dimensional Fields selected. To be an Attribute of a dimensional element, requires that all occurrences (rows) with a particular dimension field value have only one distinct attribute value in the attribute field. This results in each element value in the Dimensional Field having a definite and distinct value in the Dimensional Attribute Field.

When an Attribute Field has only one distinct value for each distinct value in a Dimensional Field, that Attribute Field is considered to be a 'Qualifying Dimensional Attribute Field' for that particular Dimensional Field.

The Dimensional Attribute Field Options:

There are currently four options available for Dimensional Attribute Field processing.

They are as follows:

• Add No Dimensional Attribute Fields
• Add All Qualifying Dimensional Attribute Fields
• Add Selected Qualifying Dimensional Attribute Fields
• Add Selected Dimensional Attribute Fields (with or without Qualifying) (use first value encountered)

The first option adds no Attribute Fields to any of the Dimensions and in effect turns the option off.

The second option instructs the program to find all Qualifying Attribute Fields for each dimension, and add them to the dimensions where they qualify.

The third option asks the user for each dimension, whether or not they want to possibly add Dimensional Attributes to that dimension. If the user answers yes to a particular dimension, then the program determines if any Element Table Attribute Fields qualify as possible Dimensional Attribute Fields for that dimension. If there are any, then the user is presented with a menu of qualified Dimensional Attribute Fields that can be selected. The selected Attributes are then added to that particular Dimension.

The fourth option presents the user, for each dimension, with a menu of all Dimensional Attribute Fields that can be selected. The selected Attributes are then added to that Dimension. Since the Attribute values for a Dimensional Element may not be unique in this case, the first value encountered is used for the Dimensional Attribute Field value.

The terms Element Table Attribute Field, and Dimensional Attribute Field are different perspectives and usages of the data column in the orginal Data Table. Each Field in the Element Table is an Element Table Attribute Field. Of those fields, a subset may qualify as Dimensional Attribute Fields for a Field selected as the Dimensional Field.

Dimensional Attribute - Qualifying Dimensional Attribute Fields - Examples:

If the rows represented sales, with each row being the sale of a particular item, one field could be the city where the item was sold, and another field could be the state where the item was sold. All rows with the value of 'Pittsburgh' in the city field would have the value of 'Pennsylvania' in the state field. Therefore 'Pennsylvania' could be an attribute of 'Pittsburgh'.

Another example given the same Element Table with the sales items, could exist for a Brand item, and the Manufacturer of the Brand item. All occurrences of the sales of a particular Brand item would have the same value in their Manufacturing field. For instance, Brand item 'Mustang Model xxx' would all have the Manufacturer of 'Ford'. If not, the Manufacturer field would not qualify as a possible Attribute of the Brand item field, and therefore would not be available for selection in the list of qualified Attribute fields available for selection.

One Data Table Field could be the Code/Order for the dimension, and another Data Table field could be the Title. If the Title field were selected as the Dimension Field, the Code field would qualify as a Dimensional Attribute Field. The Code field could be selected as the Order field for the dimension in the Element Selector, and the Title field could be selected as the Display field once the resultant MDA Database is loaded into MDAccess.

Another example could be as follows: For a given Data (Element) Table, if one of the fields were the Brand item, and another field were the Sales Person field, then the Sales Person field could not be an attribute for the Brand item field if multiple sales people made sales for the same Brand item. Alternatively, the Brand item field could be an Attribute of the Sales Person field if each Sales Person only sold one particular item.