Baisc concept of MDX

Hey Guys,

Here i would like to explain three main concept:unique names, tuples, and sets.

Unique Names:

• All objects in MDX, from cubes to dimensions to hierarchies, levels and members, have unique names
• These are the names you should use when writing MDX to uniquely identify an object in a cube

Tuples:

• A tuple is a way of referring to a single value within a cube
• If you think of a cube as a gigantic multidimensional array, a tuple is a co-ordinate to one cell within that array.
• Tuples are written as comma-delimited lists of members, from one or more different hierarchies,
• and are surrounded by round brackets: ()

Example:

([MEASURES].[sale Amount],[DATE].[Calendar].[Calendar Year].&[2014])


Sets:

• A set is an ordered list of members, or tuples, surrounded by curly brackets: {}
• Each item in a set has to be the same type of a thing: for a set of members, each member has to be from the same hierarchy
• Sets are used to define what appears on the rows and columns of an MDX query, and are also used in calculations

Example:
1) {[Date].[Date].&[20110702],

      [Date].[Date].&[20110703],

     [Date].[Date].&[20110704]} 

    Valid set member of date hierarchy from date dimension

2){[Date].[Date].&[20110702],

     [Date].[Date].&[20110703],

     [Date].[Date].&[20110704],

    [Date][Calendar][Date].&[]20110709}

Not a valid set because here date level is calendar hierarchy from date dimension and every thing else is member of date hierarchy from date dimension  so its not valid set.

Cheers !!!!! 

  

Sumit Bansal

BI Developer

Refresh Pentaho's cache system

Hey Guys,

In this post i would like to explain to refresh  Pentaho's cache system (mondrian, CDE, etc.) using a shell script.

Scenario :

Each night, after my ETL process has gone ok (thks Kettle), all data marts populated + aggregations and data validated, I want an automatic refresh of Mondrian cache .

Script:

#!/bin/bash

USER=admin
PASS=password

LOG=/var/pentaho/clearCache.log      /*Location of generating the log file for you Audit purpose*/

logCacheClear() {
    if [ "$1" = "START" ]; then
        echo "[`date +"%Y/%m/%d %H:%M:%S"`] ========== Cleaning $2 cache ==========" >> $LOG
    else
        echo >> $LOG
        echo "[`date +"%Y/%m/%d %H:%M:%S"`] ========== Finished cleaning $2 cache ==========" >> $LOG
    fi
}

execCacheClear() {
    echo -n "Cleaning $1 cache..."
    logCacheClear START $1
    wget --quiet --auth-no-challenge --http-user=$USER --http-password=$PASS -O - "$2" >> $LOG
    logCacheClear STOP $1
    echo " ok"
}


execCacheClear "Mondrian" "http://localhost:8080/pentaho/api/system/refresh/mondrianSchemaCache"

execCacheClear "Dashboards" "http://localhost:8080/pentaho/content/cda/clearCache"


Typically you'd run this script after you run your dashboard ETL.

• If you're running UNIX put this script in a cron job.

• If Windows, as a Scheduled Task. 

Cheers !!!!!
 
If you have any question you can ask me or else you can drop me email :

Sumit Bansal

BI Developer
sumitbansal450@gmail.com