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.

Parse Hierarchy(TreeView) XML using Outer Apply

/*

This sample will help you to parse Tree View XMl of Explicit format.
If you Apply Cross Apply on for Outer Apply then it will not display the child which does not have 3 Levl

If you store this XML in to temp table then you can update the Parent child clasification in to table.

*/
-- Please Replace [ and ] by < and >
DECLARE @HierarchyDetail XML

SET @HierarchyDetail =
'[treeview]


[treeview-nodes]

[treeview-node node-text="Account -1001" node-value="1001"]

[treeview-nodes]

[treeview-node node-text="Account -1075" node-value="1075"]

[treeview-nodes]

[treeview-node node-text="Account - 1132" node-value="1132" /]

[/treeview-nodes]

[/treeview-node]

[/treeview-nodes]

[/treeview-node]

[treeview-node node-text="Account - 1000" node-value="1000"]

[treeview-nodes]

[treeview-node node-text="Account -1077" node-value="1077"]

[treeview-nodes]

[treeview-node node-text="Account -1134" node-value="1134" /]

[/treeview-nodes]

[/treeview-node]

[/treeview-nodes]

[/treeview-node]

[treeview-node node-text="Account - 1080" node-value="1080"]

[treeview-nodes]

[treeview-node node-text="Account - 1101 " node-value="1101"]

[treeview-nodes]

[treeview-node node-text="Account - 1135" node-value="1135" /]

[/treeview-nodes]

[/treeview-node]

[/treeview-nodes]

[/treeview-node]

[treeview-node node-text="Account - 1114" node-value="1114"]

[treeview-nodes]

[treeview-node node-text="Account - 1124 " node-value="1124"]

[treeview-nodes]

[treeview-node node-text="Account - 1137" node-value="1137" /]

[treeview-node node-text="Account - 2937 " node-value="2937" /]

[/treeview-nodes]

[/treeview-node]

[/treeview-nodes]

[/treeview-node]

[/treeview-nodes]

[/treeview]'

Select Rows from Table1 NOT IN Table2 (t1.a = t2.a and t1.b = t2.b)

--Source(Table1 & Table2)

--Expected OutPut

DECLARE @T1 TABLE(A INT, B INT,C VARCHAR(10))
DECLARE @T2 TABLE(A INT, B INT,C VARCHAR(10))
INSERT INTO @T1(A,B,C)
VALUES (1,1,'SS1')
,(1,2,'SS2')
,(1,3,'SS3')


INSERT INTO @T2(A,B,C)
VALUES (1,1,'SS1')
,(1,2,'SS2')


--Method1:
SELECT A,B
FROM @T1
EXCEPT
SELECT A,B
FROM @T2
--Method2:
SELECT A.A,A.B
FROM @T1 A
left JOIN @T2 B ON A.A = B.A
AND a.B = b.B
WHERE b.B is null
--Method3:
SELECT * FROM @T1 AS T1
WHERE NOT EXISTS (SELECT 0
FROM @T2 AS T2
WHERE T2.A = T1.A
AND T2.B = T1.B
)
 
/*
 
I hope these will give the expected Result, Now i am looking over this which is best?
 
*/

String Concatenation Using XML Path

/*
Description : String concatenation using XML Path
This will help you to concatenate the reference table column values by using cross apply
*/
DECLARE @Table1 TABLE(CountryID INT,CountryName VARCHAR(50))
DECLARE @Table2 TABLE(StateID INT IDENTITY(1,1)
                                 ,CountryID INT
                                 ,StateName VARCHAR(50))

INSERT INTO @Table1(CountryID,CountryName) VALUES
(1 , 'USA'),
(2 , 'INDIA'),
(3 , 'AUSTRALIA')


INSERT INTO @Table2(CountryID,StateName) VALUES
(1,'Texas'),
(1,'Washington'),
(1,'New Yark'),
(1,'Colorodo'),
(2,'TamilNadu'),
(2,'Mumbai'),
(2,'Delhi'),
(2,'Banglore'),
(3,'Victoria'),
(3,'Tasmania'),
(3,'Queensland'),
(3,'New South Wales'),
(3,'Western Australia')


SELECT Table1.CountryID
,Table1.CountryName
,LEFT(StateNames,LEN(StateNames)- 1) AS StateNames
FROM @Table1 as Table1
CROSS APPLY
( SELECT StateName + ', '
FROM @Table2 AS Table2
WHERE Table2.CountryID = Table1.CountryID
FOR XML PATH('') ) AS State(StateNames)

--Desired Result

--* This Insert Statement will work in 2008 *

Parent - Child Hierarchy in SQL Server 2008 Reporting Service with Operational Source

Parent >> Child Hierarchy in SQL Server 2008 Reporting Service with Operational Source


I followed the below articles and i could build the Parent child Hierarchy in SSRS 2008 using Relational Database Source.

If you are ok with this look and feel then you can follow this.










