Business Intelligence

Step to FX world

2011-04-12T12:55:00.003+06:00

Today, I had a chance to spend some time with FX trading room. It's quite interesting. Few of the FX traders shared with me some training materials. Just going through one by one. really interesting...

New Bee SEASON

2009-07-23T02:09:00.002+07:00

Did you watch this movie" BEE SEASON "http://www.imdb.com/title/tt0387059/. it's absolutely nice one especially the ending part, it's awesome!!!

so my Bee season in IBM started...got into a project of Global Treasury Department of IBM. we are recreating our legacy report into cognos one... dont know when should i get into Cognos TM1( where really good things will happen).

Not much people around.. knowledgeable collegues.. even a IBM fiancial analyst can write a good code too...so there is good competition... i'm enjoying it...

so far so good..

a small Bee in blue ocean

2009-06-26T04:48:00.002+07:00

I'm just writing a post after a looong time...

I quit SAIC and joined with IBM. now enjoying bench period. Also frustrated about relocation threat to kolkotta.

KANDISA

2008-10-10T20:27:00.005+06:00

My friend suggest me to listen indian ocean(Indian band)KANDISA album, i found it really intersting!!!

I really liked that because my grandfather is belongs to Caldean Syrian(http://www.nestorian.org/).

Kandisa means "praise" in Aramaic, the language spoken by Jesus Christ.

In 2000, the acclaimed music band Indian Ocean released their album titled Kandisa.

The title track is an ancient prayer in Aramaic, from the south of India - where St Thomas is believed to have brought the new Christian faith in the 1st century AD.

Aalam Balam Aalam, Aamenu Aamen
Sliha Mar Yose, Almaduba Kudisa
Aangen Dhanusa, Nehave Dukharana
Kandisa Alaha, Kandisa Esana, Kandisa La Ma Yosa Isaraha Malem

New Place of Work

2008-08-12T20:42:00.001+06:00

I joined with Science Applications International Corporation as Sr.Cognos Engineer.
www.saic.com.

SQL Server Business Intelligence Training @ Microsoft

2008-06-07T18:02:00.002+06:00

Last 3 days I got training from Microsoft Bangalore R&D lab. That was awesome experience. Day 1 started from concepts of Business intelligence and dimensional modeling and then Day 2 was about SQL server Integration Services ETL engine and Reporting Services and final day about SQL server analysis services (OLAP) and data mining technologies shipped with New Microsoft EXCEL and Analysis services.

I'd recommend SSIS, SSAS and New EXCEL will take a new race in BI market because it's completely free with SQL server.

The trainer is too good, his name is Mr.Vinod UNNI he's Microsoft Valuable professional. It was fantastic experience in Microsoft lab.

TDWI indian chapter meet

2008-06-02T09:44:00.002+06:00

Last weekend i attend The Data Warehousing indian chapter meeting at Infosys Technologies Bangalore.
It was very good experience for us. i could meet around 100 BI professionals over there.
Most interestingly the presentation about GRID computing using for Data management.
This presentation taken by Dr.sethil, he's heading Yahoo India GRID R&D Lab also he's the visiting professor of University of Pheonix. he talked about HADOOP opensource technology about faster data management in storage devices. and finally there was a panel discussion about the future of BI. Mr.Vaibav(Business Objects), Mr. Prabhu(GM-Wipro) gave very good insight to BI technologies.

I could meet mr. Anoop Nambiar who is heading infosys BI practice.
overall i found it's very interesting...... looking forward to next meeting....

3 TIER Business Intelligence

2008-05-20T20:44:00.004+06:00

COGNOS 3 TIER BI PLANNING

whenever you're creating meta-data model in framework manger follow this process.. i found it very useful.

Make 3 namesapces in your Framework model

first one named as data source Views(Query Subjects,Procedures, Query items)
Second Busines View(Query subjects,items based on the business users views),
and Third one is Cube(OLAP) view, it contains measure dimensions, regular dimension with hierachies, levels as well.

and then finally publish packages based on these view because it's good for performance.

Post your comments on different modeling techniques in framework manager.

Love Your Local Data Warehouse Manager

2008-04-18T01:11:00.000+06:00

Mashup

2008-04-03T14:01:00.010+06:00

Did you know Mashups?... anyway it's not mashed potato:-)
it's an amazing tool which can collaborate data items from different data sources(google maps,feeds,websites,structured and unstructured databases) etc.. and then gives you meaningful results..

And if you want to poke around this mashups try:
YAHOO PIPES
Google Mashups:

Kapow technologies is the number one leader into this area.

Further Readings:

WikiPedia

-----------------------------------------------------------------------------
*********************************************

two days back i dicussed with my manager about mashups. he gave me good insights to mashups

Hats off Mr.Vamsi, who is heading entire healthcare R&D team in bangalore.

-----------------------------

ClickStream Business Intelligence

2008-02-07T15:32:00.000+06:00

When I started IT career learning by eCommerce technologies. .Com boom/Slowdown abolished eCommerce business. But I still believe, e-business is been here and it will be back very strongly in coming years….

In my understanding; ClickStream data warehousing means gather/capture/analyze/patterning your web data for making better business decision.

Basic measures of this ClickStream data warehouse data is: how many hits? Geographical categorization? Web visits type? Web visits duration? Which page? Most downloads? Etc…

Then I found google magic. Awesome stuff for ClickStream data warehouse !!!!
Experience it: www.google.com/analytics/home

Secret of Success

2008-01-22T17:01:00.000+06:00

When it comes to tech, simplicity sells

2008-01-22T11:57:00.000+06:00

Never Gone Never Far

2008-01-21T13:31:00.000+06:00

Cheers!!!!
I'm planning to start up my blog again.....
this time i'm just making this blog exclusively for cognos 8 BI tool.
Most probably after few months, i'll be moving on to my site:-)

SQL STUDY PART 2

2007-10-25T16:58:00.000+06:00

Study Sql Query Part 1

2007-10-23T13:08:00.000+06:00

You can directly cut&Paste these following queries into Sql server 2005 and play around:-{

--Basic SQL server 2005 Queries--
--database AdventureWorksDW and a few my own dbs
-- select statement with concatenate(combine two strings into one )columns
select * from dimorgstructure
select ORGSTRUCTCODE + ' , .,,,,,,, ' +
Name as organisation from dimorgstructure

-- including where clause limits the records retrieved by select statement.
--A where clause include columns combined by keywords AND and OR
select * from dimorgstructure where name='MARUTHI COMPANY'
select * from dimorgstructure where name='MARUTHI COMPANY' or orgstructcode='0002'
or orgstructcode='ABMH'
select * from dimorgstructure where name='MARUTHI COMPANY' AND orgstructcode='0002'
select * from dimorgstructure where name='MARUTHI COMPANY' AND orgstructcode='8000'
select * from dimorgstructure where name='MARUTHI COMPANY' AND orgstructcode like '8%'
--The order by clause determines the order--
select * from dimorgstructure
order by name

-- working with the for XML clasue
-- you can use the for xml clause to return data as an xml document, when using the
--xmal clasue, you must specify the mode as RAW, AUTO or Explicit.
--With the RAW option SQL Server takes the result of the query and
--transform each row in the
--result set into an XML element with a generic identifier
--Result of RAW tag
select orgstructcode, name from dimorgstructure
order by name asc
for XML RAW
-- auto commands returns a simple nested xml tree
--it shows xml tag without raw tag
--result of Auto tag
select orgstructcode, name from dimorgstructure
order by name asc
for XML AUTO
-- Explicit
--FOR XML EXPLICIT requires at least three columns, including the tag column,
-- the parent column, and at least one data column.
--FOR XML EXPLICIT requires the first column to hold positive integers that
--represent XML tag IDs.
select balanceamount,orgstructcode
from fact_table
for XML explicit
--creating TOP values Queries
select top 5 name from dimorgstructure
--JOINS
--inner join is most common type join, when you use an inner join it
--gives you matching rows
select * from
factsadt f inner join dimemployees e
on
f.hemployeecode=e.hemployeecode

--full join
-- full join combines the behavior of the left and right outer join
select * from
factsadt f full join dimemployees e
on
f.hemployeecode=e.hemployeecode

-- group by clasue
select employeecategorycode,employeetypename, count(*) from dimemployees
group by employeecategorycode,employeetypename
order by employeecategorycode asc

--including aggregate functions in your sql statement
select count(*) from dimemployees
-- working with the count_big function
-- same like count function except it returns bigint datatype
select count_big(employeetypename) from dimemployees

--- Sum, min, max Functions
select Max(unitprice) from FactInternetSales
select min(unitprice) from FactInternetSales
select d.SalesTerritoryCountry, sum(f.unitprice) from FactInternetSales f
inner join dimsalesterritory d on
f.SalesTerritoryKey=d.SalesTerritoryKey
group by d.SalesTerritoryCountry
--HAVING clause
-- Having clause is similiar like where clause, the major difference is
-- SQL server applies having clause after it summraizes the data
-- Sql server applies Where clause before it summrizes the data
select d.SalesTerritoryCountry, sum(f.unitprice) from FactInternetSales f
inner join dimsalesterritory d on
f.SalesTerritoryKey=d.SalesTerritoryKey
group by d.SalesTerritoryCountry
having ((sum(f.unitprice))> 2644017.7143)

--Union and Union All
--All queries combined using a UNION, INTERSECT or EXCEPT operator
--must have an equal number of expressions in their target lists.
select englishproductname, productline from dimproduct
union all
select englishproductcategoryname from dimproductcategory
--will not show duplicate values if you are using UNION
select englishproductname, productline from dimproduct
union
select englishproductname, productline from dimproduct
--SUB Queries
--A subquery is a query that sql server evaluates before
--it evaluates the main query
select englishproductname, productline from dimproduct
where ProductSubcategoryKey
not in(select ProductCategoryKey from DimProductSubcategory)
------- End of Basic Queries----------------

Cognos 8 Business intelligence

2007-10-23T10:50:00.000+06:00

I'm planning to concentrate on Cognos 8 Business intelligence Tool as well as SQL server 2005 BI sets. So most of my posting would be related to this subject.

Cognos 8 BI
-------------------
Report Studio
Query Studio
Analysis Studio
Cognos Connection
Framework Manger
Event Studio and
Metric Studio

SQL server 2005
-----------------------
SQL server Database basics
SQL server Analysis Services
SQL server Integration Services
SQL server Reporting Services

Information Management Software Session at IBM software lab,Bangalore

2007-08-22T17:32:00.000+06:00

I had a wish to attend two conference/seminar which is related to BI in this year.
So i got a chance to attend IBM software session on DB2 9 series as well as their DW and BI products:-)

it statrted from PureXML concept in DB2 and then they moved to DW session on post lunch(this session was quite interesting).There is 3 IBMers took Demo on DB2 Data Warehouse Edition and IBM Reporting tools - Alphablox & DataQuant(Reporting tool).

They have lack of experience in Demo on DW and BI but somehow they managed.According to me, DB2 data warehouse is good for ETL purspose and the main advantage is(when you compared to Microsoft integration Services) good data modeling tool is availabe for creating physical design of your data warehouse, hence no need to buy Erwin etc.. for Data modeling purpose.

Alphablox is good tool, it something similiar like Viador(which we were using before) www.viador.com. and then they have good portelets based dashboard and it can connect to any kind of OLAP tools(Cubes) for creating analytical reports...

About DataQuant, it's pure eclipse based tool , which is facilitates to create web based porel and dashboards for making your BI portel much easier(you can make any customization also becos it's an IBM Eclipse is an opensource initiative i guess).

Our discussion went well after the session time also... those IBMers are very cooperative and they spend good time with us for discussing their BI project..

My comment on DB2:
As far as i know, if anybody going for DB2 as their Backend tool, they can obiviously can buy data warehousing as well as their BI tool also... it's fantastic.

they have lack of Scorecard Manager and Event Management feature for Real time BI
IBM got colloborative BI tool
and it's purely web based(Javabased) tool.
it support multi-vendor databases etc...

let me see if i could get a trial version for DB2 BI set...so i can paste more comment on that:-))

Budgets and Basic FM

2007-08-13T13:20:00.000+06:00

I’m going to prepare Budget related reports for our project; basically the hospital wants to make budget evaluation based on different cost centers as well as profit centers. Needs to create few dimensions like Cost Center, Time and Budget(Budget Actual, Budget Variance).Mr.ArunKumar helped me a lot for preparing this report also he helped me to refresh my finance golden rules:
I’ll just paste finance basics for further readings:

From Book keeping perspective there are three types of accounts:

Personal
Real
Nominal.

Personal are accounts like Customer, Supplier, ABC & Co, DoctorReal accounts are physical in nature that can bee seen. Examples: Cash, Stock, Equipment etc.Nominal accounts are accounts whose effect is logical and can be only felt. Examples: Revenue, Expenses, Profit, Loss

GOLDEN RULES
------------

Personal Accounts Debit (Dr) the Receiver Credit (Cr) the Giver

Real Account Debit (Dr) what comes in Credit (Cr) what goes out

Nominal Account Debit (Dr) Expenses and Losses Credit (Cr) Gains, Revenue and Profits

Traditional Accounting
-----------------------

Journal Book: Record the debits and credits for each transaction. (Day Book)Ledger Book: Transfer the entry from Journal to individual Ledgers. Trial Balance: Write all the accounts with individual balancesFinal Statements: Group the accounts into P&L and Balance Sheet.

Computerized Accounting

----------------------
Chart of Account: As against Traditional accounting you group the accounts before making transaction not after. Types of account: There are 27 account types which define their place in COA, Voucher and Financial Statements. Example: An account defined as type of Cash will appear in the Balance sheet and not in the P&L. Similarly the Cash account would appear only in Cash Receipts, Cash Payment and Contra Vouchers.
Voucher: Here the debits and credits similar to Journal Book are captured. The difference is that system does automatic posting to Ledgers commonly referred to as General Ledger (GL).
Financial Statements: There are nothing but reports from General Ledger.

Cost Center Accounting

----------------------
Whereas GL gives the transactions for the whole Organization, Cost Accounting emphasizes that revenue and expenditures can be traced to a sub-unit of the organization. These sub-units are referred to as Cost and Profit Centers. Whereas Cost Centers have only expenditure, Profit Centers have profits through revenue and expenditure.
For example: In a hospital the HR department does not earn any revenue; but incurs expenditure. Hence HR department is considered as a cost center. The In-patient department earns revenue and incurs expenditure. Hence IPD is considered as a Profit Center.

Basic finance Questions???

2007-08-03T15:52:00.000+06:00

The major financial statements are designed to provide of the overall financial position and performance of the business.

The accounting system will normally produce three kinds of major reports on regualar recurring basis...

Normal Questions:

What cash movements(Cash in hand and cash out) took place over a particular period?
How much wealth(profit) was generated by the business over a particular period?
what is the accumlated wealth of the business at the edn of particular period?

These three questions are addressed by the three financial statements with the statements dealing with a particular Questions. These financial statements are:

The Cash flow statement
Profit and Loss A/c
Balance sheet

i'll update you these statements further my posting;-{

MANAGEMENT ACCOUNTING

2007-08-03T13:58:00.000+06:00

Management Accounting: which seeks to meet the needs of a business manager. Management accounting reports, are often specific purpose of reports. that is they are designed with either a particular decsion in mind or for a particular manager.it provides detailed information to make particular business decision.These reports are specifically using for internal use of the business, so there is no legal implication on this, no need to follow certain standards. the reprting interval of these reports are based on management needs, provided with certain reports daily, weekly or monthly repors which all of them to check progress frequently.

Finacial accouting reports relect the performance and position of the business to date, in essence they are backward looking but management reports are conerning with past as well as future perfromance information. Forecasting etc...

Management reports are detailed level reports rather than the financial reports(must contains evidence to reports-quality is high)..

Go bck to college Days

2007-08-01T13:20:00.000+06:00

Before i need to start up our project, i'm dealing the data with Finance Module of our ERP application(called back office). i want to brush up my old accounts and management accounting books.

i really forgot the accounting once i come out from college.

planning to read few things in this week.

Double Entry books(balance sheet, P/L account, GL etc..)
Measuring and reportin financial performance(profit measurement of revenue,expenses, depreciation,stock costing methods, bad debts problems and intrepretation of p/L account)
Cash Flow and Fundflow statment(Working Capital)
Cost-Volume-Profit anaysis
Budgeting
Managing working Capital

I'll update you the formulas and annotation of theory for my further posting.

take a break from technial stuff :-)

2007 Plans

2007-07-31T16:25:00.000+06:00

I jotted down my 2007 study plans:-))

Buy a Laptop
install Cognos 8 BI suite
Oracle 9i indepth
Cognos FrameWork Manager
Sqlserver 2005 indepth
Data warehouse indepth(planning to read 5 books about DW prescribed by TDWI*)
Sql server Integration Services indepth
Dimensional Modeling indepth (read minimum 3 books)
Sql server Analysis Services studio
Cognos PowerPlay
Cognos Query studio
Cognos Report Studio
Cognos Analysis Studio
Cognos Event Studio
Cognos Metric Studion
MCTS certification
procure CBIP guide
take CBIP credential from TDWI

Apart from that we have to complete our BI project(BackOffice) before October.
*http://download.101com.com/pub/TDWI/Images/Exam%20Preparation%20Resource%20Guide-Books.pdf

A big leap to Cognos 8

2007-07-31T15:58:00.000+06:00

i took a short break becasue i was busy with these days... i'm taking cogonos 8 training from one of independent consultant mr. sunil. cognos 8 is a beautiful BI tool which has five BI studios like Query studio, report studio,Event studio, Metric studio and analysis studio apart from this FrameWork Manager and Powerplay Olap tool.

we have 5 people taking the training... i have had hands on experience in cognos 8 before but only query and report studio. i think nobody knows Event studion(Real time Business Intelligence-see my older post about Real time BI) and metric studion in bangalore. i'm planning to buy laptop next month and install event studion and metric studio, then play around it....

We havent started cogons framework manager.we just going through the basics.
And moreover I created one Yahoogroups for my friends :-) cognosguru@yahoogroups.com
we are going to share our ideas and thougths about cognos further through this group.

All of them are working: One person PM in infosys, and two of them from Target software. and one IT manager. i think it would be a good experience.

My tutor ready to help me for surrogate key creation. let's c........ tomarrow i'm going to startup dimensional modeling for our company's project Finance part...
I'll update you on that.. moreover i need to bursh through my BBA finance books basics before that...:))

Frost Again: Miles to go before i sleep...

Few more about Surrogate keys

2007-07-03T14:25:00.000+06:00

I'm going to make surrogate key of all my dimensions. few more abt surrogate keys;

The primary key for dimension tables should be a surrogate key assigned and managed by the DW/BI system. The most common method for creating a surrogate key is to use the identity property on the surrogate key column. Prefer Small Integer for as data type because it will take 2 bytes of your data, when you go for integer data type, it consumes 4 bytes.

And moreover surrogate key consumes more storage in your fact table (FK of your Dimension Table), so we can make small integer as surrogate key.

The date dimension should use a surrogate key. That surrogate key should be an integer. But it’s convenient for it to be a meaningful integer of the for year month day such as 20070703.

Surrogate Key

2007-06-29T12:43:00.000+06:00

We had a discussion with Mr.JP, Finally the team mate who agreed upon my idea to make surrogate key rather than making a Key from OLTP system.

Next Month, i put it my plan like this:
1. Dimensional Design
2.Surrogate Key Creation
3. physical Setup
5.Package Creation
6.Task creation
7.Transformation Task creation
8.Dimension Design
9.Dimension Creation
10.audit key creation
11.Fact Table Design
12.Fact Table Growth
13. Data Loading
from there i need to start up with OLAP-Cube creation

Surrogate key Creation

2007-06-19T13:38:00.000+06:00

I'm hanging with surrogate keys creation. It's the big pain are a now.. i could create surrogate keys in dimensions but connecting to the fact table is the big problem.

Yesterday i had debate with our collegues about surrogate key creation. they were telling about no need for surrogate keys for our warehouse. but i emphasise on KIMBALL method he says:

" the ability to track changes in dimension attributes over time is reason enough to implement surrogate keys. we have regretted it more than once when we decided not to track changes in attribute values over time. and later found out the historical values were important to support certain butsiness analysis. we had to go back and add surrogate keys and re create the dimensions change history. this isnot a fun project, we encourage you to do it right the first time. if you use surrogate keys for all dimensions at the outset, it's much easier to change a dimensions so that it tracks history."

after a break

2007-06-11T17:15:00.002+06:00

I done incremental loading for our data warehouse with shortest span of time. And then I need to work on different task to fulfilling our data warehouse needs,

surrogate key
Audit
cleansing the data
Slowely changing dimension

We had a discussion with one external consultant Mr.Jayaprakash, he's doing Data Warehousing and BI more than ten years:

Their project consist of 1 cube and 120 dimensions.
they dont prefer data mart level
before starting the data warehousing we need to check granualrity and make a star schema
dont prefer Snow flake schema
their cube will take complete process around 7 days
they are using SAN storage consists 3 millions of data
Quarterly they are processing cubes

it was very nice session with him.
-------------------------------------------------------------------------------

" for incremental load i need to paste some codes from andy's web site here for furture purpose:
http://vsteamsystemcentral.com/cs21/blogs/applied_business_intelligence/archive/2007/05/21/ssis-design-pattern-incremental-loads.aspx

Next we need to modify our Lookup transformation behavior. By default, the Lookup operates as an INNER JOIN - but we need a LEFT (OUTER) JOIN. Click the "Configure Error Output" button to open the "Configure Error Output" screen. On the "Lookup Output" row, change the Error column from "Fail component" to "Ignore failure". This tells the Lookup transformation "If you don't find an INNER JOIN match in the destination table for the Source table's ColID value, don't fail." - which also effectively tells the Lookup "Don't act like an INNER JOIN, behave like a LEFT JOIN":

Expand the NULL Functions folder in the upper right of the Conditional Split Transformation Editor. Expand the Columns folder in the upper left side of the Conditional Split Transformation Editor. Click in the "Output Name" column and enter "New Rows" as the name of the first output. From the NULL Functions folder, drag and drop the "ISNULL( <> )" function to the Condition column of the New Rows condition:

Next, drag Dest_ColID from the columns folder and drop it onto the "<>" text in the Condition column. "New Rows" should now be defined by the condition "ISNULL( [Dest_ColID] )". This defines the WHERE clause for new rows - setting it to "WHERE Dest_ColID Is NULL".
Type "Changed Rows" into a second Output Name column. Add the expression "(ColA != Dest_ColA) (ColB != Dest_ColB) (ColC != Dest_ColC)" to the Condition column for the Changed Rows output. This defines our WHERE clause for detecting changed rows - setting it to "WHERE ((Dest_ColA != ColA) OR (Dest_ColB != ColB) OR (Dest_ColC != ColC))". Note "" is used to convey "OR" in SSIS Expressions:

Make it: New Rows for Null expression
Make it : conditions for Changed Rows and click this condition and make it for Default Output Name: Unchanged Rows

take OLEDB command task from tranformation and make it changed Rows
take OLEDB destination from destination and make it New rows

and then

UPDATE dbo.tblDest
SETColA = ?,
ColB = ?,
ColC = ?
WHERE ColID = ?

|| The Path of Karma || || Bhagavad Gita ||

2007-05-28T17:46:00.000+06:00

Said the Supreme Lord:

"O Sinless one(Arjuna), I declared two kinds of worship in the world before. One is the path of knowledge pursued by the Sankhyas and the other, the path of action meant for men of action."
[Chapter 3 Verse 3]

"But O Arjuna, he is better who, regulating his senses by his mind, unattached, begins karma yoga with his organs of action."
[Chapter 3 Verse 7]

"Therefore do your prescribed work, for doing some work is better than doing no work at all. Without work it is not possible to even maintain the physical body."
[Chapter 3 Verse 8]

"Works in this world can cause bondage unless done with a sense of sacrifice. Therefore, O son of Kunti, perform your actions for the sake of sacrifice only, free from attachment."
[Chapter 3 Verse 9]

"In the beginning, at the time of creation, Brahma created people along with sacrifice and declared that by performing sacrifices they would become more prosperous and also that sacrifice would be their wish fulfilling Kamadhenu"
[Chapter 3 Verse 10]

Refreshing Data Warehouse

2007-05-23T13:32:00.001+06:00

i made one data warehouse for our HMIS product by SSIS tool. i got lot's of help from Mr.Andy and Mathew Mosche. i posted our discussion(MSDN forum) about Incremental load of data warehouse.
Pls find the link:

or...
------------------------------------------------------------------------------------------
from Andy..

