BusinessObjects Topics • View topic - Degenerate dimension

BusinessObjects Topics • View topic - Degenerate dimension Visit

Read more »

Benefits of a Data Warehouse

Benefits of a Data Warehouse

Read more »

What is META DATA?

what is META DATA

visit our new blog www.businessobjects-sapbo.blogspot.com

Read more »

What is OLAP?

What is OLAP
Visit our new site www.businessobjects-sapbo.blogspot.com

Read more »

Inmon vs. Kimball - An Analysis

Mr. William (Bill) Inmon is known as the “Father of Data Warehousing”, entitled for coining the term “Data Warehouse” in 1991. He defined a model to support “single version of the truth” and championed the concept for more than a decade. He also created “Corporate Information Factory” in collaboration with Ms. Claudia Imhoff. Mr. Inmon is known to have published 40+ books and 600+ articles.

Mr. Ralph Kimball is known as the “Father of Business Intelligence” for defining the concept behind “Data Marts”, for developing the science behind the analytical tools that utilize dimensional hierarchies, and for conceptualizing star-schemas and snowflake data structures. He defined a model to support analytical analysis and championed data marts for more than a decade. Though Kimball’s writings do not exceed Inmon’s by quantity, Kimball’s books are all-time best sellers on data warehousing.

Inmon and Kimball are two pioneers that started different philosophies for enterprise-wide information gathering, information management, and analytics for decision support. Inmon believes in creating a single enterprise-wide data warehouse for achieving an overall business intelligence system. Kimball believes in creating several smaller data marts for achieving department-level analysis and reporting.

APPROACHES
Inmon’s philosophy recommends to start with building a large centralized enterprise-wide data warehouse, followed by several satellite databases to serve the analytical needs of departments (later known as “data marts”). Hence, his approach has received the “Top Down” title.

Kimball’s philosophy recommends to start with building several data marts that serve the analytical needs of departments, followed by “virtually” integrating these data marts for consistency through an Information Bus. Hence, his approach received the “Bottom Up” title. Mr. Kimball believes in various data marts that store information in dimensional models to quickly address the needs of various departments and various areas of the enterprise data.

STRUCTURES
Besides the differences in approaches, Inmon and Kimball also differ in the structure of the data. Inmon believes in creating a relational-model (third normal form: 3NF) where as Kimball believes in creating a multi-dimension model (star-schema and snowflakes).

Inmon argues that once the data is in a relational model, it will attain the enterprise-wide consistency which makes it easier to spawn-off the data-marts in dimensional-models. Kimball argues that the actual users can understand, analyze, aggregate, and explore data-inconsistencies in an easier manner if the data is structured in a dimensional-model. Additionally, to enable the Information Bus, data marts are categorized [Imhoff, Mastering Data warehouse design] as atomic data marts, and aggregated data marts that both use dimensional-models.

Irrespective of the structural differences in the model, both Inmon and Kimball agrees that there is a need to separate the detailed-level data from aggregated-level data.

CONTENT
Another difference is in the granularity of the content. Inmon believes that the content in the data warehouse has to be at the most granular level possible and must include all the possible historical data within an enterprise. His argument is that the end-users will mandate the needs on the level of data-detail that are not known at the time of building the data warehouse.

COMMON GOALS
Though Mr. Inmon and Mr. Kimball have different philosophies to their approach, they do tend to agree with each other in an indirect manner. Though Inmon’s basis is on a single data warehouse, he stressed on iterative approach and discouraged the “big bang” approach. On the other hand, though Kimball’s philosophy is to quickly create few successful data marts at a time, he stresses on integration for consistency via an Information Bus.

DATA WAREHOUSE vs. BUSINESS INTELLIGENCE
Business Intelligence = Inmon’s Corporate Data Warehouse + Kimball’s Data Marts + Data Mining + Unstructured Data.

Read more »

Evolution of the BO XI platform – from XI R2 to XI 3.1 SP2

Where were we with XI R2:

• Change to Crystal service-oriented platform (Crystal 10 architecture)
• Ability to plug Crystal Reports, Web Intelligence, Desktop Intelligence, OLAP Intelligence, Dashboard Manager, Performance Manager directly into the framework
• Single repository, security, system management, publishing, portal
• Infoview (Replaced old BO Infoview and Crystal ePortfolio)
• Central Management Console (CMC)
• Import Wizard (upgrades from BO 5, 6, XI, Crystal 8.5, 9, 10)
• Desktop Intelligence (new name for BO full client + ability to query and display Unicode data)
• Publishing, Encyclopedia, Discussions, OLAP Intelligence, Performance Management
• Changes to Data Integrator, Composer, Metadata Manager

XI 3.0

