Transform Tabular Data to Cubes

You can create a cube from tabular data by extracting fact tables from a relational database into an Excel file and then deploying the cube.

Patterns in the relationships between column headers and data are detected to deploy a multidimensional cube. The process for transforming tabular data into a structure that can be used in a multidimensional cube include these concepts:

  • Correlations between columns

  • Correlations between column types (such as date, number, and text)

  • Header text analysis for common prefixes and business intelligence-related terms (such as cost, price, account)

  • Report structure (such as merged cells and empty cells)

  • (Optional) Forced-designation headers that are used to explicitly define the shape of a cube and can include formulas to create measures dimensions.

  • Measures hierarchies (which can also be generated in Transform Data in Cube Designer).

Sample tabular data Excel files are provided to demonstrate the concepts of intrinsic and forced-designation headers.

When working with tabular data, you should analyze the data before you create a cube from it. Then, after the cube is created, you should determine if the cube outline is the way you want it.

You can create a cube from tabular data in the cloud service or in Cube Designer. See Create and Update a Cube from Tabular Data.