'[Mondrian] that was fun...'

[prev in list] [next in list] [prev in thread] [next in thread] 

List: mondrian
Subject: [Mondrian] that was fun...
From: "Ati Rosselet" <ati.rosselet () gmail ! com>
Date: 2008-12-15 1:04:02
Message-ID: ce6402fc0812141704t7f64f2b5wef7a5367e3ac54db () mail ! gmail ! com
[Download RAW message or body]

[Attachment #2 (multipart/alternative)]

ok... so there is a bug in the rollupPolicy="partial" implementation...
that's over my head.

My next task... adding drillthrough columns for the fact table.. NOT present
in any dimensions. Typically information fields.. that really wouldn't
aggregate.
I implemented an extension to the Cube in mondrian.xml
<Attribute name="extDrillThroughCols" type="String" required="false">
            <Doc>
                Specify additional columns to be added from fact table at
drillthrough
                format "colname:alias,colname:alias"
            </Doc>
        </Attribute>
and then modified RolapCube.java , adding a field

   private String[] extraDrillThroughColumns;

and to the NON virtual constructor:

   if (xmlCube.extDrillThroughCols!=null)
            this.extraDrillThroughColumns =
xmlCube.extDrillThroughCols.split(",");

now all I needed to do was modify the DrillThroughQuerySpec, and it all
seems to work..
I can now specify my additional tables like:
<Cube name="FI" extDrillThroughCols="text2:Info,text3:Text3"> (format is
"column:alias", where column is a column in the fact table).

if anyone is interested.. the DrillThroughQuerySpec changes are as follows:
added field:
   private final ArrayList<String[]> extColumnDefs;
changed constructor:
        super(request.getMeasure().getStar(), countOnly);
        this.request = request;
        extColumnDefs=getCubeExtraColDefs();   // new for handling extra
drillthrough columns

added:
 private ArrayList<String[]> getCubeExtraColDefs() {
        ArrayList<String[]> retval = new ArrayList<String[]>();
        try {
            mondrian.rolap.RolapCube cube = (mondrian.rolap.RolapCube)
getStar().getSchema().lookupCube(request.getMeasure().getCubeName(), false);
            if (cube != null && !cube.isVirtual() &&
cube.getExtraDrillThroughColumns() != null) {

                for (String extColDef : cube.getExtraDrillThroughColumns())
{
                    String[] pair = extColDef.split(":");
                    retval.add(pair);
                }
                if (retval.size() != 0) {
                    // if extra columns are defined, save the factable name
to the list - probably prettier in a separate variable... TBD
                    retval.add(0, new String[]{"fact",
cube.getStar().getFactTable().getTableName()});
                }
            }
        } catch (Exception ex) {
            System.out.println("cube lookup failed??");
        }
        return retval;
    }

and modified generateSqlQuery() to handle defined extra columns

public String generateSqlQuery() {
        SqlQuery sqlQuery = newSqlQuery();
        nonDistinctGenerateSql(sqlQuery);
        if (extColumnDefs.size()!=0){
            String factTable= extColumnDefs.get(0)[1];
            for(int i=1;i<extColumnDefs.size();i++){
                String[] pair = extColumnDefs.get(i);
                sqlQuery.addSelect("\""+factTable+"\".\""+pair[0]+"\"",
pair[1]);
            }
        }
        return sqlQuery.toString();
    }

works great in JPivot too...

Cheers
Ati

[Attachment #5 (text/html)]

ok... so there is a bug in the rollupPolicy=&quot;partial&quot; implementation... \
that&#39;s over my head. My next task... adding drillthrough columns for the \
fact table.. NOT present in any dimensions. Typically information fields.. that \
really wouldn&#39;t aggregate. I implemented an extension to the Cube in \
mondrian.xml &lt;Attribute name=&quot;extDrillThroughCols&quot; \
type=&quot;String&quot; \
required=&quot;false&quot;&gt; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \
&lt;Doc&gt; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \
Specify additional columns to be added from fact table at drillthrough \
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \
format &quot;colname:alias,colname:alias&quot; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \
&lt;/Doc&gt; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;/Attribute&gt; and \
then modified RolapCube.java , adding a field &nbsp;&nbsp; private String[] \
extraDrillThroughColumns; and to the NON virtual \
constructor: &nbsp;&nbsp; if \
(xmlCube.extDrillThroughCols!=null) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \
this.extraDrillThroughColumns = \
xmlCube.extDrillThroughCols.split(&quot;,&quot;); now all I needed to do \
was modify the DrillThroughQuerySpec, and it all seems to work.. I can now \
specify my additional tables like: &lt;Cube name=&quot;FI&quot; \
extDrillThroughCols=&quot;text2:Info,text3:Text3&quot;&gt;&nbsp;&nbsp; (format is \
&quot;column:alias&quot;, where column is a column in the fact table). if \
anyone is interested.. the DrillThroughQuerySpec changes are as follows: added \
field: &nbsp;&nbsp; private final ArrayList&lt;String[]&gt; \
extColumnDefs; changed constructor: &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \
super(request.getMeasure().getStar(), countOnly); \
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; this.request = \
request; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \
extColumnDefs=getCubeExtraColDefs();&nbsp;&nbsp; // new for handling extra \
drillthrough columns added: &nbsp;private ArrayList&lt;String[]&gt; \
getCubeExtraColDefs() { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \
ArrayList&lt;String[]&gt; retval = new ArrayList&lt;String[]&gt;(); \
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; try \
{ &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \
mondrian.rolap.RolapCube cube = (mondrian.rolap.RolapCube) \
getStar().getSchema().lookupCube(request.getMeasure().getCubeName(), \
false); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if \
(cube != null &amp;&amp; !cube.isVirtual() &amp;&amp; \
cube.getExtraDrillThroughColumns() != null) { \
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \
for (String extColDef : cube.getExtraDrillThroughColumns()) \
{ &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \
String[] pair = extColDef.split(&quot;:&quot;); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \
retval.add(pair); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \
} &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \
if (retval.size() != 0) { \
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \
// if extra columns are defined, save the factable name to the list - probably \
prettier in a separate variable... TBD&nbsp; \
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \
retval.add(0, new String[]{&quot;fact&quot;, \
cube.getStar().getFactTable().getTableName()}); \
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \
} &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \
} &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } catch (Exception ex) \
{ &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \
System.out.println(&quot;cube lookup \
failed??&quot;); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \
} &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; return retval; &nbsp;&nbsp;&nbsp; \
} and modified generateSqlQuery() to handle defined extra columns \
 public String generateSqlQuery() { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \
SqlQuery sqlQuery = newSqlQuery(); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \
nonDistinctGenerateSql(sqlQuery); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if \
(extColumnDefs.size()!=0){ &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \
String factTable= extColumnDefs.get(0)[1]; \
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; for(int \
i=1;i&lt;extColumnDefs.size();i++){ &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \
String[] pair = extColumnDefs.get(i); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \
sqlQuery.addSelect(&quot;\&quot;&quot;+factTable+&quot;\&quot;.\&quot;&quot;+pair[0]+&quot;\&quot;&quot;, \
pair[1]); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \
} &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \
}&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; return \
sqlQuery.toString(); &nbsp;&nbsp;&nbsp; } works great in JPivot \
too... Cheers Ati

_______________________________________________
Mondrian mailing list
Mondrian@pentaho.org
http://lists.pentaho.org/mailman/listinfo/mondrian

[prev in list] [next in list] [prev in thread] [next in thread] 