• All administration moved to the Central Management Console – CMC – with new GUI
• Bulk action support in CMC
• Central Configuration Manager – CCM is still there (to manage multiple nodes) with 2 entries : Tomcat & SIA
• Server Intelligence Agent (SIA) – handles service dependencies
• Server Intelligence in CMC – clone server deployments
• Repository Federation – replicate repository on other BO cluster
• Repository Diagnostic Tool (Infostore vs FileStore – repair inconsistencies between CMS database entries and files in FRS)
• Improved Import Wizard
• Web Intelligence Rich Client (offline viewing of WebI reports, no session timeout)
• Data change tracking in Web Intelligence
• Designer – “Database delegated” projection on measures
• Universe based on stored procedures
• Prompt syntax extension (persistent/primary_key undocumented features, finally!)
• Personal data provider – combine data from Excel, text, csv and get into a single report
• Smart cubes – support for non-additive measures (percentages, ratios) and RDBMS analytical functions
• Multi language support – dimensions, measures, prompts automatically localized to report viewer’s language
• Native Web Intelligence printing (without PDF)
• Enbed image in Web Intelligence report
• Hyperlinks dialog box makes links easy to create – syntax generated by WebIntelligence (remember opendocument()?)

What’s new in XI 3.1

• Support for multi-forest Active Directory authentication
• IP v6 support
• Lifecycle Management Tool (LCMBIAR files, replace Import Wizard)
• Saving Web Intelligence documents as CSV (data-only files) – new sheets for every 65K rows of data
• Web Intelligence Autosave
• “Begin_SQL” SQL prefix variable
• Prompt syntax extension (support for key-value pairs!)
• Business Objects Voyager enhancements
• Live Office enhancements
• WebIntelligence – Automatic loading of cached LOVs, interactive drag-drop, report filter bar, cancel refresh-on-open

What’s new in XI 3.1 SP2

• WebIntelligence Input controls
• OLAP universe based access to SAP BW using MDX
• BI services – expose WebIntelligence document components as web services
• Query on Query
• Fold-unfold UI improvements
• New SDKs – WebI Calculation Extension Points, Custom Data Provider plugin, Interactive viewing extension and integration points
• New universe SQL parameter SMART_AGGREGATE allows using most detailed aggregate tables
• @Prompt editor in universe Designer
• Backward compatibility is broken. Web Intelligence documents created using BO XI 3.1 SP2 cannot be opened in BO XI 3.1

Read more »

Data warehouse Concepts

Central Data warehouse

A Central Data Warehouse is a repository of company data where a database is created from operational data extracts. This database adheres to a single, consistent enterprise data model to ensure consistency in decision making support across the company.

Active Metadata Warehouse

An Active Metadata Warehouse is a repository of Metadata to help speed up data reporting and analyses from an active data warehouse. In its most simple definition, a Metadata is data describing data.

Enterprise Data Warehouse:

Enterprise Data Warehouse is a centralized warehouse which provides service for the entire enterprise. A data warehouse is by essence a large repository of historical and current transaction data of an organization. An Enterprise Data Warehouse is a specialized data warehouse which may have several interpretations

Functional Data warehouse

The Enterprise Data Warehouse is broken down into Functional Data Warehouse based one functional Data. Depending on the size of the company and their financial capability, a Functional Data Warehouse may serve on department or may server more. There are also companies that have branches in many different geographic locations around the globe and their Enterprise Data Warehouse may set up differently with different clustering for Functional Data Warehouses.

Operational Data Store

An Operational Data Store (ODS) is an integrated database of operational data. Its sources include legacy systems and it contains current or near term data. An ODS may contain 30 to 60 days of information, while a data warehouse typically contains years of data.

Read more »

ODS Vs. Data Warehouse

ODS Vs. Data Warehouse

Operational Data store

- Data Focused Integration From Transaction Processing Focused Systems

- Age Of The Data is Current, Near Term (Today, Last Week’s)

- Primary Use: Day-To-Day Decisions Tactical Reporting Current Operational
Results
- Frequency Of Load: Twice Daily , Daily, Weekly

Datawarehouse

- Subject Oriented,Integrated,Non-Volatile,Time Variant

- Age of the Data is Historic (Last Month, Qtrly, Five Years)

- Primary Use: Long-Term Decisions Strategic Reporting Trend Detection

- Frequency of load : Weekly, Monthly, Quarterly

Read more »

Differences between OLTP and OLAP databases.

Differences between OLTP and OLAP databases.

Relational Database (OLTP)
Data is atomized
Data is current
Processes many records at a time
Process oriented
Designed for highly structured repetitive processing

Dimensional Database (OLAP)

Data is summarized
Data is historical
Processes one record at a time
Subject oriented
Designed for highly unstructured analytical processing

Read more »

Operational data store (ODS)

