Daanse Tutorial - Writeback View

This tutorial discusses writeback with fact as sql view.

Database Schema

The Database Schema contains the

SqlView FACT with 3 columns VAL, VAL1, L2. The L2 column is used as the discriminator in the Level and Hierarchy definitions
L1 table with two columns: L1 and L2.
L2 table with one column: L2.
FACTWB table with 4 columns: VAL, VAL1, ID, USER.

xml

<relational:Schema xmi:version="2.0" xmlns:xmi="http://www.omg.org/XMI"  xmlns:relational="http://www.omg.org/spec/CWM/1.1/resource/relational" xmi:id="_schema">
  <ownedElement xsi:type="relational:Table" xmi:id="_table_l1" name="L1">
    <feature xsi:type="relational:Column" xmi:id="_column_l1_l1" name="L1"/>
    <feature xsi:type="relational:Column" xmi:id="_column_l1_l2" name="L2"/>
  </ownedElement>
  <ownedElement xsi:type="relational:Table" xmi:id="_table_l2" name="L2">
    <feature xsi:type="relational:Column" xmi:id="_column_l2_l2" name="L2"/>
  </ownedElement>
  <ownedElement xsi:type="relational:Table" xmi:id="_table_factwb" name="FACTWB">
    <feature xsi:type="relational:Column" xmi:id="_column_factwb_val" name="VAL"/>
    <feature xsi:type="relational:Column" xmi:id="_column_factwb_val1" name="VAL1"/>
    <feature xsi:type="relational:Column" xmi:id="_column_factwb_l2" name="L2"/>
    <feature xsi:type="relational:Column" xmi:id="_column_factwb_id" name="ID"/>
    <feature xsi:type="relational:Column" xmi:id="_column_factwb_user" name="USER"/>
  </ownedElement>
</relational:Schema>

Note: This is only a symbolic example. For the exact definition, see the Definition section.

FactQuery

The SqlSelectSource with FACT alias that selects all columns from the Fact DialectSqlView to use in the cube for the measures. DialectSqlView have description as sql query

xml

<rolapsrc:SqlSelectSource xmi:version="2.0" xmlns:xmi="http://www.omg.org/XMI"  xmlns:relational="http://www.omg.org/spec/CWM/1.1/resource/relational" xmlns:rolapsrc="https://www.daanse.org/spec/org.eclipse.daanse.rolap.mapping/database/source" xmi:id="_sqlselectsource" alias="FACT">
  <sql xmi:id="_dialectsqlview_fact" name="FACT">
    <feature xsi:type="relational:Column" xmi:id="_column_fact_val" name="VAL"/>
    <feature xsi:type="relational:Column" xmi:id="_column_fact_val1" name="VAL1"/>
    <feature xsi:type="relational:Column" xmi:id="_column_fact_l2" name="L2"/>
    <dialectStatements xmi:id="_sqlstatement" sql="select * from FACT">
      <dialects>generic</dialects>
      <dialects>h1</dialects>
    </dialectStatements>
  </sql>
</rolapsrc:SqlSelectSource>

Note: This is only a symbolic example. For the exact definition, see the Definition section.

l1TableQuery

The l1Query is a simple TableSource that selects all columns from the L1 table to use in the cube for the L1 level.

xml

<xmi:XMI xmi:version="2.0" xmlns:xmi="http://www.omg.org/XMI"  xmlns:relational="http://www.omg.org/spec/CWM/1.1/resource/relational" xmlns:rolapsrc="https://www.daanse.org/spec/org.eclipse.daanse.rolap.mapping/database/source">
  <rolapsrc:TableSource xmi:id="_tablesource_l1" table="_table_l1"/>
  <relational:Table xmi:id="_table_l1" name="L1">
    <feature xsi:type="relational:Column" xmi:id="_column_l1_l1" name="L1"/>
    <feature xsi:type="relational:Column" xmi:id="_column_l1_l2" name="L2"/>
  </relational:Table>
</xmi:XMI>

Note: This is only a symbolic example. For the exact definition, see the Definition section.

l21TableQuery

The l2Query is a simple TableSource that selects all columns from the L2 table to use in the cube for the L2 level.