Hi Abi,

Incremental loads are the same regardless of which database platform or ETL tool you use. I can best demonstrate it with T-SQL:

1. Assume a source named tblSource with the columns ColID, ColA, ColB, and ColC. Assume also ColID is a primary unique key.
2. Assume a Destination table named tblDest with the columns ColID, ColA, ColB, ColC.
3. An incremental load would take place in a couple steps - assuming no rows are deleted at the source; rows from the source only change or are added. (Make sense?)

4. New rows can be loaded using:
SELECT s.ColID, s.ColA, s.ColB, s.ColC
FROM tblSource s
LEFT JOIN tblDest d ON d.ColID = s.ColID
WHERE d.ColID IS NULL

5. There are many ways by which people try to isolate changed rows. The only sure-fire way to accomplish it is to compare each field:
UPDATE d
SET
d.ColA = s.ColA
,d.ColB = s.ColB
,d.ColC = s.ColC
FROM tblDest d
INNER JOIN tblSource s ON s.ColID = d.ColID
WHERE (
(d.ColA != s.ColA)
OR (d.ColB != s.ColB)
OR (d.ColC != s.ColC)
)

You can accomplish this in SSIS using the Merge Join or the Lookup (for the join functionality) combined with the Conditional Split (for the WHERE clause conditions) transformations.

I'm not sure what you mean by "refreshing cycle". My best guess is you want to schedule the load to run automatically every day (or night). You accomplish that best with a SQL Agent job.
Hope this helps,
Andy
------------------------------------------------------------------------------------------
From Mathew Mosche

There are many different tools and techniques in SSIS that can be applied to this problem, but the real question to ask is how do you identify new and changed data in the source system?

If you can reliably identify new records in the HMIS system, I would recommend an approach similar to this:
For each package execution, store the ID of the "greatest" (most recent) record being processed from HMIS. You can store it in a flat file, in the staging database (my personal preference) or wherever, as long as it is reliable.
Update your source queries to only SELECT records "greater" (more recent) than the last processed record. This will ensure that whatever data you're pulling from HMIS is data that you actually need, and you're not pulling any unneeded data.
Assuming that you cannot reliably identify new records, you can use the Lookup transform in your data flow, to see if a given record already exists, and only insert it into the target table if it does not. This keeps you from needing to truncate your target tables each time, but still incurrs the overhead of pulling all data from the source system.

What neither of these techniques addresses is modified data - what do you do if a record in the source system changes? This is a much larger issue in general, and the answer generally relies as much on the business context of the data as it does on the ETL toolset - what should you do, as opposed how do you do it?

Also, I'm not sure what you mean by "Refresh Cycle timings: is there any task available in SSIS?" Can you please rephrase or elaborate on this question?

Thanks!

----------------------------------------

from mosche...

Andy is exactly right - SSIS doesn't have any built-in scheduling tool. The most common way to schedule the execution of your SSIS packages is to use SQL Server Agent, but any scheduling utility should do the trick.

The 38 Subsystems of ETL(courtesy Ralph Kimball)

2007-05-09T18:43:00.000+06:00

To create a successful data warehouse, rely on best practices, not intuition.
Three little letters -- E,T, and L -- obscure the reality of 38 subsystems vital to successful data warehousing.
By
-->Ralph Kimball

The extract-transform-load (ETL) system, or more informally, the "back room," is often estimated to consume 70 percent of the time and effort of building a data warehouse. But there hasn't been enough careful thinking about just why the ETL system is so complex and resource intensive. Everyone understands the three letters: You get the data out of its original source location (E), you do something to it (T), and then you load it (L) into a final set of tables for the users to query.

When asked about breaking down the three big steps, many designers say, "Well, that depends." It depends on the source, it depends on funny data idiosyncrasies, it depends on the scripting languages and ETL tools available, it depends on the skills of the in-house staff, and it depends on the query and reporting tools the end users have.

The "it depends" response is dangerous because it becomes an excuse to roll your own ETL system, which in the worst-case scenario results in an undifferentiated spaghetti-mess of tables, modules, processes, scripts, triggers, alerts, and job schedules. Maybe this kind of creative design approach was appropriate a few years ago when everyone was struggling to understand the ETL task, but with the benefit of thousands of successful data warehouses, a set of best practices is ready to emerge.

I have spent the last 18 months intensively studying ETL practices and ETL products. I have identified a list of 38 subsystems that are needed in almost every data warehouse back room. That's the bad news. No wonder the ETL system takes such a large fraction of the data warehouse resources. But the good news is that if you study the list, you'll recognize almost all of them, and you'll be on the way to leveraging your experience in each of these subsystems as you build successive data warehouses.

The 38 Subsystems

1.Extract system. Source data adapters, push/pull/dribble job schedulers, filtering and sorting at the source, proprietary data format conversions, and data staging after transfer to ETL environment.

2.Change data capture system. Source log file readers, source date and sequence number filters, and CRC-based record comparison in ETL system.

3.Data profiling system. Column property analysis including discovery of inferred domains, and structure analysis including candidate foreign key — primary relationships, data rule analysis, and value rule analysis.

4.Data cleansing system. Typically a dictionary driven system for complete parsing of names and addresses of individuals and organizations, possibly also products or locations. "De-duplication" including identification and removal usually of individuals and organizations, possibly products or locations. Often uses fuzzy logic. "Surviving" using specialized data merge logic that preserves specified fields from certain sources to be the final saved versions. Maintains back references (such as natural keys) to all participating original sources.

5.Data conformer. Identification and enforcement of special conformed dimension attributes and conformed fact table measures as the basis for data integration across multiple data sources.

6.Audit dimension assembler. Assembly of metadata context surrounding each fact table load in such a way that the metadata context can be attached to the fact table as a normal dimension.

7.Quality screen handler. In line ETL tests applied systematically to all data flows checking for data quality issues. One of the feeds to the error event handler (see subsystem 8).

8.Error event handler. Comprehensive system for reporting and responding to all ETL error events. Includes branching logic to handle various classes of errors, and includes real-time monitoring of ETL data quality

9.Surrogate key creation system. Robust mechanism for producing stream of surrogate keys, independently for every dimension. Independent of database instance, able to serve distributed clients.

10. Slowly Changing Dimension (SCD) processor. Transformation logic for handling three types of time variance possible for a dimension attribute: Type 1 (overwrite), Type 2 (create new record), and Type 3 (create new field).

11. Late arriving dimension handler. Insertion and update logic for dimension changes that have been delayed in arriving at the data warehouse.

12. Fixed hierarchy dimension builder. Data validity checking and maintenance system for all forms of many-to-one hierarchies in a dimension.

13. Variable hierarchy dimension builder. Data validity checking and maintenance system for all forms of ragged hierarchies of indeterminate depth, such as organization charts, and parts explosions.

14. Multivalued dimension bridge table builder. Creation and maintenance of associative (bridge) table used to describe a many-to-many relationship between dimensions. May include weighting factors used for allocations and situational role descriptions.

15. Junk dimension builder. Creation and maintenance of dimensions consisting of miscellaneous low cardinality flags and indicators found in most production data sources.

16. Transaction grain fact table loader. System for updating transaction grain fact tables including manipulation of indexes and partitions. Normally append mode for most recent data. Uses surrogate key pipeline (see subsystem 19).

17. Periodic snapshot grain fact table loader. System for updating periodic snapshot grain fact tables including manipulation of indexes and partitions. Includes frequent overwrite strategy for incremental update of current period facts. Uses surrogate key pipeline (see subsystem 19).

18. Accumulating snapshot grain fact table loader. System for updating accumulating snapshot grain fact tables including manipulation of indexes and partitions, and updates to both dimension foreign keys and accumulating measures. Uses surrogate key pipeline (see subsystem 19).

19. Surrogate key pipeline. Pipelined, multithreaded process for replacing natural keys of incoming data with data warehouse surrogate keys.

20. Late arriving fact handler. Insertion and update logic for fact records that have been delayed in arriving at the data warehouse.

21. Aggregate builder. Creation and maintenance of physical database structures, known as aggregates, that are used in conjunction with a query-rewrite facility, to improve query performance. Includes stand-alone aggregate tables and materialized views.

22. Multidimensional cube builder. Creation and maintenance of star schema foundation for loading multidimensional (OLAP) cubes, including special preparation of dimension hierarchies as dictated by the specific cube technology.

23. Real-time partition builder. Special logic for each of the three fact table types (see subsystems 16, 17, and 18) that maintains a "hot partition" in memory containing only the data that has arrived since the last update of the static data warehouse tables.

24. Dimension manager system. Administration system for the "dimension manager" who replicates conformed dimensions from a centralized location to fact table providers. Paired with subsystem 25.

25. Fact table provider system. Administration system for the "fact table provider" who receives conformed dimensions sent by the dimension manager. Includes local key substitution, dimension version checking, and aggregate table change management.

26. Job scheduler. System for scheduling and launching all ETL jobs. Able to wait for a wide variety of system conditions including dependencies of prior jobs completing successfully. Able to post alerts.

27. Workflow monitor. Dashboard and reporting system for all job runs initiated by the Job Scheduler. Includes number of records processed, summaries of errors, and actions taken.

28. Recovery and restart system. Common system for resuming a job that has halted, or for backing out a whole job and restarting. Significant dependency on backup system (see subsystem 36).

29. Parallelizing/pipelining system. Common system for taking advantage of multiple processors, or grid computing resources, and common system for implementing streaming data flows. Highly desirable (eventually necessary) that parallelizing and pipelining be invoked automatically for any ETL process that meets certain conditions, such as not writing to the disk or waiting on a condition in the middle of the process.

30. Problem escalation system. Automatic plus manual system for raising an error condition to the appropriate level for resolution and tracking. Includes simple error log entries, operator notification, supervisor notification, and system developer notification.

31. Version control system. Consistent "snapshotting" capability for archiving and recovering all the metadata in the ETL pipeline. Check-out and check-in of all ETL modules and jobs. Source comparison capability to reveal differences between different versions.

32. Version migration system. development to test to production. Move a complete ETL pipeline implementation out of development, into test, and then into production. Interface to version control system to back out a migration. Single interface for setting connection information for entire version. Independence from database location for surrogate key generation.

33. Lineage and dependency analyzer. Display the ultimate physical sources and all subsequent transformations of any selected data element, chosen either from the middle of the ETL pipeline, or chosen on a final delivered report (lineage). Display all affected downstream data elements and final report fields affected by a potential change in any selected data element, chosen either in the middle of the ETL pipeline, or in an original source (dependency).

34. Compliance reporter. Comply with regulatory statutes to prove the lineage of key reported operating results. Prove that the data and the transformations haven't been changed. Show who has accessed or changed any such data.

35. Security system. Administer role-based security on all data and metadata in the ETL pipeline. Prove that a version of a module hasn't been changed. Show who has made changes.

36. Backup system. Backup data and metadata for recovery, restart, security, and compliance requirements.

37. Metadata repository manager. Comprehensive system for capturing and maintaining all ETL metadata, including all transformation logic. Includes process metadata, technical metadata, and business metadata.

38. Project management system. Comprehensive system for keeping track of all ETL development.

If you've survived to the end of this list, congratulations! Here are the important observations I'd like you to carry away: It's really difficult to argue that any of these subsystems are unnecessary as this list makes it really clear that without dividing up the task (perhaps 38 ways), the descent into chaos is inevitable. The industry is ready to define best-practices goals and implementation standards for each of these 38 subsystems, and it would be a tremendous contribution for the ETL tool vendors to provide wizards or serious templates for each of these 38 subsystems. We have a lot to talk about. Maybe 38 more columns!

Never GONE

2007-05-06T18:25:00.000+06:00

The things we did, the things we said

Keep coming back to me and make me smile again

You showed me how to face the truth

Everything that's good in me I owe to you

Though the distance that's between us

Now may seem to be too far

It will never seperate us

Deep inside I know you are

Never gone, never far

In my heart is where you are

Always close, everyday

Every step along the way

Even though for now we've gotta say goodbye

I know you will be forever in my life

Never gone......

BACK in SUMMer Of 69

2007-04-30T11:25:00.000+06:00

"Summer of '69"

I got my first real six-string
Bought it at the five-and-dime
Played it till my fingers bled
It was the summer of '69

Me and some guys from school
Had a band and we tried real hard
Jimmy quit and Jody got married
I shoulda known we'd never get far
Oh when I look back now
That summer seemed to last forever
And if I had the choice
Ya - I'd always wanna be there
Those were the best days of my life

