Thursday, December 23, 2010
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]"
[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]"
[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]
[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]"
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:
4. Load staging fact table.
5. Config 'Load Max ODS Date' task as shown below:
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:
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.
"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
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.
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
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
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
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
Subscribe to:
Posts (Atom)