xml

<xmi:XMI xmi:version="2.0" xmlns:xmi="http://www.omg.org/XMI"  xmlns:relational="http://www.omg.org/spec/CWM/1.1/resource/relational" xmlns:rolapsrc="https://www.daanse.org/spec/org.eclipse.daanse.rolap.mapping/database/source">
  <rolapsrc:TableSource xmi:id="_tablesource_l2" table="_table_l2"/>
  <relational:Table xmi:id="_table_l2" name="L2">
    <feature xsi:type="relational:Column" xmi:id="_column_l2_l2" name="L2"/>
  </relational:Table>
</xmi:XMI>

Note: This is only a symbolic example. For the exact definition, see the Definition section.

joinQuery

The joinQuery is a simple JoinedQuery that unites l1TableQuery and l2TableQuery by L2 column.

xml

<xmi:XMI xmi:version="2.0" xmlns:xmi="http://www.omg.org/XMI"  xmlns:relational="http://www.omg.org/spec/CWM/1.1/resource/relational" xmlns:rolapsrc="https://www.daanse.org/spec/org.eclipse.daanse.rolap.mapping/database/source">
  <rolapsrc:JoinSource xmi:id="_joinsource">
    <left xmi:id="_joinedqueryelement_l2" key="_column_l1_l2" source="_tablesource_l1"/>
    <right xmi:id="_joinedqueryelement_l2_1" key="_column_l2_l2" source="_tablesource_l2"/>
  </rolapsrc:JoinSource>
  <rolapsrc:TableSource xmi:id="_tablesource_l2" table="_table_l2"/>
  <relational:Table xmi:id="_table_l1" name="L1">
    <feature xsi:type="relational:Column" xmi:id="_column_l1_l1" name="L1"/>
    <feature xsi:type="relational:Column" xmi:id="_column_l1_l2" name="L2"/>
  </relational:Table>
  <relational:Table xmi:id="_table_l2" name="L2">
    <feature xsi:type="relational:Column" xmi:id="_column_l2_l2" name="L2"/>
  </relational:Table>
  <rolapsrc:TableSource xmi:id="_tablesource_l1" table="_table_l1"/>
</xmi:XMI>

Note: This is only a symbolic example. For the exact definition, see the Definition section.

L1

This Example uses one simple L1 level based on the L1 column. L2 column to use for connection to level L2

xml

<rolaplev:Level xmi:version="2.0" xmlns:xmi="http://www.omg.org/XMI" xmlns:rolaplev="https://www.daanse.org/spec/org.eclipse.daanse.rolap.mapping/olap/dimension/hierarchy/level" xmi:id="_level_l1" name="L1">
  <column href="_column_l1_l1"/>
</rolaplev:Level>

Note: This is only a symbolic example. For the exact definition, see the Definition section.

L2

This Example uses one simple L2 level based on the L2 column. L2 column to use for connection to level L1

xml

<rolaplev:Level xmi:version="2.0" xmlns:xmi="http://www.omg.org/XMI" xmlns:rolaplev="https://www.daanse.org/spec/org.eclipse.daanse.rolap.mapping/olap/dimension/hierarchy/level" xmi:id="_level_l2" name="L2">
  <column href="_column_l2_l2"/>
</rolaplev:Level>

Note: This is only a symbolic example. For the exact definition, see the Definition section.

HierarchyWithHasAll

The Hierarchy is defined with the hasAll property set to true and the two levels.

xml

