Liyasker - Business Inteligence and Database

Handle SSIS lookup failure on Date Dimension Date column NULL value

2011-09-08T01:01:00.000-07:00

Handle Lookup failure when look up null value in Date dimension.

Problem:

Lookup fail when look up null value in Date dimension.

I have date Dimension table with one row for Null value. In my OLTP Order transaction data some of the date data type column has null value.

I am getting the lookup key not found error and those rows are redirected into reject table. Actually both null was not matched up and not able to found the NULL value dimension key.

Solution:

So in this blog I have explained the customized date look up script with the following screen shots..

Note(Alternate solution):

I don’t want to replace the OLTP date column NULL value with some default date (like 1/1/1900). If I do so in the reporting layer I have to replace the default date (1/1/1900) into NULL. Otherwise this will be misleading in the report also I feel this approach is overhead.

Database & SSIS : SQL Server 2008

Table        ColumnName                            DataType
Date_D    Date(lkp column)                        Date
Order       ShippingDate(Source column)     Date

select date_Key, date from [dbo].[Date_D]

select * from (select date_Key, isnull(date,'1/1/1900') as date from [dbo].[Date_D]) [refTable]

where [refTable].[date] = ISNULL(?,'1/1/1900')

I am not sure about the performamce of Date dimenison look up. I am working on this...Since its a date dimension volume is not the consideration.

I hope this would be helpful to all.

Create Measure for DateTime DataType in SSAS

2010-09-29T05:31:00.000-07:00

*** 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

2010-09-23T08:28:00.000-07:00

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

2010-09-10T03:40:00.000-07:00

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

2010-09-09T04:10:00.000-07:00

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

2010-08-27T10:15:00.000-07:00