Ain't no use in complainin'
When you got a job to do
Spent my evenin's down at the drive-in
And that's when I met you

Standin' on your mama's porch
You told me that you'd wait forever
Oh and when you held my hand
I knew that it was now or never
Those were the best days of my life

Back in the summer of '69

Man we were killin' time
We were young and restless
We needed to unwind
I guess nothin' can last forever - forever, no

And now the times are changin'
Look at everything that's come and gone
Sometimes when I play that old six-string
I think about ya wonder what went wrong

Standin' on your mama's porch
You told me it would last forever
Oh the way you held my hand
I knew that it was now or never
Those were the best days of my life

Back in the summer of '69

PHYSICAL DATA WAREHOUSE DATABASE DESIGN

2007-04-27T13:49:00.000+06:00

Before you start the replational database phsical design process you should have completed logical model. there is verylittle to do between the logical and physical model.

there is very little to between the logical and physical models . here are the basic steps which we discusss in details follows:

Ensure that object names match the naming conventions. your logical model should already be using good clear sensible names that confor to your naming conventions

Ensure that each columns has the correct data type. start off with the column definitions from the modeling process.

Specify how to handle a changed value for each dimension attribute.

identify the levels of each hierarchies, such as the familiar year to quarter to moth to day

decide whether you will declare foreign keys in the database for all, some or none of the logical foreign key relationship

develop your intial indexing plan
develop your fact table partitioning plan

sepcify dimension and fact metadata- process related metadata that you may choose to include in each dimension and fact row.

Physical Data Modeling for Warehouse

2007-04-26T13:03:00.000+06:00

Yesterday i design one RegistrationMart(that's small form of a data warehouse,we are using for our project)physical data model used by ERWIN from computer associates.

ERWIN is an awesome tool for Logical and Physical data modeling.The beauty part of ERWIN is there is Reverse and Forward engineering.

Reverse Engineering: you can connect to the source database and get the entire schema through this function.

Forward Engineering: The Schema you have develped, you will be able to push into Target Database. it will create a Physical Model.

And moreover, you'll be able to create Triggers, Keys, Stored procedures etc.. along with your Schema.

REDHAT DEVELOPMENT METHODOLOGY

2007-04-26T12:50:00.000+06:00

Recently i found Redhat Development Methodology from my Roomy(he's working with Redhat India solution architect).Tht's quite interesting!!
the Redhat methodology as follows:

Within these seven steps(i'd called these steps as 7 GOLDEN STEPS), we can frame problems, as the right questions. Create more ideas, and choose the best answers. These steps aren’t always linear. They can occur simultaneously or be repeated. This is process from which innovation happens

Define

Decide what issue you are trying to resolve.
Agree on who the audience is.
Prioritize the project in terms of urgency.
Determine what will make the project successful
Establish a glossary of terms

Research

Review the history of the issue
Remember any existing obstacles.
Collect examples of other attempts to solve the same issue.
Note the project’s supporters, investors and critics.

Ideate

Create multiple ideas.
Record your brainstorming sessions; write everything down.
Do not judge or debate ideas

Prototype

Combine, expand and refine ideas.
Create multiple drafts.
Seek feedback from a diverse group of people.
Present a selection of ideas to the client
Reserve judgment and neutrality

Choose

Review the objective
Set aside emotion and ownership of ideas.
Remember: the most practical solution isn’t always the best.
Select the most powerful idea.

Implement

Assign tasks.
Execute.
Deliver to client.

Learn

Gather feedback from the consumer.
Determine if the solution met its goals.
Discuss what could be improved
Measure success; collect data.

how do u feel now, it's sounds good right!!!

Software Development Lifecycle

2007-04-26T12:30:00.000+06:00

I strongly recommend the project management follows by a good software development cycle.So we can discuss about some software development life cycle.
courtesy( from my reading:professional SSIS wrox publications):

software development lifecycle
------------------------------
software development life cycles or SDLC's are a systamatic approach to each componenent of application development.

The first formal software development lifecycles are sequential or linear, that is they begin with one step and proceed through subsequent steps until reaching a final step. A typical example of linear methodology steps are following:

1.Analysis: review the business needs and develop requirements.
2.Design: develop a plan to meet the business requirements with sofware solution
3.Development:build the software solution
4.Implementation:install and configure the software solution
5.Maintenance:address software issues identified after implementation.

the methodologies are referred to as waterfall methodologies because information and softwaer "fall" one way from plateau to plateau(step to step)

waterfall methodology has lots of appeal for project manager. it is easier to determine the status and competeness of a linear project.its' either in analysis, design, development, in implementation and in maintenance.

We are following RUP methodology for our applicaion development but it's so similiar to Waterfall methodology.

RUP(Rational Unified Process)Methodology
----------------------------------

Inception: The major goal of the inception phase is to achieve concurrence among all stakeholders on the lifecycle objectives for the project. The inception phase is of significance primarily for new development efforts, in which there is significant business and requirement risks that must be addressed before the project can proceed.

Elaboration: The goal of the elaboration phase is to baseline the architecture of the system to provide a stable basis for the bulk of the design and implementation effort in the construction phase.

Construction: The goal of the construction phase is on clarifying the remaining requirements and completing the development of the system based upon the base lined architecture.

Transition: The focus of the Transition Phase is to ensure that software is available for its end users.

ITERARTIVE SDLC'S
------------------------------------------------------------------------

Iterative methodology begins with the premise that it's impossible to know all requirements for a successful application before development starts:

The following examples of iterative SDLC's

Spiral: typified by ever expanding scope in hopes of identifying large flaws as soon as possible

Agile: A collection of methodologies fall into this category including SCRUM, FEATURE DRIVEN DEVELOPMENT, EXTREME PROGRAMMING, TEST DRIVEN DESIGN and others.

Physical Design

2007-04-24T02:19:00.001+06:00

i dont know wht's the time now... but it might quarter past 2.am, before go to bed, i need to scrap something!!!

I'm doing now Physical Datawarehouse Design(just like create table, indexes, references etc..) before starting ETL stage..i cant type it guys more... sorry
will make it tommarow:-)

Major Milestones of OLAP history

2007-04-20T17:13:00.000+06:00

OLAP milestones (courtesy OLAP.com)
Year Event Comment
1962
Publication of A Programming Language by Ken Iverson
First multidimensional language; used Greek symbols for operators. Became available on IBM mainframes in the late 1960s and still used to a limited extent today. APL would not count as a modern OLAP tool, but many of its ideas live on in today’s altogether less elitist products, and some applications (eg Cognos Planning Analyst and Cognos Consolidation, the former Lex 2000) still use APL internally.

1970
Express available on timesharing (followed by in-house versions later in the 1970s)
First multidimensional tool aimed at marketing applications; now owned by Oracle, and still one of the market leaders (after several rewrites and two changes of ownership). Although the code is much changed, the concepts and the data model are not. The modern version of this engine is now shipping as the MOLAP engine in Oracle9i Release 2 OLAP Option.

1982
Comshare System W available on timesharing (and in-house mainframes the following year)
First OLAP tool aimed at financial applications. No longer marketed, but still in limited use on IBM mainframes; its Windows descendent is marketed as the planning component of Comshare MPC. The later Essbase product used many of the same concepts, and like System W, suffers from database explosion.

1984
Metaphor launched
First ROLAP. Sales of this Mac cousin were disappointing, partly because of proprietary hardware and high prices (the start-up cost for an eight-workstation system, including 72Mb file server, database server and software was $64,000). But, like Mac users, Metaphor users remained fiercely loyal.

1985
Pilot Command Center launched
First client/server EIS style OLAP; used a time-series approach running on VAX servers and standard PCs as clients.

1990
Cognos PowerPlay launched
This became both the first desktop and the first Windows OLAP and now leads the “desktop” sector. Though we still class this as a desktop OLAP on functional grounds, most customers now implement the much more scalable client/server and Web versions.

1991
IBM acquired Metaphor
The first of many OLAP products to change hands. Metaphor became part of the doomed Apple-IBM Taligent joint venture and was renamed IDS, but there are unlikely to be any remaining sites.

1992
Essbase launched
First well-marketed OLAP product, which went on to become the market leading OLAP server by 1997.

1993
Codd white paper coined the OLAP term
This white paper, commissioned by Arbor Software, brought multidimensional analysis to the attention of many more people than ever before. However, the Codd OLAP rules were soon forgotten (unlike his influential and respected relational rules).

1994
MicroStrategy DSS Agent launched
First ROLAP to do without a multidimensional engine, with almost all processing being performed by multi-pass SQL — an appropriate approach for very large databases, or those with very large dimensions, but suffers from a severe performance penalty. The modern MicroStrategy 7i has a more conventional three-tier hybrid OLAP architecture.

1995
Holos 4.0 released
First hybrid OLAP, allowing a single application to access both relational and multidimensional databases simultaneously. Many other OLAP tools are now using this approach. Holos was acquired by Crystal Decisions in 1996, but has now been discontinued.

1995
Oracle acquired Express
First important OLAP takeover. Arguably, it was this event that put OLAP on the map, and it almost certainly triggered the entry of the other database vendors. Express has now become a hybrid OLAP and competes with both multidimensional and relational OLAP tools. Oracle soon promised that Express would be fully integrated into the rest of its product line but, almost ten years later, has still failed to deliver on this promise.

1996
BusinessObjects 4.0 launched
First tool to provide seamless multidimensional and relational reporting from desktop cubes dynamically built from relational data. Early releases had problems, now largely resolved, but Business Objects has always struggled to deliver a true Web version of this desktop OLAP architecture. It is expected finally to achieve this by using the former Crystal Enterprise as the base.

1997
Microsoft announced OLE DB for OLAP
This project was code-named Tensor, and became the ‘industry standard’ OLAP API before even a single product supporting it shipped. Many third-party products now support this API, which is evolving into the more modern XML for Analysis.

1998
IBM DB2 OLAP Server released
This version of Essbase stored all data in a form of relational star schema, in DB2 or other relational databases, but it was more like a slow MOLAP than a scalable ROLAP. IBM later abandoned its “enhancements”, and now ships the standard version of Essbase as DB2 OLAP Server. Despite the name, it remains non-integrated with DB2.

1998
Hyperion Solutions formed
Arbor and Hyperion Software ‘merged’ in the first large consolidation in the OLAP market. Despite the name, this was more of a takeover of Hyperion by Arbor than a merger, and was probably initiated by fears of Microsoft’s entry to the OLAP market. Like most other OLAP acquisitions, this went badly. Not until 2002 did the merged company begin to perform competitively.

1999
Microsoft OLAP Services shipped
This project was code-named Plato and then named Decision Support Services in early pre-release versions, before being renamed OLAP Services on release. It used technology acquired from Panorama Software Systems in 1996. This soon became the OLAP server volume market leader through ease of deployment, sophisticated storage architecture (ROLAP/MOLAP/Hybrid), huge third-party support, low prices and the Microsoft marketing machine.

1999
CA starts mopping up failed OLAP servers
CA acquired the former Prodea Beacon, via Platinum, in 1999 and renamed it DecisionBase. In 2000 it also acquired the former IA Eureka, via Sterling. This collection of failed OLAPs seems destined to grow, though the products are soon snuffed-out under CA’s hard-nosed ownership, and as The OLAP Survey 2, the remaining CA Cleverpath OLAP customers were very unhappy by 2002. By the The OLAP Survey 4 in 2004, there seemed to be no remaining users of the product.

2000
Microsoft renames OLAP Services to Analysis Services
Microsoft renamed the second release of its OLAP server for no good reason, thus confusing much of the market. Of course, many references to the two previous names remain within the product.

2000
XML for Analysis announced
This initiative for a multi-vendor, cross-platform, XML-based OLAP API is led by Microsoft (later joined by Hyperion and then SAS Institute). It is, in effect, an XML implementation of OLE DB for OLAP. XML for Analysis is the native API for Analysis Services 2005, but most client tools still use OLE DB for OLAP.

2001
Oracle begins shipping the successor to Express
Six years after acquiring Express, which has been in use since 1970, Oracle began shipping Oracle9i OLAP, expected eventually to succeed Express. However, the early releases of the new generation Oracle OLAP were incomplete and unusable. The full replacement of the technology and applications had still not occurred by 2006, some 11 years after Oracle acquired Express, and there are still very few users of the Oracle OLAP Option.

2001
MicroStrategy abandons Strategy.com
Strategy.com was part of MicroStrategy’s grand strategy to become the next Microsoft. Instead, it very nearly bankrupted the company, which finally shut the subsidiary down in late 2001.

2001
Siebel acquires nQuire
Siebel was surprisingly successful with what became Siebel Analytics, which now seems destined to become the core of Oracle’s future BI strategy.

2002
Oracle ships integrated OLAP server
Oracle9i Release 2 OLAP Option shipped in mid 2002, with a MOLAP server (a modernized Express), called the Analytical Workspace, integrated within the database. This was the closest integration yet between a MOLAP server and an RDBMS. But it is still not a complete solution, lacking competitive front-end tools and applications.

2003
The year of consolidation
Business Objects purchases Crystal Decisions, Hyperion Solutions Brio Software, Cognos Adaytum, and Geac Comshare.

2004
Excel add-ins go mainstream
Business Objects, Cognos, Microsoft, MicroStrategy and Oracle all release new Excel add-ins for accessing OLAP data, while Sage buys one of the leading Analysis Services Excel add-in vendors, IntelligentApps.

2004
Essbase database explosion curbed
Hyperion releases Essbase 7X which included the results of Project Ukraine: the Aggregate Storage Option. This finally cured Essbase’s notorious database explosion syndrome, making the product suitable for marketing, as well as financial, applications.

2004
Cognos buys its second Frango
Cognos buys Frango, the Swedish consolidation system. Less well known is the fact that Adaytum, which Cognos bought in the previous year, had its origins in IBM’s Frango project from the early 1980s.

2005
Microsoft finally ships the much-delayed SQL Server 2005
Originally planned for release in 2003, Microsoft managed to ship the major ‘Yukon’ version just before the end of 2005.

2005
Pentaho buys Mondrian
Pentaho acquires Mondrian, as part of the process of assembling a full-blown open source BI suite.

2006
Palo launched
The first open source MOLAP server.

2006
Microsoft buys ProClarity
Microsoft underlines its BI ambitions by buying the leading front-end suite for Analysis Services.

Why OLAP?

2007-04-20T16:31:00.000+06:00

Easy User navigation: User-Oriented names for database objects, and transparent join paths between dimensions and facts between multiple fact tables.

Complex Calculations: centralized storage of calculation logic, and execution of calculations.

Fast user query performance: Usually accomplished through aggregate navigation and aggergate management

Data Security definition and enforcement: preferably managed on a server rather than on user desktops.

The Microsoft DW and BI Toolset

2007-04-20T16:18:00.000+06:00

Courtesy: Ralph Kimball Microsoft Data warehouse Tool kit

The core set of DW/BI tools Microsoft sells is MS Sql server 2005. Sql server includes several major components of primary interest for DW/BI projects.

The relational engine(RDBMS) to manager and store the dimensional datawarehouse database. i think this cost around approximately 700 thousand indian Rupees.
Integration Services-ETL tool(free)
Analysis Services-OLAP engine(free)
Analysis services with data mining-statistical analysis tool(algorithms like neural netwoks, decision trees etc..) (free)
Reporting Services - making charts and adhoc reporting(free)
Development and Management tools(BI development studion and Sql server Management Studio, comprised functionality of Entreprie manager and query 2000)

Slowly Changing Dimensions (SCD's)

2007-04-18T18:22:00.000+06:00

One of the interesting and difficult DW principle: Slowly changing Dimensions

Sometimes attributes of your Dimensions may change, because change is inevitable,you need to face that:-)
Forexample employees DOB wont change, it will be fixed but Department may change. if the value of an attribute can change, We call dimensions that have changeable attribut values slowly changing dimensions (SCDs).

Slowly Changing Dimension Type 1- Overwriting existing attributes with a new value.
use this method if you dont care about keeping track of historical values, when the value of an attribute changes.

Changing the history is not good because thaat any time your analysts try to do historical correlation analysis, they risk drawing incorrect conclusion. and if the attribute is not considered relavant, it probably doesnt matter if you overwrite the old valuse when a change occures.

Slowly Changing Dimension Type 2- Tracking Historical values

if you need to track the history of attribute change suse Type 2. When a change to be Type 2 attribute occures, the ETL process creates a new row in the dimension table to capture the new values of the changed item.

When you use the Type 2 method, this will affect both the fact and dimension table. it will add new row to the dimension with new surrogate key, as well as fact table(new row added with new FK-surrogate key)

Integration Services has a wizard to define the process.

Slowly Changing Dimension Type 3-Keeps seperate columns for both the old and new attribute values.

This type of SCDs sometimes called as alternative realities, in Kimball experience says" Type 3 is less common because it involves changing the physical tables and is not very scalable."

Conformed Dimensions and Surrogate Keys

2007-04-18T17:02:00.001+06:00

We are following Ralph Kimball methodology for our DW implementation. so i need to understand few of the dimensional modeling principles.

Dimensions are the foundations for Dimensional model, describing the object of the business.The business process(facts-sales etc..)are the verbs or actions of the business in which the nouns(dimensions) participates.

CONFORMED DIMENSIONS

A single dimension that is shared across all these business processes is called Conformed dimensions. for example, the product dimension will be involeved in suppier orders, inventory, shipments and returns; these type of the dimension called conformed.

This works only if the two business process use the exact same product dimension with the same keys.Conformed dimensions are cornerstones for enterprise DW implementation. This kind of analysis involving data from more than one business process is called DRILL ACROSS.

SURROGATE KEYS

for your DW system you will need to create a whole new set of keys in the data warehouse database, seperate from the keys in the Transaction source systems.
although they are also known as meaningless keys, substitute keys, non-natural keys or artificial keys.

Surrogate keys usually made by int data types.

A surrogate keys becomes the primary key of the dimension table and is used to join the dimensions to the associated foreign key field in the fact table.

Benefits of Surrogate keys:

1. Surrogate keys protect DW systems from changes in the source system.
2. Surrogate keys allows DW system to integrate data from multiple data source systems.

3. it enables to add additional columns in the dimension, which is not avialable in the source system

4. it supports Slowely Changing Dimensions

5. it tracks history of dimension attributes.

IMPORTANT NOTIFICATION: The dimension tables are accessed by surrogate keys, the facts table rows will not join to the dimensions until the business keys are replaced by surrogate keys( i found error in Analayis serves cube implementation,while i'm connecting to Dimension PK to Fact FK)

Microsoft Integration services will help you to do this, when you load the fact table rows you need to look up the source system key in each dimension table and substitute the corresponding surrogate key in what we call the surrogate key pipeline.

Data Warehoue Project Plan

2007-04-18T13:26:00.000+06:00

I prepared complete project plan for DW/BI with help of Kimball method:
I'm planning to USE Rational Unified Process Methodology for this:

BI implementaion Plan

ELABORATION

Data Modeling
Build Matrix
Choose Data Mart
Declare Grain
Choose Dimensions
Develop Fact Table Diagram
Document Fact Table Detail
Analyse Data Source
Identify Data Sources
Browse Data Content
Develop Source to Target Data Map
Estimate Number of Rows
Technical Architecture
Gather & Document Technical Requirements
Review Current Technical Environment
Create Architecture diagram
Proof of concept

CONSTRUCTION

ETL (Extraction, Transformation, Loading)
Physical Database design
Develop Detailed Staging Plan by Table
Develop 1st Static Dimension Table Process (Extract, Transformation & Load)
Set Up Production Environment
Load Initial Test Data
Initial Data Validation/Quality Assurance
First Time Load Historical Data
Set Data Refersh Cycle
Preapare ETL Document
Develop Data Quality Assurance Plan
Data Warehouse Administration ( Partiioning, Indexing, Backup etc..)

Posted till ETL part, I need to finish this end of this month.

Data Warehouse

2007-04-18T12:59:00.000+06:00

i just started to making data warehouse for our project,
i grabbed a book from store'preofessional sql server 2005 integration services' published by wrox. written by brain, allan, darren etc...

i realized it's huge task for creating a data warehouse,.. i need to do this with single hand.

i'm using four books for this implementation: Kimball the microsoft data warehouse tool kit with sql server 2005, integration services, professional analysis services with mdx and Kimball complete Data warehouse tool kit.
---------------- notes-------------
The dimensional model is the best way to meet our primary design goals.
What is Dimensional Model?
A dimensional model is made up of central fact table( or fact tables) and it's associated with dimensions. the dimensional model is also called star schema.
In the Analayis services OLAP environment the engine is pecifically designed to support dimensional models.

Goals of Dimensional Modeling:

1. To present the needed information to users as simply as possible
2. Query speed
3. relevant information for tracking accurately business process.

Relational Data Modeling: Normalised Fact table and Denormalised Dimension table.

First Report

2007-04-17T16:40:00.000+06:00

I created first report through MS sql server 2005. it's not a exhaustive tool for reporting purpose!!

Before creating a cube, i thought i need to shift Integration services for creating a datawarehouse... becos i'm facing data transformation, and granualarity issues from analysis services so that i thought of going for a wareshouse...

Tomarrow, i'm going to make data warehouse in ms sql server 2005.. let's hope for the best:-))

First Cube

2007-04-16T17:13:00.000+06:00

Just few minutes back, i created one Paitent OLAP cube in MS SQL server 2005(Hooray). it's working fine now. it's fetching data from Oracle(Rcare Magnum FO:Our HIMS database).so i'm going to add more and more dimension to this cube....

Last Saturday, i saw Apple Imac laptop.. Awsome it is!!! especially Macintosh OS.... No hanging.. No Virus... and price also very cheeper than windows vista...
Apple is most innovative company in the world.... Only Steve Company can do that...

I'll update you the "Patient OLAP cube" soonnnnnnnnn

Making Healthcare Data Work better

2007-04-13T12:49:00.001+06:00

Hospitals produce large volumes of data from their operations and also routinely collect much more information from the environment. In fact, data is most important asset for any organization, but not in its raw from.

Business intelligence is the process of converting healthcare data into Healthcare wisdom. The analytical tools convert data into business intelligence. The tools include data warehouses, Data mining tools and OLAP tools. BI has become a strategic weapon, which has been found to be useful in all functional areas.

Business Inetlligence as " A way to view and analyze a lot of data to better understand why something happened, is happening or could happen"

Healthcare Scenario:

Consider this that not even a single day passes, without some negative being published in the media, regarding healthcare industry in general. Most of this news is regarding issues like medical errors resulting in fatalities, poor patient care, raising cost etc.. Media supports it news with statistics and patient experiences.

Your organization is one of the leaders in healthcare. You have excellent record of providing quality healthcare with very low medical errors. However, unfortunately for you, your organization is also becoming a victim of negative publicity in the media. Under such adverse publicity, most healthcare organizations tend to take a defensive reactive approach.

Your organization in fact, can convert this general adverse publicity about healthcare industry, to your advantage, provided you have the right data and strong analytical tools. The tools that can help you in such situations are Business Intelligence Tools. These tools have very advanced analytical capabilities. Supported by the information provided by these tools, your organization send across a strong message as to how your organization is different and convert an adverse situation to your advantage, thus providing that you are head and shoulders above the rest.

The foregoing is one of the many benefits BI can accrue to the organization.

after a break!!!

2007-04-13T12:10:00.000+06:00

i have a BI demo for reliance, while i was taking the pre-screeing, i came to know one thing...,
I've been working in the BI for quite long time but i'm a child still now n the BI field...

so i need to run fast... i need to learn fast... and i'm going to break my silence...

Research report says, there is a huge demand for BI application in coming years in the north american market...

i remember steve job's words:
1."Nobody wants to die, if they were going to heaven also".. great thought right!!!
2. Believe the destiny and the God
3.Do whatever you want , dont let others into your beautiful life for decision making.

I'm going to make a new plan in my life....

"You gotta Dream, You're going to protect it!, You are going to achieve it, People would say: you cant achieve it, do not listen them.... try try try"

courtesy: the pursuit of happiness

WHAT IS BUSINESS INTELLIGENCE

2007-03-21T19:37:00.000+06:00

Business Intelligence is a popularized, umbrella term introduced by Howard Dresner of the Gartner Group in 1989 to describe a set of concepts and methods to improve business decision making by using fact-based support systems. The term is sometimes used interchangeably with briefing books and executive information systems. A Business Intelligence System is a DSS.

Business intelligence (BI) is a broad category of application programs and technologies for gathering, storing, analyzing, and providing access to data to help enterprise users make better business decisions.

BI applications include the activities of decision support, query and reporting, online analytical processing (OLAP), statistical analysis, forecasting, and data mining. Normally describes the result of in-depth analysis of detailed business data. Includes database and application technologies, as well as analysis practices. Sometimes used synonymously with “decision support,” though business intelligence is technically much broader, potentially encompassing knowledge management, enterprise resource planning, and data mining, among other practices. Business process engineering: The analysis and re-design of business processes and associated technology systems, with the goal to eliminate or reduce redundancy and streamline interactions.

Courtesy("DSS Resources.com)

I briefly discussed the term business intelligence in DSS News (Vol. 2, No. 22) and mentioned that “business intelligence” is my favorite IS/IT oxymoron. Well that comment lead to a few emails and this follow-up column.

The general thrust of the emails was that business intelligence is a term like military or competitive intelligence. And hence, the purpose of BI is to gather information to help managers make more “intelligent” decisions and not to make managers more intelligent. This interpretation of the term may be appropriate for describing a staff group tasked with gathering information, e.g., a business intelligence unit, but it works less well for explaining IS/IT technologies and applications.

Information Systems vendors and analysts tend to use the term for a category of software tools that can be used to extract and analyze data from corporate databases. The most commonly used business intelligence software is known as a “query and reporting” tool.
Also, business intelligence is a term that some financial analysts and commentators use for categorizing a small group of software vendors and their products. A number of commentators identify the following companies as major BI tool vendors: Brio, Business Objects, Cognos, Hyperion/Essbase, and MicroStrategy.

From a historical perspective, business intelligence is a popularized, umbrella term introduced by Howard Dresner of the Gartner Group in 1989 to describe a set of concepts and methods to improve business decision making by using fact-based support systems. In the early 1990s, the term was sometimes used interchangeably with briefing books and executive information systems.

SDG Computing's (http://www.sdgcomputing.com/) Business Intelligence and Data Warehousing glossary defines Business Intelligence Tools very broadly as “software that enables business users to see and use large amounts of complex data”. SDG Computing categorizes three types of tools as Business Intelligence Tools:
1. Multidimensional Analysis;
2. Query Tools; and
3. Data Mining Tools.

Well, so much for agreement on the meaning of the term Business Intelligence and related terms like Business Intelligence Tools. What do the major BI vendors say about their BI products and services?

BRIO

Brio doesn't claim to sell Business Intelligence Tools, rather according to the website Brio “markets business performance software, comprised of three subcategories that includes ad hoc query and analysis, reporting/OLAP, executive information systems and analytical applications.

BUSINESS OBJECTS

Business Objects claims it is the world's leading provider of business intelligence (BI) solutions. According to their website, “business intelligence lets organizations access, analyze, and share information internally with employees and externally with customers, suppliers, and partners.” Business Objects was founded in 1990 and it “pioneered the modern business intelligence industry by inventing and patenting a “semantic layer” that insulates users from the technical complexity of database systems”.

COGNOS

Cognos claims it is the leading provider of enterprise business intelligence solutions. Cognos defines business intelligence as “a category of applications and technologies for gathering, storing, analyzing, reporting on and providing access to data to help enterprise users make better business decisions”.

HYPERION

Hyperion is positioned as “a global leader in business intelligence software”. It creates solutions that help businesses measure performance and drive profitability. Hyperion sells a database product called Essbase and financial analysis, performance management and eCRM analysis solutions.

MICROSTRATEGY

Finally, the MicroStrategy website notes the company helps “corporations transform their operational data into actionable information”. MicroStrategy's Business Intelligence platform, MicroStrategy 7, helps meet query, reporting, and advanced analytical needs.
What does all of this mean to IS/IT staff and business managers? Confusion and “hype”. Broadening the Business Intelligence umbrella is continuing and is increasing the conceptual confusion. For example, LexisNexis and iPhrase are partnering to deliver Business Intelligence solutions based on documents in the LexisNexis Advertising Red Books directories. Also, a Reuters news story dated 10/19/01 states business intelligence software allows companies to determine, for example, which of a group of customers is most likely to buy a car this year.
Richard Hackathorn, a pioneer in the field of Decision Support Systems, had some comments about Business Intelligence in the November 2001 DM Review. Hackathorn reminds us that information itself is pure overhead and that it delivers no direct value to a business. He says that today the issue is making BI actionable. He argues that “if your BI projects are not changing the way that you do business, then they should not be considered BI”. Apparently, we still need to “institutionalize” decision support technologies to gain benefits.

Business intelligence services are “big” business. Research firm Gartner Dataquest predicts business intelligence services will grow from $9 billion in 2000 to $18.5 billion in 2005. IDC estimates BI is currently a $5.5 billion market. By the year 2005, IDC expects the business intelligence services market to nearly triple, reaching $15.7 billion annually.
Commentators, IS/IT staff and managers are having problems with the term Business Intelligence. Let's show some mercy and at a minimum add descriptors like services, tools, department or industry to the phrase. Business Intelligence has replaced OLAP as the “hot” buzzword and some vendors want to capture the data mining and customer relationship fads.
In general, I have a problem with all of the various interpretations of the term Business Intelligence. Some BI vendors stress one interpretation and some stress another. Some vendors are moving to the term analytics, but decision support is what it is all about. The most common “business intelligence” software sold is for querying a database and creating a report ... I'd recommend vendors focus on supporting decision making rather than providing “intelligence” to managers or making managers “smarter”, “more capable as decision makers” or “more intelligent”.

For my part, I'll continue to focus on examining and researching various types of DSS, including Data-Driven Decision Support Systems. Let's build a decision support industry that provides organizations with a variety of decision support products and services and let's help managers build information systems that meet their decision support needs.

Difference between Dashboards and Scorecards

2007-03-08T16:33:00.000+06:00

I was reading a book called Business intelligence implemtnation, it's really nice one, i found some intersting articles from this book, i'm going to share it with you all.

The questions as to the dashbord and socrecards comes up constantly, tht's the reason, i'm putting some facts over here....

As you seen dashboards in your day to day life Cars, flights, stock monitoring system etc....
the names dashboard and scorecard are well chosen with respect to thereir real world usage.

Scorecards

A scorecard being a part of a broader corporate methodlogy or management( strategic management tool), socrecard reports of the business with respect to a certain goal (strategy)of the organistion.
According to Dr.Kaplan:
The Balanced Scorecard translates an organisation’s mission and strategy into a comprehensive set of performance measures that provides the framework for a strategic measurement and management system.

The Balanced Scorecard measures organizational performance across four balanced perspectives:

ØFinancial
ØCustomers
ØInternal Business Process
ØLearning and Growth

scorecards analyse the data for long term purpose. scorecards are usually effective for project driven businesses or business segements.

advices regarding to Scorecards:

integrate scorecards with corporate planning: take a company that wants to develop a scorecard system to be integrated into the entire corporate planning process, to link coporate strategy to individual executive and manager goals.
Develop scorecard wisely: the key point here is that if you are developing a scorecard with rewareds assigned to particualr performance objectives, make sure those performance objectives really align with the organisational objectives.
Make them part of culture: Scorecards are fundamentally a part of a culture of accountability.

Dashboards

A dashboard being a set of indicators about the state of a process, price of equipment, or business metric such as cash on hand, patient statistics etc...

in simple words A dashboard is a set of indicators(Key Performance Indicators) of business process.

advices regarding to dashboards:

use actionable indicators: sometimes dashboard indicators are chosen based on information that is readily available or that has interesting patterns. choose indicators that will inform individuals actions to be take.
Find leading indicators: to make lagging indicators for example out of gas indicator in Car analogy.
provide alert points and set them sensibly.
Not everything(indicators) has to be precisely real time, in other words, slightly delayed data is usually better than no data.
Make it real time data for critical information for example allocation of call centers executives support to answer incoming calls to get the hold time down.
Manage information visibility: some information visibility should given to certain people for example A CEO wants to see the fluctuation of stock price of his company

Just based on these examples some distinctions can be discerned:

scorecards inherently measure against goal or strategy, dashboards need not; said another way dashboard present raw news, while scorecard are editorials of sorts.
Dashboards can be provide tactical guidance while scorecards can assess the quality of execution

Design Tip Dashboards Done Right

2007-02-22T16:55:00.000+06:00

This is very good article from KIMBALL University for designing a Dasboard:

With their graphically appealing user interfaces, dashboards and their scorecard cousins are demo superstars. Dashboards have really grabbed the attention of senior management since they closely align with the way these people operate. What’s not to like about the promise of performance feedback on every customer or supplier facing process in the organization at a glance. It’s no wonder execs are enthused.

But there’s a dark side to dashboard projects. They’re extremely vulnerable to runaway expectations. They’re risky due to the cross organizational perspective inherent in most dashboard designs. And they can be a distraction to the DW/BI team; rather than focusing on the development of an extensible infrastructure, dashboard projects often encourage a data triage where key performance indicators from a multitude of processes are plucked and then pieced together using the systems equivalent of chewing gum and duck tape.

Dashboards and scorecards done right are layered on a solid foundation of detailed, integrated data. Anything less is ill-advised. Dashboards based on manually collected, pre-aggregated, standalone subsets of data are unsustainable in the long run.

If you have an existing data warehouse that’s populated with the requisite detailed, integrated data, then you should tackle any proposed dashboard development project with gusto. Dashboards present a tangible opportunity to deliver on the promise of business value derived from your data warehouse. The dashboard interface appeals to a much broader set of users than traditional data access tools. In addition, dashboards provide a vehicle for going beyond rudimentary, static reporting to more sophisticated, guided analytics.

But what do you do when your executives are clamoring for a sexy dashboard, but there’s no existing foundation that can be reasonably leveraged? Facing a similar predicament, some of you have bootstrapped the dashboard development effort. And it may have been initially perceived as a success. But then middle managers start calling because their bosses are monitoring performance via the dashboard, yet there’s no ability for them to drill into the details where the true causal factors of a problem are lurking. Or management starts to question the validity of the dashboard data because it doesn’t tie to other reports due to inconsistent transformation/business rules. Or the users determine they need the dashboard updated more frequently. Or your counterpart who supports another area of the business launches a separate, similar but different dashboard initiative. The quick bootstrapped dashboard will be seriously, potentially fatally, stressed from the consequences of bypassing the development of an appropriate infrastructure. Eventually you’ll need to pay the price and rework the initiative.

While it’s perhaps less politically attractive at first, a more sustainable approach would be to deliver the detailed data, one business process at a time, tied together with conformed master dimensions, of course. As the underlying details become available, the dashboard would be incrementally embellished to provide access to each deployment of additional information. We understand this approach doesn’t deliver the immediate “wow” factor and requires executive patience. While executives may not naturally exhibit a high degree of patience, most are also reluctant to throw away money on inevitable rework caused by taking too many shortcuts. Having an honest conversation with business and/or IT management so they fully understand the limitations and pitfalls of the quick-and-dirty dashboard may result in staunch converts to the steadier, more sustainable approach.

Those of us longer in the tooth remember EIS, or Executive Information Systems, that blossomed briefly in the 1980s. EIS suffered from exactly the same problem we are discussing here. The carefully prepared executive KPIs were not supported by solid detailed data that could withstand drill down. Any good executive is going to ask “why?” And that’s when the data warehouse and its dashboards need to sit on a solid foundation.

Key Components of Business Intelligence Environment

2007-02-22T16:52:00.000+06:00

Source Systems
Operational Data Source(ODS)
Dashboard
Enterprise data Warehouse
Data Marts
All these components move data using an Extraction, Transformation and Loading

Source Systems: data from your operational systems, departmental data( spread sheets, flat files etc..) and external data sources(research repositories, reports etc..)

Operational Data Source(ODS): Operational data sources are really operational like systems that combine systems into single system for reporting purposes.

The ODS data may or may not be cleansed or transformed. The ODS does not take all the data from source systems. But just data required to fulfill its mission in life

Enterprise Data Warehouse: The enterprise data warehouse is a conceptual construct. There may or may not be physical implementation. Some people believe that you have to build an enterprise warehouse, but some of them believe that no need for the enterprise data warehouse.

Dashboard: the dashboard is defined by the metrics that are needed to run the business. In effect it is the “health of the business” metrics.

In some organization dashboard may be just an email every morning providing those metrics. In other organizations dashboard may be a web page that has the high level view of the data with the ability to drill down to more detailed data.

Data Mart: is a subset of Data warehouse (I have discussed with before)
ETL: Extract Transform and loading, discussed before

SOME BASICS OF DATABASE

2007-02-16T15:56:00.000+06:00

What is an entity?
an entity usually represents a real life objects
what is primary key?
each table have a unique identifier, to providing a uniqueness in a table by designing a primary key
unique+ not null is primary key

which is a single coulumn or a set of columns that uniquely identified each row in a table is primary key

What is candidate key?
each column or set of columns in a table that contains unique values is considered a candidate key.

what is alternate key?
one candidate key become primary key, the remaining candidate key become alternate keys

what is composite key?
a prmary key comprising multiple columns is considered as composite key

What is Normalisation?
Normalisation is the process of applying a series of rules to ensure that your database achieved optimal structure.

what is first normal form?
To achieve the first normal form all the columns in a table must be atomic. this means you cannot store firstname and lastname in the samefield

another rule is not containig repeating values

what is second normal form?
all other columns except primary key, fully dependend on the primary key.

another rule is decomposition of tables that is break the tables in appropirate format, for example order table and order details table

what is third normal form?

compliant with first and second normal form
mutually independent.

Data Warehouse Mehodologies

2007-02-02T14:48:00.000+06:00

Data warehouse provides various benefits to different organisation. the major benefits to the organisations are:

* Saving Time, as y;ou do not have to search for information of different departments.
* Providing Historical data.
* Providing data for discision making

Broadly data warehousing methodologies are classified into two:

***Iterative Approach( making data marts, and finally integrating to Enterprise Data Warehouse-Not much risky) and
***Bing Bang Approach( highly risk involving to this approach-Making BIG enterprise Data Warehouse for Decision support system)

SAS Institute rapid Data Warehouse Method.

The two major reasons of data warehouse failure are lack of experience of the people involved in creating data warehouse and developing large data warehousing projects. the SAS methodology provides quick return on investments for creating by diving a data warehouse projects into builds. each builds further divided into stages, Such as assessment, requirement, desing, construction, final test deployment. The SAS method handles the technical, project management and quality assurance processes.

Multi Dimensional Expressions

2007-01-31T15:47:00.000+06:00

i started to learn MDX part of analysis services, i found it's very interesting.

What is MDX?
Multi dimensional expressions is a query language used to retrieve data from multi-dimensional databases(cubes). more specifically MDX is used for querying data from OLAP database.
MDX is two types:
1)manupulate & calculate and define expressions and
2)retrieve data from OLAP databases

