Exporting Metadata and Fact Data

Exporting Metadata and Fact Data

There are two options to export metadata:

  • Export Metadata: This function exports just the visible components of the model, such as cubes, members, and dimensions…but  not the additional technical components that are in many databases, such as Xchange definitions (drill throughs, roll-ups, and so on). It lets you select the specific metadata component and output the metadata to a text file

  • Export Metadata To XML: This function exports everything in the database, except for fact data, to a text file in an industry-standard XML (Extensible Markup Language) format.

Exporting metadata

To export metadata:

  1. From the Data ribbon's Export Group, click Export Metadata to open the Export Metadata dialog.

exportmetadataacmemargin.png

Figure 1.    Export Data Dialog - ACMEMargin

  1. For the sake of this example, we'll save the export file as: C:\Program Files\Satori\proCube\Examples\ACMEMargin. Make sure that this directory exists on the local C:\ drive of your client.

  2. In the Cubes list box, click Margin.

You can export Metadata from several Cubes or from all Cubes in the database using Ctrl-click or Shift-click.

  1. Make sure that Formulas, Mark Dimension, and Aliases are selected. If Mark Dimension is selected, then all the dimensions for the selected Cubes will be selected by default. If Mark Dimension is deselected, you will need to, within the Dimensions list box, select one or more of dimension names. If you do not wish to include formulas or aliases in the Metadata export, deselect the appropriate boxes.

  2. Select Tab Delimiter to produce a tab-delimited file. The Tab, Comma, Period or Other delimiters determine how the pieces of information are parsed in the flat, delimited export text file.

  3. Make sure the dialog appears as shown above.

  4. Click OK to begin the export process. A message displays indicating that the process was successful.

successfulexportmessage.png

Figure 2.    Successful Export to File Message

In this example, all defaults were maintained in the export of the Acme Margin Cube’s Metadata.

  1. Click OK. The export script is written to the chosen file and location.

The metadata export script

The following figure shows the first few lines of the TXT file that represents the Metadata from the Margin Cube.  These initial lines describe the Product dimension (indicated by “D+”).  These lines also tell you something about the Product dimension—there are no Alias Groups or Property Groups in the Dimension (if there were, this fact would be indicated below the Alias Group and Property Group headers).  Beginning with the “M+” indicator, the Members for the Dimension are listed.

You can scroll down to see how the script appears, all the way down to where the Cube’s formulas are shown:

metadatamargincubenotepad.png

Figure 3.    Metadata from Margin Cube in Notepad

A full explanation of the Metadata export script appears in Advanced Reference Materials.

Exporting metadata to XML

Exporting metadata to XML works a bit different than exporting metadata to a *.txt file, namely that it exports the entire current database rather than a specified cube.

To export metadata to XML:

  1. From the Data ribbon's Export Group, click Metadata to XML. A save dialog opens and you will name your file and determine the directory where the file will be written.

  2. Enter ACMEMargin.xml and click Save.

  3. Locate and view your *.xml file.

Exporting cube fact data

The Export Cube command allows you to export the Fact Data for an entire Cube or for specific portions of a Cube. This command only exports Fact Data for the selected cube; the Cube’s Metadata must be exported using Export Metadata.

To export the Fact Data for an entire Cube or for specific portions of a Cube:

  1. From the Data ribbon's Export Group, click Cube to open the Export Cube Dialog.

The Cube drop-down menu, shown at the top left, lists all Cubes in the current database for which you have security privileges to access.

  1. From the Cube drop-down menu, select Sales.

The Dimensions list contains the dimensions for the selected Cube.

  1. Select a dimension from the list — all members for that dimension appear in the Members: list.

exportcubedialogacmecubeex.png

Figure 4.    Export Cube Dialog - ACMECubeEx

By default, the All radio button is active. To select individual Members, check the box next to each Member name (note that the Selected radio button becomes active) to export Fact Data only for those Members.

You can choose to Skip Zeros, Skip Aggregates and Skip Calculated Values by maintaining the default setting, which causes the export of only Detail-level values that are notcalculated, thus enhancing export performance.

Export Cube Dialog Skip Options

Description

Skip Zeros

Eliminates any attempt to write zero values from the source Cube to the export file. In other words, zeros will not be exported. Uncheck this option if you want all values, including zeros, to be transferred and, thus, overwrite the existing destination values. This feature is a performance optimization and therefore is selected by default.

Skip Aggregates

Eliminates any attempts to write aggregated values from the source Cube to the export file. This feature is a performance optimization and therefore is selected by default.

Skip Calculated Values

Eliminate any attempt to write formula values from the source Cube to the export file. If this is selected (default), values calculated by formulas will not be written to the export file.

Deselecting Skip Calculated Values will initiate writing formula values from the source Cube to the export file. Performing this operation may take a considerable amount of time depending on the size of the source Cube and complexity of the formulas.  However, enabling and writingDependencies for the source Cube can expedite this operation. For more information on dependencies, refer to Cube Dependencies.

If you export Cube Fact Data without Aggregates and Calculated Values, and proceed to import the file (described later in this topic) into a Cube with the same Hierarchy structure and Formulas, then all values will appear as in the “original export Cube.” The fact that zeros are not exported (then imported) will not matter—proCube puts a zero value in Dimension-member intersection points where there is no information (“null”).

  1. Click Browse to open the Save As dialog and specify your path and the file name ACMECubeEx.

  2. Click OK to export the Cube. In this example, export Fact Data for the Sales cube, having maintained all defaults in the Export Cube dialog.

Security privileges are maintained in the Fact Data export process. If a user does not have Read access to specific data points, then the values for those data points will not be exported. Refer to Applying Database Security.

An export of Fact Data—both for Cubes and Slices—supports numeric as well as string values.

Exporting cube fact data script

The following figure shows the first few lines of the TXT file that represents the Fact Data from the Sales cube. The first few lines indicate that Members from the Region, Product, Sales Account and Month dimension will appear in succession. As you can see, this information is followed by a numeric value, which occurs at the intersection of those Members.

factdatasalescubenotepad.png

Figure 5.    Sales Cube Fact Data in Notepad

A full explanation of the cube fact data export script appears in Advanced Reference Materials.

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk