Thursday, December 23, 2010

Create and use Named Set in MDX Query

How to use named set in MDX Query:

MDX Named set to get Last 3 Quarters

How to get last 3 Quarters from hierarchy with data structure "
[Date].[Calendar Year].[Year].&[2011].&[Quarter - 1]"

MDX Named Set to Get Last 3 Months

How to get last 3 months from hierarchy with structure "
[Date].[Calendar Year].[Year].&[2007].&[Quarter - 3].&[2007]&[8]"

MDX Named set to get Last 3 Years

How to get last three years from hierarchy "" with structure " [Date].[Calendar Year].[Year].&[2007]"
[Date].[Calendar Year].[Year]

MDX Named Set to Get Current Month

How to write named set for hierarchy "[Date].[Calendar Year].[Month]" with
structure "
[Date].[Calendar Year].[Year].&[2008].&[Quarter - 2].&[2008]&[5]"

Tuesday, December 21, 2010

SSIS Incremental Load using Package History

Incremental Load using package History

1. Create Package History table in Warehouse:

CREATE TABLE [dbo].[PackageHistory](
[PackageHistoryId] [int] IDENTITY(1,1) NOT NULL,
[PackageName] [varchar](50) NOT NULL,
[RunDateTime] [smalldatetime] NOT NULL,
[SourceDateTime] [smalldatetime] NOT NULL,
CONSTRAINT [PackageHistory_PK] PRIMARY KEY CLUSTERED
(
[PackageHistoryId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

2. Create the variables as shown below:

3. Design ETL Package as shown below:

3. Config 'Load Max ETL Date' task as shown below:

4. Load staging fact table.

5. Config 'Load Max ODS Date' task as shown below:

6. Create tasks to Load fact table. Select data whose modified/createddate > Max(RundateTime) in package history.

7. truncate Staging table.

8. Config 'Insert Package History' task as shown below:

8. When the package is run, the data whose created/modified dates are greater than Max(RundateTime) in package history is loaded in to warehouse.

Monday, December 20, 2010

Schedule Pentaho Jobs

1. Create a batch file with the following codes:

"D:\Downloads\Pentaho Tool\Data Integration 4.0.1 (Spoon)\kitchen.bat" /file:"D:\My Work Place\Project\Telecount\DataLoad\Job_DailyLoadScript.kjb" /level:Basic

i.e., You have to mention the path where Kitchen.bat file present in your system as well as job name and its location.

2. Navigate to Programs --> accessories --> System tools --> Scheduled Tasks and call the batch file and schedule the time it has to process.

Hash Value in SSIS

Hash value can be cretaed using Check Sum and we can use it as a unique identifier. Using Hashvalue in ETL package to create Slowly Changing Dimension and incremental load by mapping hashvalue in source with hashvalue in destination will improve ETL package performance.

SELECT TrackId, CASE WHEN ProjectID = 706 THEN 'CSS' ELSE 'CIT' END AS Location, UserId, Duration, Description, Datee AS Dated, ModifiedDate, BINARY_CHECKSUM(TrackId, ProjectId, UserId, Duration, Datee) AS HashvalueFROM Timesheet.dbo.timetrack

Thursday, December 9, 2010

Changing Background Color in Pentaho Dashboard

To replace background of pentaho perform the following steps:

1. Create a CSS file defiining color for background, font size, font type as per your requirement:
e.g.,: Create CSS file with below code to change the background color of dashboard and save it as
"aquamarine" in "BI Server
(biserver-ce-3.6.0-stable)\biserver-ce\pentaho-solutions\system\pentaho-cdf\resources\style"

      body { background-color:aquamarine ;
     font-family:Verdana, Arial, Helvetica, sans-serif;
            font-size:12px;
            color:black;
            }
2. Create a template for your dashboard in "BI Server
(biserver-ce-3.6.0-stable)\biserver-ce\pentaho-solutions\system\pentaho-cdf" with the name
"template-dashboard-aquamarine".
IMPORTANT 1: The template name must be in same format "template-dashbaord-<CSSFilename>"
IMPORTANT 2: INside the template refer your style sheet in link tag as sjown below:
<head>
        <meta http-equiv="content-type" content="text/html; charset=utf-8" />
        <title>CIT Dashboard Framework</title>
  <meta name="keywords" content="" />
  <meta name="description" content="" />
  <link rel="stylesheet" type="text/css" href="resources/style/aquamarine.css"/>
</head>
3. Open the XCDF file present in your Dashboard solution. Refer the css file name in the tag <style>
<?xml version="1.0" encoding="UTF-8"?>
<cdf>
  <title>Performance Overview</title>
  <author>Webdetails</author>
  <description>Pentaho Sample</description>
  <icon></icon>
  <template>template.html</template>
  <style>aquamarine</style>
</cdf>
4. Upade your repository in http://locolhost:8080/pentaho/Admin
5. Refresh and open your dashboard the background color will be in Aquamarine.

Creating JNDI Connection in Pentaho

Creating JNDI connection for SQL in Pentaho Admin Console:

1. Create a ODBC Connection in your server.
2. Run Pentaho Admin Console and navigate to Database connection
3. In Driver Class select "com.microsoft.sqlserver.jdbc.SqlServerDriver"
4. IN URL enter "jdbc:odbc:<ODBC DB Name>"

NOTE: For SQL Server Connectivity download "sqljdbc" file and place it in "BI Server (biserver-ce-3.6.0-stable)\administration-console\jdbc"
Creating JNDI connection for MYSQL in Pentaho Admin Console:

Driver Class Name: com.mysql.jdbc.Driver
URL: jdbc:mysql://localhost:3307/foodmart?user=user_name&password=user_password

Pentaho Top 6 Files and Uses

1. Web.xml:
    LOCATION: BI Server (biserver-ce-3.6.0-stable)\biserver-ce\tomcat\webapps\pentaho\WEB-INF
    USE: To change the default port number abd IP address
2. PentahoLog:
    LOCATION: BI Server (biserver-ce-3.6.0-stable)\biserver-ce\tomcat\bin
    USE: The file contains Log details of BI Server

3. publisher_config:
   LOCATION: BI Server (biserver-ce-3.6.0-stable)\biserver-ce\pentaho-solutions\system
   USE: User can config the password required to publish reports and cubes

4.loginsettings :
   LOCATION: BI Server (biserver-ce-3.6.0-stable)\biserver-ce\tomcat\webapps\pentaho\mantleLogin
   USE: Default User list are configured in this file

5. Login properties:
    LOCATION: BI Server (biserver-ce-3.6.0-stable)\administration-console\resource\config
    USE: Admin user for Pentaho Admin Console is configured in this file

6. datasources :
    LOCATION: BI Server (biserver-ce-3.6.0-stable)\biserver-ce\pentaho-solutions\system\olap
    USE: This file contains details of datasource used for designing Cubes

Sample SQL Scalar Function and it Use

The below function is created to calculate the count of a column whose status is 'Past Due'.

E.g, dbo.ProcessStatus

How to use this function in query?

SELECTProcessID,[dbo].[udf_CalcBCLifeCycle_PastdueRating](BIAScheduledCalculation,BIACaptureCalculation,BIASignoffCalculation) AS PastDueFROM BCLIfeCycleWHERE BCLifecycle.Isdeleted = 0
Output

Tuesday, November 30, 2010

List the indexes in SQL Server

Execute the below query to list th enumber of indexes used in your Sql server:
SELECT * FROM sys.dm_db_missing_index_details
--Where Database_ID = .....

By executing the above query you can find the number of columns used in the index.

Change Background color in SSRS - Switch Syntax

Switch Syntax to change color in SSRS report

=Switch(Fields!Performance.Value >= 80, "Green", Fields!Performance.Value >= 60, "Amber", Fields!Performance.Value < 60, "Red")

Friday, November 26, 2010

Creating Parameters in Pentaho Dashboard using SQL Query

We can parse SQl query in an Xaction file to create a parameter in Pentaho dashboard.
1. Create an xaction file like below:

<?xml version="1.0" encoding="UTF-8"?>
<action-sequence>
<name>ListOperator.xaction</name>
<title>%title</title>
<version>1</version>
<logging-level>debug</logging-level>
<documentation>
    <author>Mahadevan</author>
    <description>%description</description>
    <help/>
    <result-type>rule</result-type>
    <icon>SQL_Datasource.png</icon>
</documentation>

<inputs>
      <FROM type="string">
      <sources>
        <request>FROM</request>
      </sources>
    </FROM>
    <TO type="string">
      <sources>
        <request>TO</request>
      </sources>
    </TO>
</inputs>



<outputs>
    <rule-result type="result-set"/>
</outputs>

<resources/>

<actions>
    <action-definition>
      <component-name>SQLLookupRule</component-name>
      <action-type>Query For Operator Info</action-type>
     <action-inputs>
        <FROM type="string"/>
        <TO type="string"/>
       </action-inputs>
      <action-outputs>
        <query-result type="result-set" mapping="rule-result"/>
      </action-outputs>
      <component-definition>
        <jndi>Telecount</jndi>
        <query><![CDATA[select count(smskey) AS total, Operator AS Operators from vw_telecount
                            WHERE Createddate >= ('{FROM}') AND Createddate <=('{TO}')
                            GROUP BY Operator]]></query>
      </component-definition>
    </action-definition>

</actions>
</action-sequence>

Step 2: Create an object for parameter in dashboard template as shown below:
ListOperators =
{
name: "ListOperators",
type: "selectMulti",
solution: "PentahoDemo",
path: "Dashboards/FilterA/List",
action: "ListOperators.xaction",
listeners:["startDate","endDate"],
parameters: [["FROM","startDate"],["TO","endDate"]],
parameter:"operators",
htmlObject: "ListOperators_Object",
size: "4",
executeAtStart: true,
preExecution:function(){},
postExecution:function(){}
}

3. Call the object in your Dashbord template.

<td valign = "top"    style="width: 5%; text-align: left;"><span
                        style="font-family: Arial;">Operators:</span><br>
                </td>
                <td valign = "top" style="width: 20%; text-align: left;">
                <div valign = "top" id="ListOperators_Object"></div>

4. Update repository and open your dashboard, you can see the parameter.

Thanks
Mahadevan

Thursday, November 25, 2010

Prerequesites for Pentaho

Pre-requesites for Pentaho Dashboard and Flash Charts

1. Before installing Pentaho in your system, install Java Run Time Environment 6 or higher version.
2. Install Flash Player to view flash charts.
3. If you are developing some open flash charts in your dashboard and charts then download open-flash-chart-full-embedded-font.swf file and place it in the following location "\BI Server (biserver-ce-3.6.0-stable)\biserver-ce\pentaho-solutions\system\pentaho-cdf\".
4. For better view use Mozilla Firefox browser.

Regards
Mahadevan

To change the Default Port Number of Pentaho Server

To change the Default Port Number of Pentaho Server, navigate to web.xml file in the following path:
\BI Server
(biserver-ce-3.6.0-stable)\biserver-ce\tomcat\webapps\pentaho\WEB-INF

Open Web.xml file and change the port number in highlighted text, providing IP address will allow user to share the pentaho works with other machine in same domain:

<context-param>
        <param-name>base-url</param-name>
        <param-value>http://192.168.60.195:8080/pentaho/</param-value>
    </context-param>

Mail Sending in SQL server

1. Mail Send from SQL

USE [Timesheet09-Oct-2009]

/****** Object: StoredProcedure [dbo].[pcWarantyStartDatePreAlert] Script Date: 08/04/2010 09:21:05 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER PROC [dbo].[pcWarantyStartDatePreAlert]

-- @PONumber VARCHAR(100),

-- @TimeIntervalId INT

BEGIN

BEGIN TRY

IF EXISTS( SELECT 0 FROM [Timesheet09-Oct-2009].[dbo].[Projects] WHERE CONVERT(CHAR(10), WarrantyStartDate, 101) = CONVERT(CHAR(10),GETDATE(),101))

BEGIN

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'WarrantyStartDateAlerts',

@recipients = 'mahadevan.v@congruentindia.com;',

@copy_recipients = 'SET NOCOUNT ON

SELECT EmailAddress From [User]

JOIN (SELECT ResponsiablePersonID, PROJECTID FROM Projects

WHERE CONVERT(CHAR(10), WarrantyStartDate, 101) = CONVERT(CHAR(10),GETDATE(),101))

ProjectRes ON [user].USerID = ProjectRes.ResponsiablePersonID

SET NOCOUNT OFF',

@query = 'SET NOCOUNT ON

SELECT [ProjectName]

,[RegionMaster].[RegionName]

,[Status]

,[User].Firstname

,[WarrantyStartDate]

,[WarrantyDays]

,[WarrantyEndDate]

FROM [Timesheet09-Oct-2009].[dbo].[Projects]

LEFT JOIN [Timesheet09-Oct-2009].[dbo].[RegionMaster] ON Projects.[RegionId] = RegionMaster.RegionID

LEFT JOIN [Timesheet09-Oct-2009].[dbo].[USER] ON Projects.[ResponsiablePersonID] = [USER].UserID

WHERE CONVERT(CHAR(10), WarrantyStartDate, 101) = CONVERT(CHAR(10),GETDATE(),101)

SET NOCOUNT OFF',

@subject = 'The Warranty date for the project listed starts from tomorrow'

END

END TRY

BEGIN CATCH

DECLARE @Error VARCHAR(1000)

SET @Error = ERROR_MESSAGE()

RAISERROR(@Error, 16, 1)

END CATCH

END

Mail Send from SQL Format 2

USE [Timesheet09-Oct-2009]

/****** Object: StoredProcedure [dbo].[pcWarantyStartDateAlert] Script Date: 08/04/2010 10:58:52 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER PROC [dbo].[pcWarantyStartDateAlert]

BEGIN

SET NOCOUNT ON;

DECLARE @tableHTML NVARCHAR(MAX)

DECLARE @LeadMailID NVARCHAR(MAX)

DECLARE Mail_Cursor CURSOR FOR

SELECT EmailAddress From [User]

JOIN (SELECT ResponsiablePersonID, PROJECTID FROM Projects

WHERE CONVERT(CHAR(10), WarrantyStartDate, 101) = CONVERT(CHAR(10),GETDATE(),101))

ProjectRes ON [user].USerID = ProjectRes.ResponsiablePersonID

OPEN Mail_Cursor

FETCH NEXT FROM Mail_Cursor

INTO @LeadMailID

WHILE @@FETCH_STATUS = 0

BEGIN

SET @LeadMailID = REPLACE(@LeadMailID,',',';')

FETCH NEXT FROM Mail_Cursor

INTO @LeadMailID

END

CLOSE Mail_Cursor

DEALLOCATE Mail_Cursor

SET @tableHTML =

N'<H1>Project Waranty Start Date Alert</H1>' +

N'<table border="1">' +

N'<tr><th>SNo</th><th>Projecdt Name</th><th>Region Name</th>' +

N'<th>Status</th><th>Responsible Person</th><th>Waranty Start Date</th>' +

N'<th>Warranty Days</th><th>Warranty End Date</th></tr>' +

CAST ( ( SELECT td = ROW_NUMBER() OVER (ORDER BY [ProjectName]), ''

,td = [ProjectName], ''

,td = [RegionMaster].[RegionName],''

,td =[Status], ''

,td =[User].Firstname, ''

,td =[WarrantyStartDate], ''

,td =[WarrantyDays], ''

,td =[WarrantyEndDate], ''

FROM [Timesheet09-Oct-2009].[dbo].[Projects]

LEFT JOIN [Timesheet09-Oct-2009].[dbo].[RegionMaster] ON Projects.[RegionId] = RegionMaster.RegionID

LEFT JOIN [Timesheet09-Oct-2009].[dbo].[USER] ON Projects.[ResponsiablePersonID] = [USER].UserID

WHERE CONVERT(CHAR(10), WarrantyStartDate, 101) = CONVERT(CHAR(10),GETDATE(),101)

FOR XML PATH('tr'), TYPE

) AS NVARCHAR(MAX) ) +

N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail

@subject = 'The Warranty date for the project listed starts Today',

@profile_name = 'WarrantyStartDateAlert2',

@recipients = @LeadMailID,

@copy_recipients = 'mahadevan.v@congruentindia.com',

@body = @tableHTML,

@body_format = 'HTML' ;

SET NOCOUNT OFF;

END

<!--

USE [BIServer]

/****** Object: StoredProcedure [dbo].[sp_BIServerHealthCheckMail] Script Date: 11/25/2010 15:01:50 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER PROC [dbo].[sp_BIServerHealthCheckMail]

BEGIN

SET NOCOUNT ON;

DECLARE @tableHTML NVARCHAR(MAX)

DECLARE @PingCount INT

SELECT @Pingcount = Count(*) from PingServer

SET @tableHTML =

N'<H1>CIT Server Health Check</H1>' +

N'<table border="1">' +

N'<tr><th>SNo</th><th>SYSTEM NAME</th>' +

N'<th>PING RESULT</th>' +

N'<th>SERVER STATUS</th>' +

N'<th>PROCESSED DATE</th></tr>' +

CAST ( ( SELECT td = ROW_NUMBER() OVER (ORDER BY Ping DESC, PingServer.[CITServer]), ''

,td = PingServer.[CITServer], ''

,td = PING,''

,td = CASE WHEN PING like '%Request timed out%' THEN 'Connection Failed'

ELSE 'Connection Succeeded' END, ''

,td =GETDATE(), ''

FROM [BIServer].[dbo].[PingServer]

JOIN (SELECT [CITServer],MAX(SERID) AS SIDS

FROM [BIServer].[dbo].[PingServer]

GROUP BY [CITServer]

) SERPING ON PingServer.SERID = SERPING.SIDS

ORDER BY PING DESC, PingServer.[CITServer]

FOR XML PATH('tr'), TYPE

) AS NVARCHAR(MAX) ) +

N'</table>' ;

IF (@PingCount >0)

BEGIN

EXEC msdb.dbo.sp_send_dbmail

@subject = 'CIT SERVER HEALTH CHECK',

@profile_name = 'BIServerHealthCheck',

@recipients = 'sathishkumarg@congruentindia.com;Chalapathi.M@congruentindia.com;Satheeshkumar.m@congruentindia.com',

@copy_recipients = 'vinod.s@congruentindia.com;mahadevan.v@congruentindia.com',

@body = @tableHTML,

@body_format = 'HTML' ;

END

SET NOCOUNT OFF;

END

Mahadevan BI Consultant

Labels

Thursday, December 23, 2010

Create and use Named Set in MDX Query

MDX Named set to get Last 3 Quarters

MDX Named Set to Get Last 3 Months

MDX Named set to get Last 3 Years

MDX Named Set to Get Current Month

Tuesday, December 21, 2010

SSIS Incremental Load using Package History

Monday, December 20, 2010

Schedule Pentaho Jobs

Hash Value in SSIS

Thursday, December 9, 2010

Changing Background Color in Pentaho Dashboard

Creating JNDI Connection in Pentaho

Pentaho Top 6 Files and Uses

Sample SQL Scalar Function and it Use

Tuesday, November 30, 2010

List the indexes in SQL Server

Change Background color in SSRS - Switch Syntax

Friday, November 26, 2010

Creating Parameters in Pentaho Dashboard using SQL Query

Thursday, November 25, 2010

Prerequesites for Pentaho

To change the Default Port Number of Pentaho Server

Mail Sending in SQL server

1. Mail Send from SQL

Mail Send from SQL Format 2

Total Pageviews

Labels

Thursday, December 23, 2010

Tuesday, December 21, 2010

Monday, December 20, 2010

Thursday, December 9, 2010

Tuesday, November 30, 2010

Friday, November 26, 2010

Thursday, November 25, 2010

1. Mail Send from SQL

Mail Send from SQL Format 2

Total Pageviews

Subscribe To