Create Measure for DateTime DataType in SSAS

*** Self reference entry by Liyasker **
There is a requirement to create the Measure for User InTime Hrs, OutTime Hrs, CheckInTime, CheckOutTime, AVGCheckInTime with HH:MM AM/PM format.

But SSAS measure will support only for Integer and Numeric. Our case I have to expose the cube measure in to MS EXCEL 2007. There we can’t do any conversion.

I got the right help from the following blog of John (Thanks John :)). Please go through this, this will help you.

http://sqlblogcasts.com/blogs/drjohn/archive/2007/12/03/ssas-working-with-date-and-time-measures-to-calculate-average-elapse-time.aspx

I create a Facttable view and did the float conversion like below
,CONVERT(FLOAT,CAST(InTime_InHrs AS DateTime)) + 2 AS OfficeInTime
,CONVERT(FLOAT,CAST(OutTime_InHrs AS DateTime)) + 2 AS OfficeOutTime
,CONVERT(FLOAT,CheckInDateTime) + 2 AS InDateTime
,CONVERT(FLOAT,CheckOutDateTime) + 2 AS OutDateTime

Then I apply the Time format in SSAS Measure Properties.

I hope this will help to calculate DateTime Measure in SSAS.
 
 
 
 
 
 
 
 
 
 

Pentaho Transformation Deployment

How to deploy Pentaho Transformation(Database Connection)from Developement to Staging and Production?
Please look at this, I hope you can do this.
http://wiki.pentaho.com/display/EAI/Beginners+FAQ

I was looking more than 2 weeks to to find the right resource for the deployment :)

Pentaho - Fact Incremental Load

Pentaho - Fact Incremental Load

1. Create MapingHistory table to maintain the ETLRun Date


2. Load the MaxDate from Source and LastETLRunDate from MapingHistory Table(Destination).
A)Select MaxSource and Use the proper date convertion
Select job => SetVariables Transformation to hold the "MaxSource" date Value

3. Do the same for get the MAX ETL Date

4. Create Mapping to load FactData

a) Substitutevariable in the Source Script and choose "Replace Variable in script"
b) Use "Execute SQl Script" Tranfromation under the Scripting component 
and Select Variable Substitution option

5. Create job to Integrate the GetVariable and FactIncrementalLoad Transformation

Pentaho Looping

Pentaho Looping

I have a scenario to load the staging table from different sources. So i have to execute the same mapping more the one times based on number of rows avaliable in the Control Table.

Table : ConnectionInfo
ServerName      DatabaseName
Server1              DB1
Server2              DB2
Server2              DB3

I followed this URL and i could implement the Pentaho Looping
http://www.youtube.com/watch?v=rEG5yU8oLgI

Step1:"LoadConnections.ktr"

Create Transformation to select number of rows(Databasename,serverName) and copy the rows in to result set

Step2: "LoadConnectionInfo.ktr"

Create Transformation to Assign connection Information to variable 

Step3:"Load StgEntity.ktr"

a) Create Static Source Connection to load the data from source.

b) Create mapping to transfer the data from Source to Destination

c) Then change the connection name in to Variable.

Step4) "LoadVariable and ExecuteTransformation.kjb"

Create  the job to Integrate the (Step2)-LoadConnectionInfo.ktr and (Step3) - LoadStgEntity.ktr

Step5: Create job to integrate  Step1 - "LoadConnections.ktr"  and Step4 - "LoadVariable and ExecuteTransformation.kjb"

And configure the job properties like below

Here (Step4 - "LoadVariable and ExecuteTransformation.kjb")  job will be executed by the number of rows return from the (Step1- "LoadConnections.ktr")  transaction

I hope this will hellp you to do the pentahoo looping.