Data-Driven Modeling in Excel Add-in

image_pdfimage_print

Related link: Column headers in Data-Driven OLAP

Data-driven modeling is an innovative process that enables you to analyze existing data earlier and faster.

Instead of creating OLAP cubes manually, data-driven modeling allows you to generate and fill a new Jedox OLAP cube with your existing data in CSV or Excel content. The Data-Driven Modeling Wizard models the data cube from existing data automatically and intelligently. Additionally, data-driven modeling creates a complete Jedox Integrator (ETL) script that can be further specified and used to populate the data cubes on a periodic basis.

Below are the requirements for the data-driven modeling procedure.

Configuration Requirements

Jedox Web and Jedox Integrator must be installed and the connection from Excel Add-in to Jedox Web must work correctly. For more information, see Connecting Jedox Excel Add-in with Jedox Web.

Data-driven modeling is by default set for a maximum file size of 128 MB. This default value can be changed in the file <install_path>\httpd\php\php.ini in the lines post_max_size and upload_max_filesize. To disable the limit, set the post_max_size value to 0.

To use DDM in an environment with Windows Single Sign-On, see the article SSO Windows Configuration.

Rights Requirements

A user requires delete (D) rights on the following rights objects: database, cube, dimension, dimension element, cell data, ste_files, ste_etl, and ste_reports.

Importing Data Files

Jedox comes with three sample files to test Data-Driven Modeling, all of which can be found in C:\Users\Public\Public Documents\Jedox\Jedox Suite. Two of the samples consist of comma-separated values and one is an Excel spreadsheet. In the example below, we will use the CSV file FiletoOLAP1.csv.

To begin the data import, click on Data-Driven Modeling in the Jedox Ribbon, as indicated below.

When the wizard opens, browse to the sample file location C:\Users\Public\Public Documents\Jedox\Jedox Suite and select FiletoOLAP1.csv. Check the box to show detailed settings and click Next.

The next screen provides a preview of the data:

Here you can change the suggested names for project, database, and cube. You can also overwrite an existing project, append data, and create dimension versions with variance, consolidated of Actual(1), and Budget(-1). Imported data will be written in Actual.

Notes:

  • If a Jedox Integrator (ETL) project exists, you have to check “Overwrite”; otherwise you receive a corresponding message and nothing will be created or imported.
  • If database name and cube name already exist, you have to check “Append data”, otherwise existing dimensions and elements will be deleted in the cube.

For XLSX imports, the Range option can be used to reduce the cell range that is used for the import.

Click Execute to start data import and cube creation:

After the import is done, you will receive a success message or, if there was a problem, an error message. If successful, you can check the Modeler for all creations/changes regarding your database name and cube name.

In Jedox Web – Panel Integrator, you will find your entered project name with all new modules created with the Data-Driven Modeling Wizard.

image_pdfimage_print