Sunday, June 30, 2013

Data Warehousing and Business Objects -- My old presentation

Customization of Infoview -- My old presentation

Wednesday, June 26, 2013

Questions for understanding a BO project

Many of the developers always struggle in beginning of a migration/conversion/consolidation project where they have to understand an existing SAP Business objects reporting environment and build a new one. They start collecting the information and there are no set of rules of as-is analysis.
                I worked in several projects where I faced this difficulty and started preparing a list of question that we need to know about a reporting environment. You can use this template to start requirement gathering and eventually you will come up with bigger or smaller list. But you can use it as start point:   

Environment details
Server Name & Connection of all environments
TNS entries for all environments

Universe Details:
Name & Number of Universe
Name & Number of star Schemas with confirmed Dimensions
Name & Number of tables
Name & Number of Aliases and Contexts
Name & Number of classes with some subclasses
Name & Number of objects
Name & Number of customized hierarchies
Name & Number of conditional objects with time conditions
Name & Number of default hierarchies
Name & Number of aggregate navigations
Name & Number of Linked universe
Security structure
Security groups
Manage access restriction mapping (groups and security levels) if any.
Universe connection name for each environment

Reporting details:
Name & Number of Reports
Name and Number of Linked reports
Reports with multiple data provider
Number of report level objects
Customization of the reports(Means if there any standard template for the reports)
If there any specific alerter for all the reports
Number of Tabbed reports
Scheduling time and scheduling process.
Multilingual Support information
Prompts used in reports

CMC details:
Access levels of the system(Security analysis)
Number of users/groups
Any customization of Info view is there..
Load Balancing system
Third party software used
Integration system with the Active Directory of company with BO

Documents needed
Data Model
Process flows
Functional specification
Operation manual
A single document which track all objects of all reports available

Coformed dimension

Conform dimension
What is conformed dimension?
A Conformed Dimension is any type of dimension that has a single meaning or value across a data warehouse. Literally word “conform” means ‘to follow rules and guidelines’.  Taking that a conformed dimension is a dimension which follows the same rule and guidelines across the data warehouse.

Time dimension is one of the best examples that fit with Conformed dimension.
In a fact table if a key ‘20130605’ pass the staging area it should have column for below information
Year, Month, day, Quarter, Calender_week
This dimension table is conformed for all the business area which is using the data warehouse.

Another example is a Dealer details in in Automotive industry. Dealer information is one of the central dimensions for all the sales, after sales, finance, warranty.

Why conformed dimension is important?
Conformed dimension is best fit for the centralized system. Now a day’s large companies are centralizing the data which are spread across the organization in different warehouse application.

Used dimension in different application may have same or slightly different data. That creates lots of redundancy and unnecessary usage of resource.

Conformed dimension proposed by Kimball is to analyze and finalize the dimension which can be reuse in different application and can be referenced in several disconnected mart.

Which factors is holding back and how to overcome this?
To confirm the conform dimension a major collaborative effort is needed across the organization stakeholders to business analyst and data modeler.
All potential dimensions which can be conformed dimension may have some difference in attribute and stakeholders needs to compromise or change the process for the sake of modeling.
This happens rare.

Kimball suggested a way that you don’t need to do conformed dimension in first place. You don’t have to agree with all the attributes. At a minimum, you should identify a subset of attributes that have significance across the enterprise. These commonly-referenced descriptive characteristics will become the starter set of conformed attributes.

Who needs to be involved to confirm a conformed dimension?
Practically conformed dimension cannot be done enterprise wide at beginning. You need to start with the two major applications which is similar in nature. Then identify commonly referenced dimensions which in future may convert to conformed dimension.

·         To confirm a conform dimension you need:
·         Data Modeler
·         Data Architect
·         Business Analyst
·         Business Owners and stakeholders from each of the application.
·         Business SMEs who have deep knowledge in all the application.

Tuesday, June 4, 2013

What is Business Intelligence(BI)?

What is Business Intelligence?

Business Intelligence was defined by is creator Hans Peter Luhn an IBM researcher as:
“The ability to apprehend the interrelationship of presented facts in such a way as to guide action towards desired goal.”
The sentence was carefully crafted and designed to define an enormous details in single line. That was 1958 and BI came a long way and grab the market in next millennium with help of relational database.
We normally start the discussion of BI from Data Warehouse. Loves to use lots of terms and speak about the fact and dimension. But unfortunately we never dig into the basic plus and minus sign which makes the algebra correct.
                Let’s think this way that why the definition needs to wait for more than 30 years to come into practical life. This is only because a theory cannot work else the tools are invented to implemented that. The definition got its buddy when the relational database word was coined by Dr. E.F. Codd. Relational software (now Oracle Corporation) gives the concept a visual interpretation. The concept and the tools are fuelled by addition of the letter “I” with Oracle 8 which revolutionizes the way of thinking to keep the data. Dot com boom did the last finish and we are now enjoying the beauty of business Intelligence everywhere.
                 It was easy to tell the story of how a revolutionary concept evolves and progressed to its absolute form. But still there is no definition which can accumulate all the parts of Business Intelligence in a single sentence or a paragraph. As everyone tried to define it and end up with a definition of its limbs. Better not to define it rather analyze the holistic view and purpose of Business Intelligence.