Origin:
MDX was first introduced as part of the OLEDB for OLAP specification in 1997 from Microsoft (where it was invented by Mosha Pasumansky). The specification was quickly followed by commercial release of Microsoft OLAP Services 7.0 in 1998, and later by Microsoft Analysis Services.

While it was not an open standard, but rather a Microsoft owned specification, it was adopted by the wide range of OLAP vendors. This included both vendors on the server side such as Applix, Microstrategy, SAS, SAP, Whitelight, NCR and vendors on the client side such as Panorama, Proclarity, AppSource, Cognos, Business Objects, Brio Technology, Crystal Reports, Microsoft Excel, etc.

Mr.Mosha Pasumansky is inventor of MDX language. He's working with microsoft. he's dedicated his life for MDX language. you will find more information from his own site:
http://www.mosha.com

BASICS of MDX

CUBE: is the foundation of multi dimensional database. Each cube contains more than two dimensions.

MEASURE: the measures object is basically special dimension of the cube which is a collection of measures. meaures are quantitative entities which are sued for analysis.

MEASURE GROUP:each measure is part of entity called measured measure group; meaure group is a collection of related measures.

DIMENSION:Dimension is the attributes of the cube,Dimensions has hierarchy (for example product dimension has product line hirearchy)

Each hirearchy of dimensions contains one more items refered to as members.members are dimension items.