/*

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 Level1.value('@node-value','VARCHAR(200)') AS Level1ID

,Level1.value('@node-text','VARCHAR(200)') AS Level1AName

,Level2.value('@node-value','VARCHAR(200)') AS Level2ID

,Level2.value('@node-text','VARCHAR(200)') AS Level2Name

,Level3.value('@node-value','VARCHAR(200)') AS Level3ID

,Level3.value('@node-text','VARCHAR(200)') AS Level3Name

FROM @HierarchyDetail.nodes('/treeview/treeview-nodes/treeview-node') AS Accounts(Level1) -- I Level Nodes

OUTER APPLY Level1.nodes('treeview-nodes/treeview-node') AS Level2Items(Level2) -- II Level Nodes

OUTER APPLY Level2.nodes('treeview-nodes/treeview-node') AS Level3Items(Level3) -- III Level Nodes

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

2010-07-22T07:35:00.000-07:00

--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

2010-07-14T04:21:00.000-07:00

/*
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

2010-06-21T05:59:00.000-07:00

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

Step 2: Configure Recursive Parent

1. General Tab Configure the Prroperties

Name : ParentChildGroup
Then Add Group Expression and Specify the
[Group On] : EmployeeKey

2. Advance Tab
Recursive Parent : ParentEmployeeKey
Document Map : Leave it

3: Visiblity Tab
3.a)Check the Hide Check Box
3.b) Enable Display can be Toggle by this Item and
Select EmploeeName (Name of the First column in our report)

Step 3: Configure Indent
Right click EmployeeName Test Box and Select "Text Box Property"

1. Alignement Tab

Padding Option : Left (Expression)
= 20 * level() + 5 & "pt"

2. Font Tab

Style : Bold (Expression)
=iif(Count(Fields!FirstName.Value , "ParentChildGroup", Recursive) > 1, "Bold", "Normal")

Step 4: Set Roll up(Drill Down and Dril Up) for the SalesAmount

Set Recursive Sum in the "Sales Amount" Column

1. Select Sales Amount column Text Box Property

2. General Tab
Value : Expression

=sum(Fields!SalesAmount.Value,"ParentChildGroup",Recursive)

Thats it Enjoy SQl 2008 Parent Child Hierarchy Reporting :)

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

2010-05-19T05:23:00.000-07:00

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

2010-04-15T05:48:00.000-07:00

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

2010-03-31T07:01:00.000-07:00

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

2010-03-03T02:21:00.000-08:00

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

2010-02-10T04:16:00.001-08:00

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.
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)

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….

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

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

2010-02-10T03:55:00.000-08:00

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

2010-02-01T01:59:00.000-08:00

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

2010-01-13T04:57:00.000-08:00

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

SSAS NamedSet for FiscalTime and CalendarTime

2009-11-03T06:51:00.000-08:00

Here i have described some the useful Fiscal and Calendar NamedSet

Last Years
Last Quartes
Last Months
Current Month
LastDays
Current Fiscal Months
Fiscal Months
Last Calendar Month

Calendar Time Namedset
1. Last Years:
ORDER(
LASTPERIODS(3, StrToMember("
[Date].[Time].[Year].&["
+ Format(Now(),"yyyy")
+"]"
))
,[Date].[Time].CURRENTMEMBER.PROPERTIES("ID", TYPED), BDESC)

2. Last Quarters

ORDER(
LASTPERIODS(4, StrToMember("[Date].[Time].[Year].&["
+ Format(Now(), "yyyy")
+ "].&["
+ Format(Now(), "yyyy")
+ "]&["
+ cstr(datepart( "q", Now()))
+ "]"))
,[Date].[Time].CURRENTMEMBER.PROPERTIES("ID", TYPED), BDESC)

3.LastMonthsORDER(
LASTPERIODS(6, StrToMember("
[Date].[Time].[Year].&["
+ Format(Now(),"yyyy")
+"].&["
+ Format(Now(), "yyyy")
+"]&["
+ cstr(datepart("q", Now()))
+"].&["
+ Format(Now(), "yyyy")
+"]&["
+ Format(Now(),"MM")
+"]"
))
,[Date].[Time].CURRENTMEMBER.PROPERTIES("ID", TYPED), BDESC)

4.Current Month:

StrToMember("
[Date].[Time].[Year].&["
+ Format(Now(),"yyyy")
+"].&["
+ Format(Now(), "yyyy")
+"]&["
+ cstr(datepart("q", Now()))
+"].&["
+ Format(Now(), "yyyy")
+"]&["
+ Format(Now(),"MM")
+"]"
)

5. LastDays

ORDER(
LASTPERIODS(30, StrToMember("
[Date].[Time].[Year].&["
+ Format(Now(), "yyyy")
+ "].&["
+ Format(Now(), "yyyy")
+ "]&["
+ cstr(datepart( "q", Now()))
+ "].&["
+ Format(Now(), "yyyy")
+ "]&["
+ Format(Now(), "MM")
+ "].&["
+ Format(Now(), "dd")
+ "]"
))
,[Date].[Time].CURRENTMEMBER.PROPERTIES("ID", TYPED), BDESC)

6.Current Fiscal Months

ORDER(
StrToMember("[Date].[Fiscal Time].[Fiscal Year].&[" +
CASE WHEN CINT(Format(Now(),"MM")) >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "].&[" +
CASE WHEN CINT(Format(Now(),"MM")) >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "]&[1].&[" +
CASE WHEN CINT(Format(Now(),"MM")) >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "]&[1]")
:
StrToMember("[Date].[Fiscal Time].[Fiscal Year].&[" +
CASE WHEN CINT(Format(Now(),"MM")) >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "].&[" +
CASE WHEN CINT(Format(Now(),"MM")) >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "]&[" +
CASE WHEN CINT(Format(Now(),"MM")) >= 4 THEN CSTR(CINT(DATEPART("q", Now())) - 1)
ELSE CSTR(CINT(DATEPART("q", Now())) + 3) END + "].&[" +
CASE WHEN CINT(Format(Now(),"MM")) >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "]&[" +
CASE WHEN Format(Now(),"MM") >= 4 THEN CSTR(CINT(Format(Now(),"MM")) - 3)
ELSE CSTR(CINT(Format(Now(),"MM")) + 9) END + "]"
)
,[Date].[Fiscal Time].CURRENTMEMBER.PROPERTIES("ID", TYPED), DESC)

7. Fiscal Months
ORDER(
LASTPERIODS(3, StrToMember("[Date].[Fiscal Time].[Fiscal Year].&[" +
CASE WHEN CINT(Format(Now(),"MM")) >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "].&[" +
CASE WHEN CINT(Format(Now(),"MM")) >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "]&[" +
CASE WHEN CINT(Format(Now(),"MM")) >= 4 THEN CSTR(CINT(DATEPART("q", Now())) - 1)
ELSE CSTR(CINT(DATEPART("q", Now())) + 3) END + "].&[" +
CASE WHEN CINT(Format(Now(),"MM")) >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "]&[" +
CASE WHEN Format(Now(),"MM") >= 4 THEN CSTR(CINT(Format(Now(),"MM")) - 3)
ELSE CSTR(CINT(Format(Now(),"MM")) + 9) END + "]"
)),[Date].[Fiscal Time].CURRENTMEMBER.PROPERTIES("ID", TYPED), DESC)

8. Last Calendar Month

STRTOMEMBER("

[Date].[Time].[Year].&["

+ Format(Now(),"yyyy")

+"].&["

+ Format(Now(), "yyyy")

+"]&["

+ cstr(datepart("q", Now()))

+"].&["

+ Format(Now(), "yyyy")

+"]&["

+ Format(Now(),"MM")

+"].PREVMEMBER"

)

PPS Error Msg : Unable to connect to Remote server

2009-10-09T00:55:00.000-07:00

I got the follwing err Message after i did the PPS Instalation.
"
PPS - Error Code - 3008 Message : A configuration error has occurred( Unable to connect to Remote server)
"
Detail Error Msg
Event code: 3008 Event message: A configuration error has occurred. Event time: 10/8/2009 12:09:23 PM Event time (UTC): 10/8/2009 7:09:23 PM Event ID: 6a954f77e2cf4aa1a756c50f9cfeb8ef Event sequence: 1 Event occurrence: 1 Event detail code: 0 Application information: Application domain: /LM/W3SVC/774163112/Root/WebService-9-128995025636718750 Trust level: Full Application Virtual Path: /WebService Application Path: C:\Program Files\Microsoft Office PerformancePoint Server\3.0\Monitoring\PPSMonitoring_1\WebService\ Machine name: 25070_2_165763 Process information: Process ID: 4200 Process name: w3wp.exe Account name: NT AUTHORITY\NETWORK SERVICE Exception information: Exception type: HttpException Exception message: Could not load file or assembly 'System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The system cannot find the file specified. (C:\Program Files\Microsoft Office PerformancePoint Server\3.0\Monitoring\PPSMonitoring_1\WebService\web.config line 25) Request information: Request URL: http://74.3.234.9:40000/WebService/PmService.asmx Request path: /WebService/PmService.asmx User host address: 74.3.234.9 User: Is authenticated: False Authentication Type: Thread account name: NT AUTHORITY\NETWORK SERVICE Thread information: Thread ID: 1 Thread account name: NT AUTHORITY\NETWORK SERVICE Is impersonating: False Stack trace: at System.Web.Compilation.BuildManager.ReportTopLevelCompilationException() at System.Web.Compilation.BuildManager.EnsureTopLevelFilesCompiled() at System.Web.Hosting.HostingEnvironment.Initialize(ApplicationManager appManager, IApplicationHost appHost, IConfigMapPathFactory configMapPathFactory, HostingEnvironmentParameters hostingParameters) Custom event details:
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
"

I refered the following link it solve my issue.

Block Question:
http://74.125.153.132/search?q=cache:onAi3XCJ0ocJ:social.technet.microsoft.com/forums/en-US/ppsmonitoringandanalytics/thread/c83614c0-547f-4243-b6c5-1c04629c9b14/+PPS+%2B+Event+code+3008+%2B+Event+message:+A+configuration+error+has+occurred.&cd=2&hl=en&ct=clnk&gl=in

Answer:
http://blogs.msdn.com/fooshen/archive/2008/10/18/performance-point-server-2007-and-ajax-on-sharepoint.aspx

Unable to connect to SQL Server 2005 Analysis Services from Dashboard Designer

2009-10-09T00:45:00.000-07:00

I got the folowing error Msg in Dashboard Designer after i did the PPS Instalalation.
"
The PerformancePoint Server could not connect to the specified data source. Verify that either the current user or application pool user has Read permissions to the data source, depending on your security configuration. Also verify that all required connection information is provided and correct."
Please refer this link for more detail. It solve my issue.
http://74.125.153.132/search?q=cache:thQqD6WhI7AJ:social.technet.microsoft.com/Forums/en-US/ppsmonitoringandanalytics/thread/a1633bfb-6dad-49de-9a8e-0d130e586f10+PPS+could+not+connect+to+Sql+server+Analysis+Server+Database&cd=1&hl=en&ct=clnk&gl=in

Answer:
If everything is installed on the same server then you could setup these local accounts that you have running the Application Pools to have the proper read permissions to the SSAS database by adding them into a Role with Read Definition and Read-only access to the cube(s). If you want to pass your credentials then you need to go into the web.config files in the Web Service, Preview, and SharePoint directories and modify the Bpm.ServerConnectionPerUser value in the AppSettings to True so that the credentials of the current logged in user will be passed to the database.If you are in a distributed environment then you will have to look at configuring Kerberos, Configuring Kerberos Delegation with PerformancePoint Monitoring Server, or using the CustomData option outilined here - PPS Data Connection Security with CustomData.

MDX CrossJoin Rows and Columns

2009-05-20T08:32:00.000-07:00

This Sample will help you to understand How to applay Cross Join for Dimension meber( Rows and Columns)

Measures:
Payment Amt, Curr Payment

Rows : Vendor Name, Vendor Group are Rows
Columns : Month Name.

To Find Grand Total for Each Group :

[Vw Payment Details].[Vend Details].[Vend Details] .Members
[Vw Payment Details].[VENDGROUP].[VENDGROUP].Members
This Will give the value for each VendorName ,Vendor Group and Grand Total of Each Vendor Group.
Note ** But It will not give the [Grand Total] All the Vendor Groups

To Find Grand Total for ALL Group

1. Create Named Members
MEMBER [Vw Payment Details].[Vend Details].[Grand Total]ASAGGREGATE([Vw Payment Details].[Vend Details].[Vend Details] )MEMBER [Vw Payment Details].[VENDGROUP].[Grand Total]ASAGGREGATE([Vw Payment Details].[VENDGROUP].[VENDGROUP] )

2. Call the Member Inside the Query

({[Vw Payment Details].[Vend Details].[Grand Total]}
,{[Vw Payment Details].[VENDGROUP].[Grand Total]})

WITH
MEMBER [Vw Payment Details].[Vend Details].[Grand Total]
AS
AGGREGATE([Vw Payment Details].[Vend Details].[Vend Details] )
MEMBER [Vw Payment Details].[VENDGROUP].[Grand Total]
AS
AGGREGATE([Vw Payment Details].[VENDGROUP].[VENDGROUP] )
SELECT
CROSSJOIN(
{ [Time].[Fiscal Period].[Year].&[2009]&[2009 - 2010].&[2]&[May]
, [Time].[Fiscal Period].[Year].&[2009]&[2009 - 2010].&[1]&[April] }
, { [Measures].[Payment Amt], [Measures].[Cur Payment Amount] }
)
ON COLUMNS
,
{
CROSSJOIN({[Vw Payment Details].[Vend Details].[Vend Details] AS [Vendor Name] }
,{[Vw Payment Details].[VENDGROUP].[VENDGROUP] AS [Vendor Group]}
)
,(
{[Vw Payment Details].[Vend Details].[Grand Total]}
,{[Vw Payment Details].[VENDGROUP].[Grand Total]}
)
}
ON ROWS
FROM [Axapta Payment Details]

SSAS MDX Query Last Transaction Date , Count Payment Due

2009-05-15T05:45:00.000-07:00

This Post will help you to Find the Payment Due of Vendors.
Here the Idea is Find the Last Transaction date and Last Date of Seleted Period Then subtract between this.
This is done using -[Measure].[Day Count] Row Count of DimDate.Date column
And i explained some different behaviour of this.

1. Find LastDate Over Period(Selected Time Period)
This Measure is Derived from Date Dimension Table.
Ex. Select Year 2009 in filter and select some Months in column then
Result is :
January : February :March
31/1/2009 ;28/2/2009 ;31/3/2009

TAIL(EXISTING [Date].[Date].[Date].MEMBERS).ITEM(0).Name

2. Last Transaction Date Over Period

Ex: Selected Month is Jan then
(Date >= Jan - 1 - 09 and Date <= Jan - 31 -09)

TAIL(NonEmpty(EXISTING [Date].[Date].MEMBERS, [Measures].[Amount])).ITEM(0).NAME

3. Last Transaction Date From Current Period to Previous Periods
Ex: Selected Month is Jan then (Date <= Jan - 31 -09)
Find the Transaction Date Between
1. All the Previous Periods : NULL
2. Tail Member : TAIL(EXISTING [Date].[Date].[Date].MEMBERS).ITEM(0)
3. Look up Measure Between this Date and get the Tail Member

TAIL(
FILTER(
(NULL : TAIL(EXISTING [Date].[Date].[Date].MEMBERS).ITEM(0))
, NOT ISEMPTY([Measures].[Amount])
)
).ITEM(0).Name

4. Count Payment Due
Count Date Between Last day of Each Period to Last Transaction Date of Previous Period to the Current Period
Step 1:
First Find the [Measures].[DayCount]
[Measures].[DayCount] :- Count Rowl Measure from DimDate.Date Column
Step2:

I).From : Last Date of Period (Jan - 31 -09)
II). To: LastTransactionDate(NULL : (all previous Member) and Date <= Jan - 31 -09)

III). Sum of DayCount Between the Period

SUM(
{ TAIL(EXISTING [Date].[Date].[Date].MEMBERS).ITEM(0)
:
TAIL(
FILTER(
(NULL : TAIL(EXISTING [Date].[Date].[Date].MEMBERS).ITEM(0))
, NOT ISEMPTY([Measures].[Amount])
)
).ITEM(0)
}
,[Measures].[DayCount]
)

5. Count Payment Due from Last Transaction Date To Last Date of current Period
Ex:
1.From : Last Date of Period (Jan - 31 -09)
2. To: LastTransactionDate(Date >= Jan - 1 - 09 and Date <= Jan - 31 -09)
3. Sum of DayCount Between the Period

SUM(
{ TAIL(EXISTING [Date].[Date].[Date].MEMBERS).ITEM(0)
:
TAIL(NonEmpty(EXISTING [Date].[Date].MEMBERS
, [Measures].[Amount])).ITEM(0)
}
,[Measures].[DayCount]
)

****************
Sample Query
****************

WITH
MEMBER [Measures].[PaymentDue Previous Period] AS
SUM(
{ TAIL(EXISTING [Date].[Date].[Date].MEMBERS).ITEM(0)
:
TAIL(
FILTER(
(NULL : TAIL(EXISTING [Date].[Date].[Date].MEMBERS).ITEM(0))
, NOT ISEMPTY([Measures].[Amount])
)
).ITEM(0)
}
,[Measures].[DayCount]
)
MEMBER [Measures].[PaymentDue Over Period] AS
SUM(
{ TAIL(EXISTING [Date].[Date].[Date].MEMBERS).ITEM(0)
:
TAIL(NonEmpty(EXISTING [Date].[Date].MEMBERS
, [Measures].[Amount])).ITEM(0)
}
,[Measures].[DayCount]
)

SELECT {[Measures].[PaymentDue Previous Period]
,[Measures].[PaymentDue Over Period]
}
ON ROWS,
[Date].[Month Name].MEMBERS
ON COLUMNS
FROM [Vendor Ledger]
WHERE ([Date].[Year].&[2008],[Vendor].[Vendor].&[10006])

In this example [Measures].[PaymentDue Over Period] will fit in to real time

** Any Feeback about this Post are welcome **

Create Dynamic Time Dimension Named Set in SSAS

2009-05-15T04:14:00.000-07:00

This post will explain that How to create the Time Dimension Dynamic named set using CurrentDate .

Most of the people might have know about this. This post is for self reference and sharing the knowledge.

In My cube I have Calandar Time Hierarchy
My Hierarchy is
[Year] -> [Quarter] -> [Month] -> [Day]
Here i explained some Named Set.

1. [Last years]
2. [Last Quarter]
3. [Last Months]
4. [Last Days]

Step1: First Find the Time Dimension Hierarchy Member from your cube, and select the Hierarchy member inside your Query Window.
MemberName of Year,Quarter,Month,Day :
[Date].[Time].[Year].&[2009]
[Date].[Time].[Year].&[2009].&[2]
[Date].[Time].[Year].&[2009].&[2].&[5]
[Date].[Time].[Year].&[2009].&[2].&[5].&[15]

Now you will get this query.

Step2:
Frame the string member as it is in the step1. then create the Name set by using [New Named Set] in the cube Calculation Tab.

The following link will help you to derive DateParts from Current Date.
http://www.ssas-info.com/analysis-services-faq/27-mdx/78-how-write-mdx-query-that-uses-execution-datetime-now-as-parameter

1. [Last Years]

LASTPERIODS(3, StrToMember("[Date].[Time].[Year].&["
+ Format(Now(), "yyyy")
+ "]"))

2.[Last Quarter]

LASTPERIODS(4, StrToMember("[Date].[Time].[Year].&["
+ Format(Now(), "yyyy")
+ "].&["
+ cstr(datepart( "q", Now()))
+ "]"))

3. [Last Month]

LASTPERIODS(6, StrToMember("[Date].[Time].[Year].&["
+ Format(Now(), "yyyy")
+ "].&["
+ cstr(datepart( "q", Now()))
+ "].&["
+ Format(Now(), "MM")
+ "]"))

4.[Last Day]

LASTPERIODS(30, StrToMember("[Date].[Time].[Year].&["
+ Format(Now(), "yyyy")
+ "].&["
+ cstr(datepart( "q", Now()))
+ "].&["
+ Format(Now(), "MM")
+ "].&["
+ Format(Now(), "dd")
+ "]"))

here you can define the lastperiod as your wish.

** Any feedback about this Article are Welcome **

Parsing different type of Column Delimited in SSIS FlatFile Data Source.

2009-04-21T21:39:00.000-07:00

I followed this Article to achive my requirement. Very nice and Interesting.
Parsing the diferent type of Column Delimited(Comma, tab,Colon, SemiColon) in SSIS for the Flat File Source.
http://www.sql-server-performance.com/articles/dba/import_text_files_ssis_p1.aspx

Create Named Set for Dimension Member in MDX.

2009-02-12T03:07:00.000-08:00

WITH
MEMBER [Dim Project].[Project Type].[T&M & FixedBid] AS '[Dim Project].[Project Type].&[FIXED BID] + [Dim Project].[Project Type].&[T & M PROJECTS]'
MEMBER [Measures].[NonBillable Hours] AS
([Measures].[Duration In Hours] ,([Dim BillableType].[Billable Type].&[BILL]
,[DimBillable].[Billable].&[0],[T&M & FixedBid]))
SELECT {
[Measures].[NonBillable Hours]
}
ON ROWS,
[Dim Employee].[Employee Name] ON COLUMNS
FROM [Resource Utilization]

SSAS Cube Processing Using .Net CLR StoredProcedure

2009-01-18T22:57:00.000-08:00

There are 3 ways to Process the SSAS 2005 Cube:
1. AMO(Analysis Management Objects) with .Net CLR Procedure
2. Power Shell Script
3. SSIS ETL Package.
Here i explained my Experience with

[1.] AMO(Analysis Management Objects) with .Net CLR Procedure this is More flexible and easy to handle Cube Objects.

Step 1.Enable CLR in SqlDataBase

SP_CONFIGURE 'CLR ENABLED',1 GO RECONFIGURE

Step2. Create New .Net DataBase SqlServerProject(CLR Project) with your Required DataBase connection

Step3: Create Microsoft.AnalysisServices.DLL" Reference in [Cube Processing] Project

1. Find "Microsoft.AnalysisServices.DLL" in C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\ and copy paste to some Folder D:\CLR Project\

2. Register Assembly in SQLServer DataBase

CREATE ASSEMBLY [AnalysisServices]
FROM D:\CLR Project\Microsoft.AnalysisServices.DLL'
WITH PERMISSION_SET = UNSAFE

3. Refer [AnalysisServices] Assembly

Right click [Cube Processing] Project and click Add Reference and Select "Sql Server Tab" select

[AnalysisServices] Assebly.

Note1. Assembly will be avaliable in "SqlServer Tab" only after Creating Assembly in Datbase .
as well as it can be seen in SQLManagementStudio -> Database->Programmabillity -> Assembly

Step 4: Create Method ProcessPartition Using Microsoft.AnalysisServices Classs

/*
Process the Cube Partition using given Input Parameter Values. If ProcessDimensions is True then
process all MeasureGroup Related Dimension
-- Simple Code and Commented Briefly
*/

1. Copy Paste the code

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using Microsoft.AnalysisServices;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void ProcessPartition(SqlString ServerName, SqlString CatalogName, SqlString CubeName, SqlString MeasureGroupName, SqlString PartitionName, SqlBoolean ProcessDimensions)
{
try
{
// Create Instance for AnalysisServer
Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server();
// Create Instance for MeasureGroup
MeasureGroup measureGroup = new MeasureGroup();
// Connect Analysis Server Instance
svr.Connect(ServerName.ToString());
if ((svr != null) && (svr.Connected))
{
// Connect the Analysis Server DataBase
Microsoft.AnalysisServices.Database db = svr.Databases.FindByName(CatalogName.ToString());
Cube cub;
// Find the Analysis Server DataBase Cube
cub = db.Cubes.FindByName(CubeName.ToString());
// Find the Analysis Server DataBase Cube MeasureGroup
measureGroup = cub.MeasureGroups.FindByName(MeasureGroupName.ToString());
// Check ProcessDimensions Flag If its True Process All MeasureGroup Related Dimesnion
if (ProcessDimensions == true)
{
foreach (MeasureGroupDimension Dim in measureGroup.Dimensions)
{
// Proces Dimension
Dim.Dimension.Process();
}
}
// Find the Analysis Server DataBase Cube MeasureGroup Partttion
Partition par = measureGroup.Partitions.FindByName(PartitionName.ToString());
// Process Partition
par.Process();
}
// DisConnect Server Connection
svr.Disconnect();
}
catch (Exception Ex)
{
// To Send Error Message to Database
SqlContext.Pipe.Send(Ex.Message);
throw new ApplicationException(Ex.Message);
}
}
};

