Import and Prepare Fact Data for a Classic Account Model

Selecting Dimension Types

When importing data to a new model, columns containing text are typically identified as Dimensions, and numeric data may be identified as Measures. If the assigned dimension type isn't appropriate, you can change it to another type; for example, to a Date or Organization dimension. For details, see Learn About Dimensions and Measures.

Location Dimensions

If your data contains columns of latitude and longitude coordinate data, or state and county data (for the USA only), you can create a Location dimension. Select the (Geo Enrichment) icon under Transformations, and choose either By Area Name or By Coordinates.

When creating locations using coordinate data, location dimensions can be based on a combination of latitude and longitude pairs. You also have the option to specify a column as an identifier for the location dimension, and another column as the location description.

For more information, see Creating a Model with Coordinate or Area Data for Geospatial Analysis.

Maximum Number of Dimension Members

When importing data into a new model, there is an upper limit applied to the number of unique members in a dimension, to maintain good performance. See System Requirements and Technical Prerequisites.

However, for non-planning-enabled models only, you can import dimensions with more than the maximum number of members, with these restrictions:

• In the data integration view, these dimensions cannot have any dimension attributes added to them, such as description or property.
• Once the dimensions are imported into the Modeler, they will have only one ID column, and will be read-only.
• The dimensions can't be used as exception aggregation dimensions or required dimensions.
• The dimensions can't be referenced in formulas.

Calculated Columns

While you're preparing data in the data integration view, you can create a calculated column based on input from another column and the application of a formula expression.