http://www.mssqltips.com/tip.asp?tip=1939

http://blogs.msdn.com/b/robertbruckner/archive/2008/10/14/using-analysis-services-parent-child-hierarchies-in-reports.aspx


There are 4 Steps need to be done to do the SSRS 2008 Parent Child Hierarcchy report with ODS Source
Step 1: Create Datasource Connection  and Dataset

Datasource: [AdventureWorksDW]

SELECT DimEmployee.EmployeeKey

,DimEmployee.ParentEmployeeKey

,DimEmployee.FirstName

,ISNULL(SUM(SalesAmountQuota),0) AS SalesAmount

FROM DimEmployee

LEFT JOIN FactSalesQuota ON FactSalesQuota.EmployeeKey = DimEmployee.EmployeeKey

GROUP BY DimEmployee.EmployeeKey

,DimEmployee.ParentEmployeeKey

,FirstName

ORDER BY 4 DESC

How to debug SSIS Script task breakpoints in 64bit Environment ?

Please look at the Faruk Celik Blog.This will hep you.

I had the same proplem in SSIS 2008 64 bit Environment. Scrript task does not get in to Break Point. I over come this issue by following the bellow article. Thanks Faruk Celik.

http://blogs.msdn.com/farukcelik/archive/2010/03/17/why-the-breakpoints-that-i-set-in-my-script-task-not-script-component-in-the-data-flow-never-hits.aspx

 Thanks

Export Table Row in to CSV File

Step1: Create Procedure with Select Statement

Create Procedure pcget_ETLErrorlog
AS
BEGIN
   SELECT * FROM ETLErrorlog
END

Step 2:
DECLARE @SQL VARCHAR(2000)
DECLARE @ExportResult INT


SET @SQL = 'bcp "EXEC [MyDatabase].[dbo].pcget_ETLErrorlog" queryout "C:\Configuration V1\Global Alert\ETLAlert.CSV" -c -t, -T -S ServerName'
EXEC master..xp_cmdshell @SQL

Note: Procedure can be replaced directly by Select Statment "SELECT * FROM ETLErrorlog"
This will return the list of Output rows. If we dont want the out put rows then we can run the above query like this...


EXEC @ExportResult = master..xp_cmdshell @SQL,NO_OUTPUT
SELECT @ExportResult

Note: 
If the OutPut Result is 0 then It means no error. This will help you to validate and proceed the next step.

Log on through windows authentication to the Different Domain SQL Server

1. Connect SQL Server Management Studio from Remote

Run the following script from PowerShell

runas /netonly /user:CIT\Sam “C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"
Then type the domain account Password.(Password will not be visible while enter) Now you can able to login to the DatabaseServer

2. Connect Microsoft Visual Studio from Remote:

runas /netonly /user:CIT\Sam "C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe"

SSIS Expression

Please go through this. This will help to write expression in SSIS Package.
http://www.pragmaticworks.com/cheatsheet/

 

 

 

1. A) Create String Variable "SourcePath" for the Source Folder and Assign Value C:\DatFiles\Pending
   

   B) Select Script Component from Transformation Control and choose the "Source Option" and
   

   Edit Script component and set Read Only Variable like here.
   

   
   

   
    

   
    

2. Create OutPut Columns and set the datatype to hold the Out put rows
   

3 a) Read File Names from Folder using VB.Net or C#.NET

public
override
void CreateNewOutputRows()

    {

 

 

string folderPath = "";

// Asign Package Variable Path to local Variable

folderPath = Variables.SourcePath;

DirectoryInfo dir = new
DirectoryInfo(folderPath);

if(dir.Exists)

{

if (dir.GetFiles().Length > 0)

{

FileInfo[] dirFiles = dir.GetFiles();

foreach (FileInfo fiInfo in dirFiles)

{

SourceFilesBuffer.AddRow();

SourceFilesBuffer.FileName = fiInfo.Name;

SourceFilesBuffer.CreatedDate = fiInfo.CreationTime;

}

}

}

 

b) Write OutPut in to OLEDB Table like step 1. B)

 

1. A) Create Source File and Pending Files Variables for Load Current Filename and Hold All Filenames Respectively
   

   b) Select Source Filenames from OLEDB Table and Assign Rows into PendingFiles Object Variable
   

   Using Execute SQL Task Editor
   

   Please refer the URL….
   

   
    

 

 

1. Read Pending Files from collection Object and Assign Current File Name in to SourceFile Variable
   

 

 

 

1. Assign Variable Name in to Source File Connection
   

   Note: If you assign only the Variable Name in the Connection String then it will work in Development Environment. It won't work in Production. So specify the File Path With \\
   

   
   

   
    

   
    

   
    

   I Hope this will Help you to Read Files from folder Using Script component Source and do the looping over the files using For Each Loop.
   

   
    

Read Files From Folder

 A) Create String Variable “SourcePath” for the Source Folder and Assign Value C:\DatFiles\Pending