CELL: is the smaller part of the cube , which point to a specific piece of a cube.

TUPLES:a tuple uniquely identifies a cell or section of a cube. a tuple is represented by one member from each dimension, seperated by comma, and enclosed with parantheses.

a section of the cube is represented by a tuple is called slice. since you are slicing the cube to form a section(slice) based on certain dimension members.

SET: a set is a collection of tuples that are defined using the exaact same dimensions,a set is typically specified with the curly brace charaters{}

THE FACT LESS FACT TABLE

2007-01-24T20:19:00.000+06:00

The fact table contains one or several numeric attributes that measure business performance. examples are unit sale, dollar sales, number of shipments, number of passengers and so on...

some fact tables represent events uch as etudents attending a course, products placed on promotion, shipment tracking and the like. in such representaions, there is no numeric data to be measured rather the intention is to record the business event as it happens these type of facts are represented with measurable attributes as flags that record the status and are known as " fact less facts"

MOLAP, ROLAP and HOLAP

2007-01-23T17:58:00.001+06:00

where the data and /or aggregated fact data is stored you can classify the storage types as MOLAP( multi-dimensional olap), ROLAP (relational OLAP) and HOLAP(hybrid OLAP).
MOLAP is the storage mode in which the data and aggregated data are both storage.
advantages of MOLAP: better query performance(fast retrieval of data) and can make complex calculations.
Disadv: more storage area needed and inability to see new data entering into your data warehoue.

ROLAP is the storage mode in which the data left in the relational database. aggregrated or summary data(cubes are typeically contain pre-calcuated summary data called aggregations) stored in the relational database

Advantages: ability to handle large cubes(storage).
Disadvantage: slow query performance

Why: when you query to an OLAP tool, it will translate each querty into one or more queries to the relational backend. In OLAP environment normally use multi-dimension expressions to query the cubes.

The HOLAP which combines both storage modes like MOLAP and ROLAP.
disadv: fi the queries request detailed data, appropriate queries are send to the relational backend and these queries can take a long time based on the relational bakced(in terms of ROLAP).

MCTS

2007-01-23T11:11:00.000+06:00

I'm planning to write the exam Microsoft certified technology specialist-business intelligence, on end of next month.
i bought a book analysis services with mdx-professional sql server for preparing this exam. i need to complete this and need to purchase few more:
integration services and reporting services

i'm trying level my best to do in this exam

Difference between OLTP System and Data Warehouse System

2006-12-19T13:45:00.000+06:00

Operational systems largely exist to support transactions, for example healthcare information management system, airline ticketing, railway ticket booking system etc..

Decision support, which is a type of complex analysis is very different from OLTP, most OLTP transactions require a single record in a database to be located and updated or an addition of one or more new records. Even simple decision support query such as “ How Many Cancer Patients Visited Oncology centers in North Wales Region, UK for January 1999” requires very different operations at database level to an OLTP transaction.

OLTP system is fully normalized, and its supports only day to day transactions, but Data warehouse System is demoralized in nature(STAR Schema Model except Snow Flake Schema), and DW system holds historical data as well as transactional data(Real Time Data Analysis-operational Data Source) for complex analytical purpose. And OLTP data stored at transaction level but DW integrated from different Data Sources.

Recently I studied Cognos architecture (www.cognos.com) ; I found Content Manager holds very good part of data integration service. So there is good demand for CMS and Portal services for coming Business Intelligence years.

If the BBC or CNN wants to analyze their Programme, But the data stored in unstructured format then content management system will play big role for integrating those data.

Few Data Warehouse Key facts

2006-12-18T23:10:00.000+06:00

learning the elements of a data warehouse or data mart is, in part, about building a new vocabulary.
Two kind of tables forma a data warehouse fact tables and dimension tables

Fact Table

A fact table contains typically busiess fact data called as "Measures", "Quantitative data" such as sales amount, sales quanity, number of patients, number of clients, number of customers etc..( the fact table contains foreigns keys which is connect to Primary keys of Dimension tables)

Dimension Table

Dimension tables contain detailed information relevent to specific attributes of the fact data, such as customer, product, time, region, et...

To make sense of a cube, which is at the heart of business analysis.Dimensions are most often made up of several hierarchies( hierarchies are logical entities by which business user-end user might want to analyse the fact data. each hierarchies having level such as Country, State,City, village etc... Year, Quarter,Month, Date etc..

Cube:
The cube is a Multidimensional data strucure from which you can query for business information. cubes build by the fact table and dimensions.

In my words when you build a relational database, we derive enity is an object of the business process. in the same way Building a Data warehouse Cube considered as an entity just like RDBMS but it's more subject oriented as well as Analytical purpose...

Additivity of Facts

2006-12-13T18:37:00.000+06:00

Types of Facts

A fact is said tobe fully additive if it is additive over "EVERY DIMENSION" of its dimensionality; Partially(Semi-Additive) if additive over "AT LEAST ONE" but not all of the dimensions; if it is "NOT ADDITIVE OVER ANY DIMENSION".

There are three types of facts:

Additive: Additive facts are facts that can be summed up through all of the dimensions in the fact table.

Semi-Additive: Semi-additive facts are facts that can be summed up for some of the dimensions in the fact table, but not the others.

Non-Additive: Non-additive facts are facts that cannot be summed up for any of the dimensions present in the fact table.

Few Questions

2006-12-12T13:20:00.000+06:00

differnce between OLTP and DW systems
Additive and Non Additive Facts
Attributes of Dimension Table
Difference Between Star Schema and Snow flake Schema?
Data warehouse Modeling in your Real life scenario
Difference between Data modeling and Data Warehouse modeling?
Slowely Changing Dimension
How many facts are using in your BI projects?
Confined facts....

And lot's of Real life Scenarios.............

So i'm going to make strong my basics....

Relational basics

2006-12-12T13:12:00.000+06:00

I was reading the books of Database Concepts and fundamentals i found new thing in that.... it might not be new... but for me it's new :-))

Relational theory of mathematics applied in Relational Database Management System.

Relational theory in matematics divided into two groups...

Relational Algebra and Relational Calculus..

Relational Alegbra Consists few calcualation like restricts, projects, product,divide, union etc...
for example RDBMS applied Restrict Algebra through: comparision operators like=,<,>, <> etc...
it is procedural in nature...

Relational Calculus applied in RDBMS through Spcifed commands. Declarative in nature...

moreover i'm reading through it.....Data Mining mainly consists of Relational Algebra.. and algorithms (a few amount of Artifical Intelligence)

I'm making one presentation on this... i'll update you on this sooon...

My Study Plans

2006-10-26T22:01:00.000+06:00

My plans are to finish and expertise the following books by December 2007

• Microsoft SqL server fundamentals and advanced
• Microsoft Analysis Services 2005 with MDX by sivakumar and stephen quinn
• Microsoft Integration Services
• Microsoft Reporting Services
• Data Warehousing and Best Practices
• Business Intelligence Road Map (the complete project lifecycle for decision support applications by Larissa T moss, Shaku Atre
• Balance Scorecard by Kaplan
• Data Warehouse toolkit Ralph Kimball through MS sql server business intelligence

Trainings

• Cognos 8 planning to finish by end of December(done only query studio)
• Enroll CBIP march 2007 finish by end of septemeber
• CBIP training(exam 1, exam 2, exam 3)
• Planning to attend few BI conferences.

Certifications

• Certified Business Intelligence Professional by end of August 2007
• Microsoft certified technology specialist end of feb 2007

eLearning :: balanced scorecard collaborative :: driving breakthrough results

2006-10-25T14:17:00.000+06:00

eLearning :: balanced scorecard collaborative :: driving breakthrough results

Real Time Business Intelligence or BAM

2006-10-16T22:13:00.000+06:00

I slipped into new subject called real time BI and Business Activity Monitoring, it' s very interesting... becos i always like live and fresh data to fast business decisions...

i'm just reading gartner report about real time business intelligence...

two methods using for real time business intelligence vendors:
1.event driven programming for getting and analysing the data.
2. making datawareshouse refresh cycle faster..(milli second loading)

i got very valuable resources from wikipedia about real time business intelligence and also
i found www.seewhy.com very good vendor of real time BI segment..

more: after sometime..:-))

META DATA

2006-09-29T15:20:00.000+06:00

What is META DATA information in Data warehouse project?

META DATA is the data about data. Well that’s not enough definition for interviews we need something more than that to tell the interviewer. It’s the complete documentation of a data warehouse projects. From perspective of sql server all META DATA is stored in micro soft repository. It’s all about way the structure is data warehouse

Just to summarize the some elements of data warehouse META DATA are as follows:

Source Specification – such as repositories, source schemas etc…
Source Descriptive Information – Such as ownership descriptions, update frequencies, legal limitations, access methods etc…
Process information – such as job schedules extraction code.
Data acquisition information – such as transmission scheduling, results and file usage.
Dimension table management- such as definitions of dimensions, surrogate key.
Transformation and aggregation – such as data enhancement and mapping, DBMS load scripts, aggregate definitions etc..
DBMS system Table contents,
Description for columns
Network security data.

A letter from my BI friend (FYI)

2006-09-23T17:12:00.000+06:00

Assuming you're a "non-techie" that means you have functional experience(meaning you know how some part of your business works), you should leverageyour experience by creating reports. Report development requires anunderstanding of the end use, so your functional experience should helphere. Build on the reporting tools that are available to you. Become apower user. That might mean learning VBA for macros in Excel, Access, orwhatever tool your company has. It won't do you any good to learn Cognos ifyour company uses Essbase. Aside from being useful to your existingemployer, the real trick is to move from being a functional person, to atechnical one. That means a technical job opportunity will expect lots ofactual experience, not just book experience, before you'll even qualify foran interview.

Learn SQL. Most popular databases use it and it's essential to accessingthe data.

If you have the above skills and experience, you can transition to atechnical role, if you're lucky. You may have to make a large sacrifice inpay so that you're an attractive employee, because a new employer will viewyou as a risk.

At least in the U.S., employers have high expectations and expect resultsright away. I've seen one position filled 4x in 8 months because the peopledidn't "fit". Because of this type of experience, employers will be veryskeptical about your abilities.

If your company has an IT department, talk to them about your goals. Theymight help, they might not.

Once you've made the transition, learn a programming language. Not all BIis done within a proprietary IDE like Cognos, Crystal, or Business Objects.

If you're using Oracle as a back end, then Java or .Net. If SQL Server,then .Net. I've heard that C# is more marketable than VB.Net, but that'sjust a rumor. Have knowledge about web development and architecture.Remember, you have to deploy BI to remote users and the web is a perfectvehicle.

My own experience followed the course above. I tried to move within myemployer, but found too much resistance. I found a software company thatsold business applications in my industry, so I went to work for thesoftware company working with customers (including my former employer). Myfunctional experience gave me an advantage to grow into a full time "techie"

Sorry for being long winded.

Good luck!

Pentaho BI demo installation

2006-09-22T14:29:00.000+06:00

we need to change the following codes for installing pentaho BI demo installation. whaterever the datasource code given by the pentaho user guide is not working properly... Add the JNDI Datasource Mappings to the server.xml File for installing pentaho BI... it will work fine...
thanks jeswin for your extensive help for providing this:

-
f(clean);

-
f(clean);

-
f(clean);

-
-
f(clean);

-
f(clean);

-
f(clean);

-
-
f(clean);

-
f(clean);

-
-
factory
org.apache.catalina.users.MemoryUserDatabaseFactory

-
pathname
conf/tomcat-users.xml

-
f(clean);

-
f(clean);

-
-
f(clean);

-
f(clean);

-
f(clean);

-
f(clean);

-
f(clean);

-
f(clean);

-
f(clean);

-
f(clean);

-
f(clean);

-
f(clean);

-
f(clean);

-
f(clean);

-
f(clean);

-
-
f(clean);

-
f(clean);

-
f(clean);

-
f(clean);

-
f(clean);

-
f(clean);

-
f(clean);

-
f(clean);

-
f(clean);

-
f(clean);

-
f(clean);

-
f(clean);

-
-
f(clean);

-
f(clean);

-
f(clean);

-
f(clean);

-
f(clean);

-
f(clean);

-
f(clean);

-
f(clean);

-

-
-
factory
org.apache.commons.dbcp.BasicDataSourceFactory

-
maxActive
20

-
maxIdle
5

-
maxWait
10000

-
username
pentaho_user

-
password
password

-
driverClassName
org.hsqldb.jdbcDriver

-
url
jdbc:hsqldb:hsql://localhost/sampledata

-
-
factory
org.apache.commons.dbcp.BasicDataSourceFactory

-
maxActive
20

-
maxIdle
5

-
maxWait
10000

-
username
hibuser

-
password
password

-
driverClassName
org.hsqldb.jdbcDriver

-
url
jdbc:hsqldb:hsql://localhost/hibernate

-
-
factory
org.apache.commons.dbcp.BasicDataSourceFactory

-
maxActive
20

-
maxIdle
5

-
maxWait
10000

-
username
pentaho_user

-
password
password

-
driverClassName
org.hsqldb.jdbcDriver

-
url
jdbc:hsqldb:hsql://localhost/quartz

-
-
factory
org.apache.commons.dbcp.BasicDataSourceFactory

-
maxActive
20

-
maxIdle
5

-
maxWait
10000

-
username
sa

-
password

-
driverClassName
org.hsqldb.jdbcDriver

-
url
jdbc:hsqldb:hsql://localhost/shark

-
driverClassName
org.hsqldb.jdbcDriver

-
url
jdbc:hsqldb:hsql://localhost/shark

OPensource BI

2006-09-12T15:18:00.000+06:00

Iv'e one BI collegue in my company. His name is chadru, we both of them are involving for BI development. we just started dowloading opensource tools for creating BI platform. i found pentaho is one of the good product for opensource BI needs: with help of my friend chandru we are going to build a powerful Healthcare BI tool..

Mondrin: chandru is working on this.,, later i'll put my hand on this..

Report Wizard and Report Designer: we both of them got very good idea on this.. it's working fine ...
(Thanks Rudraprasad, he's supporting us very much for installation and making olap cubes)

We have installed complete demo of pentaho BI suite, i'm very thankful for Jeswin louis and Mini)

first we have downloaded: kettle (open source ETL tool), i havent gone throught it, it's working fine now.. i just done a small transformation from oracle to flat files... it's through, i need to learn this within short span of time.. but before that still i need to expertise in SQL becos it's very essential, when you talk about sql i need to concentrate on DATA QUALITY and DATA modeling part becos i believe data quality is the essential part of a Datawarehouse success so i need to invest my time for this..

first of all we need to get good java resource for making cubes and portal, we have already requested waiting for the approval.... this is our major pain area.. once we get it we will start the pentaho BI product development...

i'll update you soon....

2006-09-06T19:43:00.000+06:00

I have attached some basics about Datawarehouse concepts here: it would be helpful for beginners:

(I'll be adding the Analysing Part soon: OLAP, HOLAP, MOLAP) and then will discuss about the current tool i'm working with Pentaho BI opensource, Kettle ETL tool, Mondrian OLAP and JFREE reporting tool and Shark Workflow engine tool soon)....

Courtesy: my reading from oracle Data warehouse consulting methodologies, Business Intelligence roadmap and internet.

Let's Start!!

Management Information Systems and Decision Support Systems

For your business to be successful, fast and accurate access to information is critical. You extract information from existing data. Important decisions are based on the information available at any point in time. In order to get the right information at right time; you need to gather data from internal and external resources. A typical Online Transaction Processing (OLTP) system has many users adding new data to the database while fewer users generate reports from the database. As the volume of data increases, reports takes longer to generate.

Early Management Information Systems

Early Management Information Systems (MIS) provided management with reports to assess the performance the business. Report requirements were submitted as a request to the MIS development team, who developed the report and made it available to the user some time afterward –days, weeks or even months later. The data in the reports was made available in a way that was difficult to use for analysis and forecasting.

Personal computing

With advent of personal computing and 4GL programming techniques, MIS became decision support (Decision Support Systems or DSS). DSS was judged to support business users better, by giving them direct access to the operational data for additional ad-hoc querying, which provided more flexible reporting as the information was needed.

Although decision support tools are friendly, intuitive, and easy to use, often the structure of data in the OLTP systems does not support the user’s analytical requirements

A data warehouse is often used as the basis for a decision-support system(also referred to from an analytical perspective as a business intelligence system). It is designed to overcome some of the problems encountered when an organisation attempts to perform strategic analysis using the same database that is used to perform online transaction processing (OLTP).

Definition of Data Warehouse

Bill Inmon defines data warehouse as:
“ An enterprise structured repository of subject oriented, time variant historical data used for information retrieval and decision support the data warehouse stories atomic and summary data.”

Subject Oriented:

Subject oriented data is organized around major subject areas of an enterprise and is useful for an enterprise wide understanding of those subjects, for example:

Customer financial information
Toll calls made in telecommunication industry
Airline passenger booking information
Insurance claim data
Healthcare analysis etc..

Integrated

In many organizations data resides in diverse independent systems, making it difficult to integrate into one set of meaningful information for analysis. A key characteristic of a DW is the data is completed integrated.

Data consistency: you must deal with data inconsistencies and anomalies before the data loaded into warehouse. Consistency is applied to naming conventions, measurements, encoding structures and physical attributes of data.

Data redundancy: data redundancy at the detail level in the warehouse environment is eliminated.

Time variant

Warehouse data is by name historical; it does not usually contain the current transactional data. Data is represented over a long time horizon, from two to ten years, compared with one to three months of data for a typical operational system. The data allows for analysis of past and present trends and for forecasting using “what If” scenarios.

Nonvolatile

Typically, data in the data warehouse is read-only. Data is loaded into the data warehouse for the first time load, and then refreshed regularly.

Warehousing operation typically involves:

Loading the initial set of warehouse data. (often called first-time load)
Refreshing the data regularly (called the refresh cycle)

Data warehouse versus Data mart

Data mart is the subset of data warehouse. A data mart is a simpler form of a data warehouse designed for a single line of business (LOB) or functional area such as sales, finance or marketing. Data marts are typically smaller and less complex than the data warehouse and therefore are typically easier to build and maintain. A data warehouse typically assembles data from multiple source systems. A data mart typically assembles data from fewer sources.

Dependent and independent Data Marts

Data marts can be categorized into two types: dependent and independent.

Dependent data marts source is data warehouse. Dependent data marts rely on the data ware house for content. The extraction, transformation and loading (ETL) process is easy. Dependent data marts draw data from central data ware house that has already been created.

Independent data marts are stand alone systems built from scratch that the data directly from operational and external source of data. ETL process is difficult because independent data marts draw data from unclean or inconsistent data sources, efforts are directed toward error processing and integration of data. The creation of independent data marts is often driven by the need for quick solution to analysis demands especially Pilot data warehouse projects.

The Heart of DataWarehouse

Extraction, Transformation and Loading (ETL)

These processes are fundamental to the creation of quality information in the data warehouse. You take the data from source systems; clean, verify, validate, and convert it into a consistent state; then move it into the warehouse.

Extraction: the process of selecting specific operational attributes from the various operational systems.

Transformation: The process of integrating, verifying, validating, cleaning and time stamping the selected data into a consistent (star schema model-will discuss with this later) and uniform format (renormalized format) for the target database. (This stage is called as Staging)

Loading (Transformation): The process of moving data from an intermediate storage area into the target warehouse database. This stage is called as Data ware house stage.

Financial justification (Return On Investment customer perspective)

The project is big investment in resources and finances. Management must be able to report on how the data warehouse benefits the business. Justification is divided into three main areas:

The tangible benefits are that the business can remain competitive, respond to changing business conditions, and support reorganization.

Better data and decision making reduce information technology costs. Provide better response times and provide rigorous reporting

Productivity or return on investment (ROI) benefit internal and external users

Return on Investment

The financial justification must set out a strong case that clearly establishes measurements such as cost versus return on investment, and increased efficiency and profit. It must also set clearly defined objectives that can be monitored and measured.

Associated costs: along with cost justification, you should provide a plan that specifies other factors that will impact the cost of the project and other aspects of the business.

The cost of developing ETT or purchasing the ETT tools,
The actual time required for data cleansing, transformation, and extraction, which may impact day to day operations
Storage requirement for extract, summarization, workspace, log space, backup, recovery and maintenance.
The cost of redundant data
Hardware and software costs
The cost of server and system software licenses.
Labor cost.

Obtaining business commitment for DW

A data warehouse implementation requires the total support of those who control the business and make the decisions that drive the business forward. The warehouse is a business driven project, not an information technology drive for the latest hardware, software, tools and techniques.

Business objectives must be clear, well defined, measurable and achievable:

Research and study the business problem; identify the business vision, goals and priorities
Research the solution and define what the warehouse solution may do
Identify the benefit of the solution, such as efficiency, people power, customer satisfaction and returns.
Identify the constraints such as schedule, costs and experience.

Business Intelligence

2006-09-04T19:41:00.000+06:00

This Blog is created for Business Intelligence Consultants, sharing their thoughts of technically and functionally.