/*
This code will process the Cube up to low Level object. If you wish to Process only the DataBase,Cube,MeasureGroup then
use DataBase.Process(),Cube.Process(),MeasureGroup.Process() .
*/

2. Build the Project
3. Copy the [Cube Processing] DLL from Solution \bin\Debug folder and Paste to D:\CLR Project\
4. Create Assembly in SqlServer DataBase:

CREATE ASSEMBLY [Cube Processing]
FROM 'D:\CLR Project\Cube Processing.dll'
WITH PERMISSION_SET = UNSAFE

5. View the Assembly
Now the Registered [Cube Processing] Assembly wl be abaliable in SQLManagementStudio -> Database->Programmabillity -> Assembly

Step5: Create Extented StoreProcedure Using Registed Assembly (Cube Processing])

CREATE PROCEDURE [dbo].[ProcessPartition]
(@ServerName NVARCHAR(50)
, @CatalogName NVARCHAR(50)
, @CubeName NVARCHAR(50)
, @MeasureGroupName NVARCHAR(50)
, @PartitionName NVARCHAR(50)
, @ProcessDimensions BIT
)
AS
EXTERNAL NAME [Cube Processing].[StoredProcedures].[ProcessPartition]

[Cube Processing] -->Registered Assembly Name
[StoredProcedures] --> Class Name of the above Code
[ProcessPartition] --> Method Name of he Class