Operational data store

A subject-oriented system that is optimized for looking up one or two records at a time for decision making. An operational data store is a hybrid form of data warehouse that contains timely, current, integrated information. The data typically is of a higher level granularity than the transaction. You can use an operational data store for clerical, day-to-day decision making. This data can serve as the common source of data for data warehouses.

Read more »

Data Mart

What is Data mart?

A subset of data warehouse that is stored in a smaller database and that is oriented toward a specific purpose or data subject rather than for enterprise-wide strategic planning. A data mart can contain operational data, summarized data, spatial data, or metadata. Typically, you use a dimensional data model to build a data mart.

Read more »

The Star Schema

A good dimensional data warehouse has a simple database structure. Technically, a simple structure means faster queries. In a dimensional data warehouse, the implementation relational database has two types of tables, the fact table and the dimension table. The fact table consists of the facts (or measures) of the business. Dimension tables contain descriptions for querying the database.

The tables in a data warehouse are related such that the schema looks like a star, hence the term star schema


A star consists of a fact table surrounded by two or more dimension tables. A one-star structure has one fact table only. A multi-star structure has multiple fact tables, one for each star

dimension tables may be shared among multiple fact tables.

In Data warehouse the star structure, the snowflake structure could also be used However, it is more difficult to model than the star structure. As well, the snowflake structure is not that easy to understand and implement and the performance of its queries is slower than that of the star structure. These drawbacks make the snowflake structure unsuitable for dimensional data warehousing.

The Dimension, Fact table used term to suffix the table _dim, _fact.

Every Dimension table has one or more primary key which is reference to fact table. so based on this we can build query to retrieve the date what we required.

Above figure..

The customer_dim (customer dimensional table) is reference the Sales_order_fact table so that we can trace customer information. By using the Product_id in sales_order_fact table, we can retrieve the product information in production_dim table. By joining the sales_order_fact table and the date_dim table on the date_id column, you’ll get the order date. And by joining the fact table and the order_dim table on the order_id column, you obtain the order number.

Read more »

Data warehouse Concepts - Day 2

dimension table:
A dimensional table is a collection of hierarchies and categories along which the user can drill down and drill up. it contains only the textual attributes.

LookUp table

A LookUp table is the one which is used when updating a warehouse. When the lookup is placed on the target table (fact table / warehouse) based upon the primary key of the target, it just updates the table by allowing only new records or updated records based on the lookup condition.

Aggregate table

Aggregate table contains the summary of existing warehouse data which is grouped to certain levels of dimensions.Retrieving the required data from the actual table, which have millions of records will take more time and also affects the server performance.To avoid this we can aggregate the table to certain required level and can use it.This tables reduces the load in the database server and increases the performance of the query and can retrieve the result very fastly

What is ODS?

1. ODS means Operational Data Store.
2. A collection of operation or bases data that is extracted from operation databases and standardized, cleansed, consolidated, transformed, and loaded into an enterprise data architecture. An ODS is used to support data mining of operational data, or as the store for base data that is summarized for a data warehouse. The ODS may also be used to audit the data warehouse to assure summarized and derived data is calculated properly. The ODS may further become the enterprise shared operational database, allowing operational systems that are being reengineered to use the ODS as there operation databases.

Read more »

Data warehouse Concepts - Day 1

Data warehouse :
A Data warehouse is a repository of integrated information, available for queries and analysis. Data and information are extracted from heterogeneous sources as they are generated. This makes it much easier and more efficient to run queries over data that originally came from different sources". Another definition for data warehouse is: " A data warehouse is a logical collection of information gathered from many different operational databases used to create business intelligence that supports business analysis activities and decision-making tasks, primarily, a record of an enterprise's past transactional and operational information, stored in a database designed to favour efficient data analysis and reporting (especially OLAP)". Generally, data warehousing is not meant for current "live" data, although 'virtual' or 'point-to-point' data warehouses can access operational data. A 'real' data warehouse is generally preferred to a virtual DW because stored data has been validated and is set up to provide reliable results to common types of queries used in a business

Data Warehouse is a repository of integrated information, available for queries and analysis. Data and information are extracted from heterogeneous sources as they are generated....This makes it much easier and more efficient to run queries over data that originally came from different sources.
Typical relational databases are designed for on-line transactional processing (OLTP) and do not meet the requirements for effective on-line analytical processing (OLAP). As a result, data warehouses are designed differently than traditional relational databases


Data Warehousing is concerned with the activities involved with creating a DataRepository where you store all the facts you know about some large enterprise or activity, and then analyse the data in different ways,to look for patterns on the data. Typically this is done by large retail organisations, trying to identify patterns in buying behaviour so they can sell more stuff to the poor unsuspecting customer.

Read more »