Basic syntax of Writing MDX query

Hey Guys,

In this post I would like to explain the Basic syntax of Writing MDX query.

Introduction  :

MDX stands for 'multi-dimensional expressions'. It is the main query language implemented by Mondrian.

MDX was introduced by Microsoft with Microsoft SQL Server OLAP Services in around 1998, as the language component of the OLE DB for OLAP API.

More recently, MDX has appeared as part of the XML for Analysis API. Microsoft proposed MDX as a standard, and its adoption among application writers and other OLAP providers is steadily increasing.

Syntax:                

SELECT {set 0} on COLUMNS,                      /* block comment */

{set 1} on ROWS                                               // line comment

FROM [cube]

WHERE (tuple) 

Note:

•   No axes or the WHERE statement can share any of the same dimensions
•   FROM clause can name only one cube at a time
•   The WHERE clause describes the slicer axes

Dimension Representation:

-              [<dimension_name>.<hierarchy_name>].[<level1>].[<level2>]...[<leveln>].MEMBERS

-  [<dimension_name>.<hierarchy_name>].[<level1>].[<level2>]...[<level_n-1>].[<MEMBERNAME>]

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

Sumit Bansal

BI Developer

 

 

Installation of MongoDB

 Hey Guys,

In this post i would like to explain the basic installation of MongoDB

Steps to Install MongoDB

Download MongoDB file from the link https://www.mongodb.org/downloads
Extract the zip filer or install the msi file in a folder.

Updates to be made in System

Create a folder in MongoDB folder data/db manually.
Create a folder "log"  inside MongoDB folder manually to store the logs.
Commands 

Step-1
Now open a command prompt as an administrator and then run the following commands. By running these commands your mongoDBserver will start and logs will get saved in mongod.log file:

cd\

cd C:\MongoDB\bin

mongod --dbpath I:\Servers\data  --logpath I:\Servers\logs\mongod.log

Here I:\Servers\data:you have to specified your db path and log path .

Step-2

Now to run the MongoShell or client open second command prompt normally and run the following command:

c:\MongoDB\bin\mongo.exe


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

 

R integration with PDI

Hey Guys,

Here i would like to explain the How to integrated R with PDI .

Steps:

• Install R 3.0.1
• Install rJava package along with REngine
• Set Environment variables for R:
  • R_HOME -  C:\Program Files\R\R-3.0.1
  • R_LIBS_USER - C:\Program Files\R\R-3.0.1\library
  • Path - C:\Program Files\R\R-3.0.1\bin\i386

• Install pentaho-r-plugin in /data-integration/plugins/steps folder  (find plugin in attachments)
• Copy /rJava/jri/i386/jri.dll file (for 32 bit system) to /data-integration/libswt/win32
• Restart PDI
• In statistics step, you can find R script Executor step.

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

Sumit Bansal

BI Developer

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

Pentaho Schema workbench and Saiku Analytics

Mondrian

Mondrian is an OLAP engine written in Java. It executes queries written in the MDX language, reading data from a relational database (RDBMS), and presents the results in a multidimensional format via a Java API

• It is a designer interface that allows you to create and test Mondrian OLAP cube schemas visually.
• The Mondrian engine processes MDX requests with the ROLAP (Relational OLAP) schemas.
• These schema files are XML metadata models that are created in a specific structure used by the Mondrian engine.
• These XML models can be considered cube-like structures which utilize existing FACT and DIMENSION tables found in your RDBMS.
• It does not require that an actual physical cube is built or maintained; only that the metadata model is created.

PSW:

If you want to create your schemas as per your requirement then you need to use PSW

Feature of PSW:

• High performance, interactive analysis of large or small volumes of information
• Dimensional exploration of data, for example analyzing sales by product line..so on
• High-speed queries through the use of aggregate tables in the RDBMS
• Advanced calculations using the calculation expressions of the MDX language

Saiku Analytics

Saiku has a much lightweight architecture on the client side than our sample application and the plugin, so it can be deployed and embedded virtually anywhere.

Saiku Analytics for pentaho is a web-based Ad-hoc reporting tools.

It’s a plug-in for pentaho. You can download and install from Penthao Market Place in Penthao Server.

Features of Saiku Analytics

• Drag & Drop Report-Design
• Export to: PDF,CSV,XLS,CDA,PRPT
• Uses Pentaho Report Designer PRPT-Templates
• Grouping
• Aggregation
• Performance tuning
• OpenFormula Support
• Charts: new chart types (heatgrid, area, dot and others) , improved settings, upgrade to ccc2, improved result, basic chart export

 Sumit Bansal

 BI Developer