Export Mondrian Schema to Excel using PDI

This blog explains how to create a job in PDI which can export Mondrian schema which is in XML format to excel.

Consider below a sample of Mondrian Schema:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<Schema name="TransactionType">
  <Dimension highCardinality="false" name="Dim Customers" type="StandardDimension" visible="true">
    <Hierarchy hasAll="true" name="Customer Billing Details" primaryKey="dim_customer_id" visible="true">
      <Table alias="Customer Table" name="dim_customers" schema="public">
      </Table>
      <Level column="billing_state" hideMemberIf="Never" levelType="Regular" name="Billing state" type="String" uniqueMembers="true" visible="true">
      </Level>
      <Level column="billing_city" hideMemberIf="Never" levelType="Regular" name="Billing City" type="String" uniqueMembers="true" visible="true">
      </Level>
      <Level column="customer_name" hideMemberIf="Never" levelType="Regular" name="Customer Name" type="String" uniqueMembers="true" visible="true">
      </Level>
    </Hierarchy>
  </Dimension>
  <Cube cache="true" enabled="true" name="TransTypeSchema" visible="true">
    <Table alias="Fact Sales" name="fact_sales" schema="public">
    </Table>
    <DimensionUsage foreignKey="dim_customer_id" highCardinality="false" name="Dim Customers" source="Dim Customers" visible="true">
    </DimensionUsage>
    <Measure aggregator="sum" column="netamt" datatype="Numeric" formatString="#,###.00" name="Invoice Amount" visible="true">
    </Measure>
  </Cube>
</Schema>

Now, we would create a job in PDI which gives an excel export with columns Dimension, Hierarchy, and Level.

Below is the job created to export the above schema to XML.

The transformation used ‘Export Levels Schema’ is as shown

1. Get data from XML
2. Microsoft Excel writer

1. Get data from XML:

This component provides the ability to read data from any type of XML file using XPath specifications. There are three tabs in this component out of which first three are mandate.

1. File:

This tab is where you define the location of the XML files from which you want to read.



2. Content :

Required loop format is mentioned in this component.



3. Fields:

Fields to be exported are given here along with field details and XPath.



2. Microsoft Excel Writer :

This component writes incoming rows from PDI out to an MS Excel file and supports both the .xls and .xlsx file formats. We need to mention the location of the exported excel file and content to be filled in the excel in this component.

When you run the job, you would get an excel sheet which is as shown

In case if you have any queries please get us at support@helicaltech.com

Thank You
Mounika Pulimamidi
BI Developer
Helical IT Solutions Pvt Ltd

Best Open Source Business Intelligence Software Helical Insight Here

A Business Intelligence Framework

Free
Download