<xmi:XMI xmi:version="2.0" xmlns:xmi="http://www.omg.org/XMI"  xmlns:relational="http://www.omg.org/spec/CWM/1.1/resource/relational" xmlns:rolaphier="https://www.daanse.org/spec/org.eclipse.daanse.rolap.mapping/olap/dimension/hierarchy" xmlns:rolaplev="https://www.daanse.org/spec/org.eclipse.daanse.rolap.mapping/olap/dimension/hierarchy/level" xmlns:rolapsrc="https://www.daanse.org/spec/org.eclipse.daanse.rolap.mapping/database/source">
  <rolaphier:ExplicitHierarchy xmi:id="_explicithierarchy_hierarchywithhasall" name="HierarchyWithHasAll" primaryKey="_column_l1_l2" source="_joinsource" levels="_level_l1 _level_l2"/>
  <rolapsrc:TableSource xmi:id="_tablesource_l2" table="_table_l2"/>
  <relational:Table xmi:id="_table_l1" name="L1">
    <feature xsi:type="relational:Column" xmi:id="_column_l1_l1" name="L1"/>
    <feature xsi:type="relational:Column" xmi:id="_column_l1_l2" name="L2"/>
  </relational:Table>
  <rolaplev:Level xmi:id="_level_l2" name="L2" column="_column_l2_l2"/>
  <rolapsrc:JoinSource xmi:id="_joinsource">
    <left xmi:id="_joinedqueryelement_l2" key="_column_l1_l2" source="_tablesource_l1"/>
    <right xmi:id="_joinedqueryelement_l2_1" key="_column_l2_l2" source="_tablesource_l2"/>
  </rolapsrc:JoinSource>
  <relational:Table xmi:id="_table_l2" name="L2">
    <feature xsi:type="relational:Column" xmi:id="_column_l2_l2" name="L2"/>
  </relational:Table>
  <rolapsrc:TableSource xmi:id="_tablesource_l1" table="_table_l1"/>
  <rolaplev:Level xmi:id="_level_l1" name="L1" column="_column_l1_l1"/>
</xmi:XMI>

Note: This is only a symbolic example. For the exact definition, see the Definition section.

Dimension

The dimension is defined with the one hierarchy.

xml

<xmi:XMI xmi:version="2.0" xmlns:xmi="http://www.omg.org/XMI"  xmlns:relational="http://www.omg.org/spec/CWM/1.1/resource/relational" xmlns:rolapdim="https://www.daanse.org/spec/org.eclipse.daanse.rolap.mapping/olap/dimension" xmlns:rolaphier="https://www.daanse.org/spec/org.eclipse.daanse.rolap.mapping/olap/dimension/hierarchy" xmlns:rolaplev="https://www.daanse.org/spec/org.eclipse.daanse.rolap.mapping/olap/dimension/hierarchy/level" xmlns:rolapsrc="https://www.daanse.org/spec/org.eclipse.daanse.rolap.mapping/database/source">
  <rolapdim:StandardDimension xmi:id="_standarddimension_dimension" name="Dimension" hierarchies="_explicithierarchy_hierarchywithhasall"/>
  <rolapsrc:TableSource xmi:id="_tablesource_l2" table="_table_l2"/>
  <relational:Table xmi:id="_table_l1" name="L1">
    <feature xsi:type="relational:Column" xmi:id="_column_l1_l1" name="L1"/>
    <feature xsi:type="relational:Column" xmi:id="_column_l1_l2" name="L2"/>
  </relational:Table>
  <rolaphier:ExplicitHierarchy xmi:id="_explicithierarchy_hierarchywithhasall" name="HierarchyWithHasAll" primaryKey="_column_l1_l2" source="_joinsource" levels="_level_l1 _level_l2"/>
  <rolapsrc:JoinSource xmi:id="_joinsource">
    <left xmi:id="_joinedqueryelement_l2" key="_column_l1_l2" source="_tablesource_l1"/>
    <right xmi:id="_joinedqueryelement_l2_1" key="_column_l2_l2" source="_tablesource_l2"/>
  </rolapsrc:JoinSource>
  <rolaplev:Level xmi:id="_level_l2" name="L2" column="_column_l2_l2"/>
  <relational:Table xmi:id="_table_l2" name="L2">
    <feature xsi:type="relational:Column" xmi:id="_column_l2_l2" name="L2"/>
  </relational:Table>
  <rolapsrc:TableSource xmi:id="_tablesource_l1" table="_table_l1"/>
  <rolaplev:Level xmi:id="_level_l1" name="L1" column="_column_l1_l1"/>
</xmi:XMI>

Note: This is only a symbolic example. For the exact definition, see the Definition section.

Cubec C

Cube C is defined by DimensionConnector D1 and a MeasureGroup containing two measures using SUM aggregation. The cube also contains a FACTWB WritebackTable configuration with a WritebackAttribute mapped to the VAL column from the fact table, along with two WritebackMeasures: WbMeasure1 and WbMeasure2.

xml

<xmi:XMI xmi:version="2.0" xmlns:xmi="http://www.omg.org/XMI"  xmlns:relational="http://www.omg.org/spec/CWM/1.1/resource/relational" xmlns:rolapcube="https://www.daanse.org/spec/org.eclipse.daanse.rolap.mapping/olap/cube" xmlns:rolapdim="https://www.daanse.org/spec/org.eclipse.daanse.rolap.mapping/olap/dimension" xmlns:rolaphier="https://www.daanse.org/spec/org.eclipse.daanse.rolap.mapping/olap/dimension/hierarchy" xmlns:rolaplev="https://www.daanse.org/spec/org.eclipse.daanse.rolap.mapping/olap/dimension/hierarchy/level" xmlns:rolapmeas="https://www.daanse.org/spec/org.eclipse.daanse.rolap.mapping/olap/cube/measure" xmlns:rolapsrc="https://www.daanse.org/spec/org.eclipse.daanse.rolap.mapping/database/source">
  <rolapcube:PhysicalCube xmi:id="_physicalcube_c" name="C" source="_sqlselectsource">
    <dimensionConnectors xmi:id="_dimensionconnector_d1" dimension="_standarddimension_dimension" overrideDimensionName="D1">
      <foreignKey href="_column_l1_l2"/>
    </dimensionConnectors>
    <writebackTable xmi:id="_writebacktable_factwb" name="FACTWB">
      <writebackAttribute xmi:id="_writebackattribute_l2" column="_column_fact_l2" dimensionConnector="_dimensionconnector_d1"/>
      <writebackMeasure xmi:id="_writebackmeasure_measure1" column="_column_fact_val" name="Measure1"/>
      <writebackMeasure xmi:id="_writebackmeasure_measure2" column="_column_fact_val1" name="Measure2"/>
    </writebackTable>
    <measureGroups xmi:id="_measuregroup">
      <measures xsi:type="rolapmeas:SumMeasure" xmi:id="_summeasure_measure1" name="Measure1" column="_column_fact_val"/>
      <measures xsi:type="rolapmeas:SumMeasure" xmi:id="_summeasure_measure2" name="Measure2" column="_column_fact_val1"/>
    </measureGroups>
  </rolapcube:PhysicalCube>
  <rolapsrc:SqlSelectSource xmi:id="_sqlselectsource" alias="FACT">
    <sql xmi:id="_dialectsqlview_fact" name="FACT">
      <feature xsi:type="relational:Column" xmi:id="_column_fact_val" name="VAL"/>
      <feature xsi:type="relational:Column" xmi:id="_column_fact_val1" name="VAL1"/>
      <feature xsi:type="relational:Column" xmi:id="_column_fact_l2" name="L2"/>
      <dialectStatements xmi:id="_sqlstatement" sql="select * from FACT">
        <dialects>generic</dialects>
        <dialects>h1</dialects>
      </dialectStatements>
    </sql>
  </rolapsrc:SqlSelectSource>
  <rolapsrc:TableSource xmi:id="_tablesource_l2" table="_table_l2"/>
  <relational:Table xmi:id="_table_l1" name="L1">
    <feature xsi:type="relational:Column" xmi:id="_column_l1_l1" name="L1"/>
    <feature xsi:type="relational:Column" xmi:id="_column_l1_l2" name="L2"/>
  </relational:Table>
  <rolapdim:StandardDimension xmi:id="_standarddimension_dimension" name="Dimension" hierarchies="_explicithierarchy_hierarchywithhasall"/>
  <rolaphier:ExplicitHierarchy xmi:id="_explicithierarchy_hierarchywithhasall" name="HierarchyWithHasAll" source="_joinsource" levels="_level_l1 _level_l2">
    <primaryKey href="_column_l1_l2"/>
  </rolaphier:ExplicitHierarchy>
  <rolapsrc:JoinSource xmi:id="_joinsource">
    <left xmi:id="_joinedqueryelement_l2" key="_column_l1_l2" source="_tablesource_l1"/>
    <right xmi:id="_joinedqueryelement_l2_1" key="_column_l2_l2" source="_tablesource_l2"/>
  </rolapsrc:JoinSource>
  <rolaplev:Level xmi:id="_level_l2" name="L2" column="_column_l2_l2"/>
  <relational:Table xmi:id="_table_l2" name="L2">
    <feature xsi:type="relational:Column" xmi:id="_column_l2_l2" name="L2"/>
  </relational:Table>
  <rolapsrc:TableSource xmi:id="_tablesource_l1" table="_table_l1"/>
  <rolaplev:Level xmi:id="_level_l1" name="L1" column="_column_l1_l1"/>