Step6: Execute Procedure [dbo].[ProcessPartition]

EXEC [ProcessPartition] @ServerName = 'MyServer\Instance1'
, @CatalogName = 'MyDatabase'
, @CubeName = 'MyCube'
, @MeasureGroupName = 'MyCube'
, @PartitionName = 'MyCube_2008_08'
, @ProcessDimensions = 1

!! Any help and sugestion about this article are welcome !!!

Unexpected error occurred while Open CalculationTab in SSAS Cube

2009-01-18T22:35:00.000-08:00

In Business Intelligence Development Studio (BIDS), when i am in Analysis Services Project and when i open a cube and select the calculations tab, the design surface displays a message that reads:
Unexpected error occurred: 'Error in the application' and then it closes Visual Studio.

Found Mary Potapova's answer in the Microsoft Forum very useful as it fixed my problems. I quote here fix:
this might be caused by version mismatch between some binaries. i'd suggest checking the versions of msmdlocal.dll and msmgdsrv.dll and making sure that the ones installed into %ProgramFiles%\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ are the same as those in %ProgramFiles%\Common Files\System\Ole DB\ (if they are not then try updating the ones in the PrivateAssemblies with the ones from ole db; you might want to save the original ones as to be able to get back to original state just in case.. It helped me to fix this issue.

SSAS Time Dimension Design

2009-01-11T20:57:00.000-08:00

Read Data from RecordSet using Sequence Container

2008-11-27T04:08:00.000-08:00

http://www.sqlis.com/post/Shredding-a-Recordset.aspx