MDA Database Build
(without Element Selector)

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

After selecting that menu item, the following message is displayed to summarize the building process:

MDA Database Build from External Data

Select the Data Source. Select the Rows to Create the Datatable. Select the: Columns to be Dimensions Columns to be Data Values (Columns to be Attributes if any) in the MDA Database.

The Data Source Type Menu:

Data Source Type

Access Database Oracle Datatable Sql Server Datatable Excel File

The Data Source Type is selected - that's the input database type to select the data from. Then the Actual Data Source (database) is selected.

The Data Source File Menu:

In this case a .mdb file is being selected.

A Data Table is built by selecting data from the Data Source thru a Sql Statement.

The Sql Statement Menu:

The Sql Statement menu allows the user to enter a Select Sql statement or stored procedure call. For instance the text above 'Select * from [table...]' could be replaced by 'Select * from Table1'. The 'Run Sql Script' button can then be clicked to determine if the syntax of the statement is correct. When a syntactically sound statement has been entered, the Data Table is built and used by the 'MDA Database Output Specifications' menu.

The 'MDA Database Output Specifications' form allows the user to select:

• The Output Database
• 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.)

In the example menu above:

The selections in the two listboxes are the fields or columns that were in the datatable returned by the select query.

The fields selected to be Dimensions are:

• Areas
• Brands
• Months

Then a fourth Dimension will be added called 'Measures'. The Elements in the fourth Dimension will be the fields that are selected in the rightmost listbox - in this case 'UnitSales'.

The Elements in the first three dimensions will be the unique values that exist in those columns.
For instance, the unique values in the returned database from the query could be:

Areas	Brands	Months
New York	Cherrios	January
Chicago	Raison Brand	February
Los Angeles	Shredded Wheat	March

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 Field, 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 Data 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 Data Table Attribute Field, and Dimensional Attribute Field are different perspectives and usages of the data column in the Data Table. From the Data Table 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 Data 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 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.