Select the icon from the menu toolbar. Use the Create Calculated Column interactive dialog to build up your calculated column. Enter a Name for the calculated column. In the Edit Formula space start building the formula for your column. You can select an entry from Formula Functions as a starting point or enter [ to view all the available columns. Press control + space to view all the available functions and columns. Select Preview to view the results of the formula.

Note

The data displayed in the preview will be a random 10 line sample.

To add the calculated column to the model, select OK.

Note

Once the calculated column is added to the data, you can always modify the formula that it's based on by selecting Edit Formula in Designer.

For a listing of supported functions, see Supported Functions for Calculated Columns.

Dimensions Attributes

Dimension attributes are information that is not suitable to be standalone dimensions.

For example, if you have a Customers column, and a Phone Numbers column, you could set Phone Numbers to be an attribute of the Customers dimension.

You can use dimension attributes to create charts, filters, calculations, input controls, linked analyses, and tables (with “import data” models only).

These are the available dimension attributes:

• Description: The column can be used for descriptive labels of the dimension members when the member IDs (the unique identifiers for the dimension members) are technical and not easily understandable.

  For example, if your imported data contains a pair of related columns Product_Description and Product_ID, containing data descriptions and data identifiers, you can set Product_Description to be the Description attribute for the Product_ID dimension. Note that the Product_ID column would then need to contain unique identifiers for the dimension members.

  ID	Description
  X123	Computer and equipment
  Y456	Books

• Property: The column represents information that is related to the dimension; for example, phone numbers.
• Parent-Child Hierarchy (Parent): The column is the parent of the parent/child hierarchy pair.

  For example, if your imported data contains the two columns Country and City, you can set the Country column to be the parent of the Country-City hierarchy.

  The hierarchy column is a free-format text attribute where you can enter the ID value of the parent member. By maintaining parent-child relationships in this way, you can build up a data hierarchy that is used when viewing the data to accumulate high-level values that can be analyzed at lower levels of detail.

  You can also create level-based hierarchies when your data is organized into levels, such as Product Category, Product Group, and Product. When the data is displayed in a story, hierarchies can be expanded or collapsed. In the toolbar, select (Level Based Hierarchy). For more information about hierarchies, see Learn About Hierarchies.

• Currency: If you set a column to be an Organization dimension, the Currency attribute is available. The Organization dimension offers an organizational analysis of the account data, based, for example, on geographic entities. You can add the Currency attribute to provide currency information for the geographic entities.

Data Quality Considerations

During data import, anomalies in your data can prevent the data from being imported properly, or prevent the model from being created. If any of the following issues are found in your data, those data cells are highlighted, and messages in the Details panel explain the issues. You'll need to resolve these issues before the data can be fully imported or the model can be created.

• In account dimensions only, dimension member IDs cannot contain these restricted characters , ; : ' [ ] =.
• Numeric data cells in measures cannot contain non-numeric characters, and scientific notation is not supported.
• When importing data to an existing model, all dimensions of the existing model need to be mapped.
• When importing data to an existing model, cells in a column that is mapped to an existing dimension must match the existing dimension members. Unmatched values will result in those rows being omitted from the model.
• For stories, if any member IDs are empty, you can type values in those cells, or select Delete empty rows in the Details panel to remove those rows.
• When creating a new model, if member IDs are empty, they are automatically filled with the “#” value if you select the Fill applicable empty ID cells with the "#" value option. Otherwise, those rows are omitted from the model.
• In dimensions and properties, a single member ID cannot correspond to different Descriptions in multiple rows (but a single Description can correspond to multiple member IDs).

  For example, if member IDs are employee numbers, and Descriptions are employee names, it's allowable to have more than one employee with the same name, but not allowable to have more than one employee with the same member ID.

• In a Date dimension column, cell values must match the format specified in the Details panel. The following date formats are supported:
  • dd-mm-yy
  • dd.mm.yy
  • dd/mm/yy
  • dd-mm-yyyy
  • dd.mm.yyyy
  • dd/mm/yyyy
  • dd-mmm-yyyy
  • dd-mmmm-yyyy
  • mm-dd-yy
  • mm.dd.yy
  • mm/dd/yy
  • mm-dd-yyyy
  • mm.dd.yyyy
  • mm/dd/yyyy
  • mm.yyyy
  • mmm yyyy
  • yy-mm-dd
  • yy.mm.dd
  • yy/mm/dd
  • yyyq
  • yyyy
  • yyyymm
  • yyyy-mm
  • yyyy.mm
  • yyyy/mm
  • yyyymmdd
  • yyyy-mm-dd
  • yyyy-mm/dd
  • yyyy.mm.dd
  • yyyy/mm-dd
  • yyyy/mm/dd
  • yyyy.mmm
  • yyyyqq

  Examples:

  • mmm: JAN/Jan/jan
  • mmmm: JANUARY/January/january
  • q: 1/2/3/4
  • qq: 01/02/03/04
• Latitude and longitude columns, from which location dimensions are created, must contain values within the valid latitude and longitude ranges.
• For planning-enabled models, in a hierarchy measure, non-leaf-node members are not allowed.
  Note

  For analytic (non-planning-enabled) models only, non-leaf node members are allowed, but be aware of the effects of this behavior. Example: in an organizational chart that includes employee salaries, the Manager has her individual salary, and her staff members have their own salaries as well. In a visualization, do you expect the Manager’s data point to reflect her individual salary, or the sum of her staff members' salaries?
• Nodes in a hierarchy cannot be circular. For example, this hierarchy arrangement is not allowed: USA > California > San Francisco > California.

Mapping Considerations

• At least one measure must be defined for the model.

  If the source data doesn't contain a column that can be used directly as a measure, you can create a measure column based on a count of an existing column that contains 100% unique values. The generated measure column will contain the value 1 for all rows, representing the count of the unique values.

  If you proceed to create the model without having created a measure column, a measure column will be generated automatically, based on the first column that contains 100% unique values.

  To create the measure column yourself, select a column with 100% unique values, and then select Create Count in the Details panel.

  If there isn't an existing column that contains 100% unique values, a measure column will be generated anyway when the model is created, containing the value 0 for all rows.

• There can be only one of each of these dimension types:
  • Account
  • Organization
  • Version

Viewing the data imported to create a new model

During the initial data import, the data type of each column is inferred, as well as dimension type in the data integration view. In the right hand Details side panel, you will have a summary of the characteristics of the model.

1. Under Layout, select the Card View icon to display all the columns as separate cards. Each card includes the column name, a specific summary of the data corresponding to its type (mean value for numbers, date ranges for dates, number of unique values for text, etc.), as well as a quality bar that shows in green the proportion of values formatted correctly – those in red formatted incorrectly and require action, and those in gray are empty.

   You can sort the displayed cards in original, ascending, or descending order, or according to the model structure (listing all measures first, followed by dimensions). If you switch to (Grid View), the columns associated with your selected cards are automatically highlighted.

   Note

   Use the (Show/Hide) icon to limit the view to a few selected columns in either (Grid View) or Card View. A drop-down list of all the columns in the uploaded data is displayed under (Show/Hide). All columns listed with a check mark are displayed in the current view. Select a column name to show or hide the column. You can use the search tool to look for a specific column. You can also hide columns by directly selecting columns in the data integration view, and choosing (More Actions)  (Hide selected).
2. The Details panel is divided into two sections:
   • Model level: shows general information about the imported data, including any data-quality issues including mapping issues, and model-specific information. A Model Options section is provided to enable planning, set the default currency for the model, plus other options.
   • Column level: allows you to map columns to existing dimensions in the model, displays histograms for the currently selected column (rectangles indicating frequency of data items in successive numerical intervals), lists the attribute and dimension types that you can apply to the column, and displays any data-quality issues represented as quality bars under Data Quality. The green section indicates correct values, while those in red indicate incorrect values. Those in gray are empty.
3. When you select a column in (Grid View) or Card View, numerical or textual histograms are displayed under Data Distribution.
   • Text histograms are horizontal, and the values are clustered by count. The number of clusters can be adjusted by dragging the slider shown above the histogram. When a cluster contains more than one value, the displayed count is the average count for the cluster. The count is prefaced by a tilde symbol (~) if there are multiple different occurrences. Expand the cluster for a more detailed view of the values in the cluster along with individual counts. Use the search tool to look up specific column values, and press Enter to initiate the search. When you select a value in the histogram, the corresponding column is sorted and the value is highlighted in the grid.
     Note

     The displayed histogram is determined by the data type of the column. A column with numbers could still be considered as text if the data type is set to text.
   • Numerical histograms are vertical, and represent the range of values along the x-axis. Hover over any bar to show the count, minimum, and maximum values for the data in the bar. The number of bars can also be adjusted by using the slider above the histogram. Toggling the Show Outliers box includes or removes outliers from the histogram.
     Note

     Below the histogram, there is a box and whisker plot to visualize the histogram's distribution of values.
4. You use level-based hierarchies when your data is organized into levels, such as Product Category, Product Group, and Product. When the data is displayed in a story, hierarchies can be expanded or collapsed. In the toolbar, select (Level Based Hierarchy). For more information, see Learn About Hierarchies.
5. Select any of the following Model Options:
   • Specify whether you want the model to be Planning Enabled. For information about model types, see Selecting the Right Model for Your Data.
   • Specify whether you want empty ID cells to be filled with automatically generated values. If you clear this check box, rows with empty ID cells will be omitted.

Viewing and mapping data imported to an existing model

The Card View displays after you import data into an existing model. By default, cards representing dimensions and measures are displayed. Double-click a card to display attribute cards under the selected card.

Cards display as either mapped or unmapped entities. A card represents mapped data if it is shaded solid and has defined borders.

A card represents unmapped data if it does not have a border and appears transparent.

A red dot in the top right corner of the card indicates there are mapping errors that need to be addressed. A blue dot indicates that new values have been added from the import to the existing data.

Cards display sample values and icons for specific data types. The table below summarizes the available data typology:

Icon	Data Type
	Generic dimension
	Date dimension
	Account dimension
	Measure
	Version dimension
	Organization dimension
	Attribute only available for organization dimension
	Description or property attributes (for example, unit, scale, or decimal)
	Timestamp dimension
	Hierarchy attribute

When you select a card, the Details panel is populated with information specific to your selection – the number of rows, unique values, data types, and corresponding histograms. A pie chart is provided under Mapping Quality showing the proportion of Accepted versus Rejected values.

To the left of the card view, a panel containing the imported data columns is displayed. Use any of the following sort options:

• Original Order: reflects the original order in the imported data source.
• A-Z: alphabetical order based on column headings.
• Z-A: reverse alphabetical order based on column headings.
• Mapped First: mapped columns listed alphabetically before unmapped columns.
• Unmapped First: unmapped columns listed alphabetically before mapped columns.

You can use the search tool to look for a specific column. When you select an entry in the imported column panel, the Details panel is populated with information specific to your selection: the number of rows, unique values, data types, and corresponding histograms. Use this information to determine what kind of data is contained in the imported column, and this information will be invaluable for mapping purposes.

The following headings appear under color-coded progress bars indicating what percentage of the imported data was accepted: Dimensions, Attributes, and Measures. If a progress bar is not 100 percent blue, select the heading for more information on the mapping progress and a listing of unmapped entities.

Combining data with your acquired data

Importing cross table data