B) Select Script Component from Transformation Control and choose the “Source Option” and

Edit Script component and set Read Only Variable like here.

1.       Create OutPut Columns and set the datatype to hold the Out put rows

3    a) Read File Names from Folder using VB.Net or C#.NET

public override void CreateNewOutputRows()

      {

               

        string folderPath = "";

        // Asign Package Variable Path to local Variable

        folderPath = Variables.SourcePath;

        DirectoryInfo dir = new DirectoryInfo(folderPath);

        if(dir.Exists)

        {

            if (dir.GetFiles().Length > 0)

            {

                FileInfo[] dirFiles = dir.GetFiles();

                foreach (FileInfo fiInfo in dirFiles)

                {

                    SourceFilesBuffer.AddRow();

                    SourceFilesBuffer.FileName = fiInfo.Name;

                    SourceFilesBuffer.CreatedDate = fiInfo.CreationTime;

                }

            }           

        }    

b) Write OutPut in to OLEDB Table like step 1. B)

4.       A) Create Source File and Pending Files Variables for Load Current Filename  and Hold All Filenames Respectively

b) Select Source Filenames from OLEDB Table and Assign Rows into PendingFiles Object Variable

Using Execute SQL Task Editor

Please refer the URL….

5.       Read Pending Files from collection Object and Assign Current File Name in to SourceFile Variable

6.       Assign Variable Name in to Source File Connection

Note: If you assign only the Variable Name in the Connection String then it will work in Development Environment. It won’t work in Production. So specify the File Path With \\

I Hope this will Help you to Read Files from folder Using Script component Source and do the looping over the files using For Each Loop.

---

MDX - Top 10 and Others

WITH

SET [top 10] AS

TopCount
(

[Product].[Product Name].[Product Name].MEMBERS
,10

,[Measures].[Spend]

)

// MEMBER [Product].[Product Name].[Others] AS

// Aggregate([Product].[Product Name].[Product Name].MEMBERS - [top 10])

// OR



MEMBER [Product].[Product Name].[Others] AS

Aggregate(EXCEPT([Product].[Product Name].[Product Name].MEMBERS,[top 10]))





SET [Top 10 AND rollup] AS

{

[top 10]

,[Product].[Product Name].[Others]

}

SELECT

{[Measures].[Spend]

,[Measures].[Spend Ratio Over Product]

} ON COLUMNS
,{[Top 10 AND rollup]} ON ROWS [FactPurchase]

FROM

SQL OVER() - Perform SQl Aggregate With out Group by

Use Aggregate Function With out Group by:
SQL Over() Function:

The real use of this function is
1. Find Sales ratio of each month sales out of the current year
2. Find Sales ratio of each product sales out of all product
3. Find Sales ratio of each division sales out of all division
Example
1. (SUM(Sales) OVER (PARTITION BY Year,Month) / SUM(Sales) OVER (PARTITION BY Year)) *100
2. (SUM(Sales) OVER (PARTITION BY product) / SUM(Sales) OVER ()) *100
3. (SUM(Sales) OVER (PARTITION BY division) / SUM(Sales) OVER ()) *100

--Example1:

DECLARE @FactSale TABLE(ID INT IDENTITY(1,1),ProductName VARCHAR(50),Amount INT)
INSERT INTO @FactSale(ProductName,Amount)
SELECT 'Samsung',40
UNION
SELECT 'LG',60
UNION
SELECT 'Sony',100

SELECT ProductName
,Amount
,SUM(Amount) OVER () TotalSales
,(CAST(SUM(Amount) OVER (PARTITION BY ProductName) AS NUMERIC(10,2)) / CAST(SUM(Amount) OVER () AS NUMERIC(10,2))) * 100 AS SalesRatiobyProduct
FROM @FactSale
--Example2:
DECLARE @FactSale1 TABLE(ID INT IDENTITY(1,1),Year INT,Month VARCHAR(10),Amount INT)
INSERT INTO @FactSale1(Year,Month,Amount)
SELECT 2008,'Jan',600
UNION
SELECT 2008,'Feb',700
UNION
SELECT 2008,'Mar',200
UNION
SELECT 2008,'Apr',500
UNION
SELECT 2009,'Jan',40
UNION
SELECT 2009,'Feb',60
union
SELECT 2009,'Mar',70
UNION
SELECT 2009,'Apr',30


SELECT Year
,Month
,Amount
,SUM(Amount) OVER () AS TotalSales
,SUM(Amount) OVER (PARTITION BY Year) AS TotalSalesbyYear
,(CAST(SUM(Amount) OVER (PARTITION BY Year,Month) AS NUMERIC(10,2)) / CAST(SUM(Amount) OVER (PARTITION BY Year) AS NUMERIC(10,2))) * 100 AS SalesRatiobyMonth
FROM @FactSale1

Please go through this for more information

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/ddcef3a6-0341-43e0-ae73-630484b7b398.htm