Business and Intelligence
Information Technology always funded by large companies who wants to keep their data in a retrievable manner. From where the data come and where the data goes is the main objective of Information Technology.
                Business or so called industry always struggle to keep organize data and organize related data retrieval. To run an automotive business they don’t need a excel sheet but need a hammer. But which size of hammer they need to flatten a piece of steal should be documented somewhere or a person is needed to remember all these things which are totally vague.
                Now hammer can be of different size and shape different weight and thrust. And alone a hammer cannot curve out a body of a car from a sheet metal. You need chisel, anvil. These also have own measurement. Someone needs to understand the match and implement in the industry.
                As complex machinery started to invent, keeping thousands of information about tools are generated. Utilization of those tools with matching different specification leads to a major confusion. Slowly all observation became data in a spreadsheet for a technician or a field engineer.
                Now it needs to be matched with different part of a factory – Inventory, where a technician put its demand and the Inventory manager matches with supply. The calculation of transaction goes to finance and finance calculates the profit and loss.
                To hold this dissimilar gigantic data with a backbone of money flow is to be tracked somewhere. It may be a spreadsheet; it may be a database or may be a relational database. But how a person from management comes to know that why factory A is not creating as much car as factory B?
                Is it the hammer that matter? May be, may not be. It may be the employees working there are depressed and slow down the production because of their low wages. It may be the assembly line don’t have good amount of inventory and the line stops frequently. It may be the suppliers are delaying supplying their small parts.
                Whatever it is, business needs to know. They need to understand the root cause. And have to take decision over Factory A and their employee.
                They have to do it intelligently because the decision makers are accountable to every person falls under an organization hierarchy, sometimes outsiders (shareholders). Their decision needs to be supported by a set of data which is accurate and immediate but based on a statistics over a big set of data which was gathered over a period of time.
                This intelligence is provided by Information Technology with high volume of data in all levels of granularity. The power of analysis with the online day to day data with comparison to historical information slicing and dicing by taking key performance indicator parameters results a solid reason for all the decisions company make. 
                In a line Business Intelligence is set of theories and concepts which understand business completely, focus on a subject area, identify the problem area and provide an intelligence supported by a pure analytical dataset which results to a decision taken for the good of an organization.

Who needs this and who can afford this?
Any industry can use this as a tool to take decision. It may be small or may be a major enterprise. But the cost and expenditure is major as to implement a BI solution you need at least

  •  A Data warehouse to keep your historical data which will be used for data analysis.
  •  A data mart which you will use for reporting
  • An ETL ( Extraction Transformation Load) tool to load the data in data warehouse
  • A reporting tool to see the result data
And all are not very simple to implement and easy to use. So you need many specialized person to build this environment. As result a big enterprise which has a large scale of data and need an robust analytical system can afford this.
Components of BI
Business Intelligence is a concept and not a tool to be composed by components. But as it is a common terminology which is used everywhere that which all can be together call as Component of BI which actually generates intelligence for business.
My definition of BI components holds the below values:

Let’s not go deep into as these will be part of the later article.
Tools used for BI
Business Intelligence is a tool driven system and the tools which are generically in use for creating in BI development are all specialized in particular field.
Though we can find many BI tools in Google specifically used in BI project but I feel that BI needs almost every tool which IT world offers. The key to choose your tool is business requirement. We missed everywhere that requirement gathering in proper manner also need some tools which efficiently track the requirement and asking for solution.
Let me consolidate my past projects used tool where you might find some tools which are normally ignored while creating cost estimation. But those also should have some credit for success of BI.
Requirement Gathering and documenting Tool
Requirement gathering and analysis is the main factor for success of BI project. All the information gathered need to be documented properly and store in some document management system which can be searched and retrieve properly and promptly in time of validation and verification of business rules
·         Microsoft one notes to take instant note and save.
·         Microsoft Visio to create graphical representation of understanding
·         Business Analyst tool to keep the gathered data and create a system workflow.
·         Microsoft SharePoint to create the data repository
·         Screen grabber tool to create snapshot.
·         Audio or video grabbing tool to capture meeting (if organization permit)

Data Modeling tool
After gathering the requirement data need to be modeled properly either following top down approach or bottom approach.
Data modeling tools used to create a conceptual model and then convert into a relation model. Tools are now pretty powerful to create the database from the relational model diagram.
SYBASE Power Designer
RDBMS for Data Warehousing
Data Warehouse and Data marts are the concepts which are nothing but a set of table in a database. To hold that properly modeled data warehouse or DataMart a RDMS is in need which can process a major set of data and a decent throughput from query.
ETL tool for Data loading
Extraction Transformation and load is the mechanism to load data from heterogeneous sources and formats. This specially designed BI tool take cares all the major business rules which need to be implemented in database level.
Informatica, Datastage
Reporting tool for showing reports
Reporting tools are for end user who will see the clean and summarized data by different criteria or parameter. A reporting tool is consist of a semantics layer which holds the business rules which need to implemented in reporting side.
Business Objects, Cognos
Dash boarding tool for showing dashboard
Dashboard is also a reporting environment which provides highly summarized data for KPIs of an industry.
Qlikview, Xcelcius, Crystal report
Portal development tool
Some time it is needed that the application or reporting may not be used directly from the link but from a different company portal. Sometime the portal is present or sometime you need to build that.
Java, .net
Office package
Office package is all along the most important tool in BI. Still there are employees who want to run the whole company using Excel. Users also love to see the report in pdf , xls, doc format. So integration with office package is most important thing in BI reporting
Microsoft Office, Open office, Adobe PDF

Let’s end this article with famous definition of Data Warehouse by Bill Inmon,
A data warehouse is a subject-oriented, integrated, time-variant and non-volatile collection of data in support of management's decision making process.
This is what a BI generally does. It takes a time span of data over a particular subject area and doing a statistical measure it provides a data driven support which leads a management to take fearless decision.

We will discuss this more in other article.