</xmi:XMI>

Note: This is only a symbolic example. For the exact definition, see the Definition section.

Definition

This file represents the complete definition of the catalog.

xml

<?xml version="1.0" encoding="UTF-8"?>
<xmi:XMI xmi:version="2.0" xmlns:xmi="http://www.omg.org/XMI" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:relational="http://www.omg.org/spec/CWM/1.1/resource/relational" xmlns:rolapcat="https://www.daanse.org/spec/org.eclipse.daanse.rolap.mapping/catalog" xmlns:rolapcube="https://www.daanse.org/spec/org.eclipse.daanse.rolap.mapping/olap/cube" xmlns:rolapdim="https://www.daanse.org/spec/org.eclipse.daanse.rolap.mapping/olap/dimension" xmlns:rolaphier="https://www.daanse.org/spec/org.eclipse.daanse.rolap.mapping/olap/dimension/hierarchy" xmlns:rolaplev="https://www.daanse.org/spec/org.eclipse.daanse.rolap.mapping/olap/dimension/hierarchy/level" xmlns:rolapmeas="https://www.daanse.org/spec/org.eclipse.daanse.rolap.mapping/olap/cube/measure" xmlns:rolapsrc="https://www.daanse.org/spec/org.eclipse.daanse.rolap.mapping/database/source">
  <relational:SQLSimpleType xmi:id="_sqlsimpletype_character_varying" name="CHARACTER VARYING" structuralFeature="_column_l1_l1 _column_factwb_id _column_l1_l2 _column_l2_l2 _column_factwb_l2 _column_fact_l2 _column_factwb_user" typeNumber="12"/>
  <relational:SQLSimpleType xmi:id="_sqlsimpletype_integer" name="INTEGER" structuralFeature="_column_factwb_val _column_fact_val _column_fact_val1 _column_factwb_val1" typeNumber="4"/>
  <rolapcat:Catalog xmi:id="_catalog_writeback_view" description="View-based writeback functionality" name="Daanse Tutorial - Writeback View" cubes="_physicalcube_c" dbschemas="_schema"/>
  <relational:Schema xmi:id="_schema">
    <ownedElement xsi:type="relational:Table" xmi:id="_table_l1" name="L1">
      <feature xsi:type="relational:Column" xmi:id="_column_l1_l1" name="L1" type="_sqlsimpletype_character_varying"/>
      <feature xsi:type="relational:Column" xmi:id="_column_l1_l2" name="L2" type="_sqlsimpletype_character_varying"/>
    </ownedElement>
    <ownedElement xsi:type="relational:Table" xmi:id="_table_l2" name="L2">
      <feature xsi:type="relational:Column" xmi:id="_column_l2_l2" name="L2" type="_sqlsimpletype_character_varying"/>
    </ownedElement>
    <ownedElement xsi:type="relational:Table" xmi:id="_table_factwb" name="FACTWB">
      <feature xsi:type="relational:Column" xmi:id="_column_factwb_val" name="VAL" type="_sqlsimpletype_integer"/>
      <feature xsi:type="relational:Column" xmi:id="_column_factwb_val1" name="VAL1" type="_sqlsimpletype_integer"/>
      <feature xsi:type="relational:Column" xmi:id="_column_factwb_l2" name="L2" type="_sqlsimpletype_character_varying"/>
      <feature xsi:type="relational:Column" xmi:id="_column_factwb_id" name="ID" type="_sqlsimpletype_character_varying"/>
      <feature xsi:type="relational:Column" xmi:id="_column_factwb_user" name="USER" type="_sqlsimpletype_character_varying"/>
    </ownedElement>
  </relational:Schema>
  <rolapsrc:TableSource xmi:id="_tablesource_l1" table="_table_l1"/>
  <rolapsrc:TableSource xmi:id="_tablesource_l2" table="_table_l2"/>
  <rolapsrc:JoinSource xmi:id="_joinsource">
    <left xmi:id="_joinedqueryelement_l2" key="_column_l1_l2" source="_tablesource_l1"/>
    <right xmi:id="_joinedqueryelement_l2_1" key="_column_l2_l2" source="_tablesource_l2"/>
  </rolapsrc:JoinSource>
  <rolapsrc:SqlSelectSource xmi:id="_sqlselectsource" alias="FACT">
    <sql xmi:id="_dialectsqlview_fact" name="FACT">
      <feature xsi:type="relational:Column" xmi:id="_column_fact_val" name="VAL" type="_sqlsimpletype_integer"/>
      <feature xsi:type="relational:Column" xmi:id="_column_fact_val1" name="VAL1" type="_sqlsimpletype_integer"/>
      <feature xsi:type="relational:Column" xmi:id="_column_fact_l2" name="L2" type="_sqlsimpletype_character_varying"/>
      <dialectStatements xmi:id="_sqlstatement" sql="select * from FACT">
        <dialects>generic</dialects>
        <dialects>h1</dialects>
      </dialectStatements>
    </sql>
  </rolapsrc:SqlSelectSource>
  <rolaplev:Level xmi:id="_level_l1" name="L1" column="_column_l1_l1"/>
  <rolaplev:Level xmi:id="_level_l2" name="L2" column="_column_l2_l2"/>
  <rolaphier:ExplicitHierarchy xmi:id="_explicithierarchy_hierarchywithhasall" name="HierarchyWithHasAll" primaryKey="_column_l1_l2" source="_joinsource" levels="_level_l1 _level_l2"/>
  <rolapdim:StandardDimension xmi:id="_standarddimension_dimension" name="Dimension" hierarchies="_explicithierarchy_hierarchywithhasall"/>
  <rolapcube:PhysicalCube xmi:id="_physicalcube_c" name="C" source="_sqlselectsource">
    <dimensionConnectors xmi:id="_dimensionconnector_d1" foreignKey="_column_l1_l2" dimension="_standarddimension_dimension" overrideDimensionName="D1"/>
    <writebackTable xmi:id="_writebacktable_factwb" name="FACTWB">
      <writebackAttribute xmi:id="_writebackattribute_l2" column="_column_fact_l2" dimensionConnector="_dimensionconnector_d1"/>
      <writebackMeasure xmi:id="_writebackmeasure_measure1" column="_column_fact_val" name="Measure1"/>
      <writebackMeasure xmi:id="_writebackmeasure_measure2" column="_column_fact_val1" name="Measure2"/>
    </writebackTable>
    <measureGroups xmi:id="_measuregroup">
      <measures xsi:type="rolapmeas:SumMeasure" xmi:id="_summeasure_measure1" name="Measure1" column="_column_fact_val"/>
      <measures xsi:type="rolapmeas:SumMeasure" xmi:id="_summeasure_measure2" name="Measure2" column="_column_fact_val1"/>
    </measureGroups>
  </rolapcube:PhysicalCube>
</xmi:XMI>

Tutorial Zip

This file contains the data-tables as csv and the mapping as xmi file.

Download Zip File

Daanse Tutorial - Writeback View ​

Database Schema ​

FactQuery ​

l1TableQuery ​

l21TableQuery ​

joinQuery ​

L1 ​

L2 ​

HierarchyWithHasAll ​

Dimension ​

Cubec C ​

Definition ​

Tutorial Zip ​

Daanse Tutorial - Writeback View

Database Schema

FactQuery

l1TableQuery

l21TableQuery

joinQuery

L1

L2

HierarchyWithHasAll

Dimension

Cubec C

Definition

Tutorial Zip