Data Warehouse: Facts and Measures

What is a Fact Table?

A fact table is a table that joins dimension tables with measures.  For example, Lets say you wanted to know the time worked by employees, by location, by project and by task.  If you had a dimension for employees, location, project and task you would create a composite primary key using these foreign keys and add an additional column for the time worked measure. (more on measures in a little bit)

Keep in mind that fact tables are huge unlike dimension tables.  Fact tables are usually built to contain a hundred thousand records on the low side up to billions of records on the high side.  Therefore, these tables must be normalized to be efficient.

Introduction to Dimensions

What is a Dimension Table?

A dimension table provides the description behind the analytic numbers.  It describes the who, what, when, where and why behind the facts. Dimensions are normally broken down into groups (tables) and they contain several attributes (columns).   Unlike a fact table the dimension table is not normalized.  Generally, dimension tables have many columns but a limited amount of rows. 

Dimension tables normally provide two purposes in a data warehouse, it can be used to filter queries and to select data.

Building a Data Warehouse with SQL Server

What is a Data Warehouse?

In its simplest form a Data Warehouse is a way to store data information and facts in an format that is informational.  Hopefully, you were able to pull this information from the photos above.   Personally, I like to think of a Data Warehouse as a tool used by decision makers to improve decision‐making.

5 Faktor Kegagalan Implementasi BI

Tidak semua implementasi Business Intelligence (BI) pasti berhasil di lakukan sesuai dengan yang diharapkan. Beberapa faktor yang dapat mengakibatkan kegagalan yang sangat perlu diperhatikan dan dipertimbangkan antara lain sebagai berikut :

1. Pemahaman Konsep dan Sistem BI yang Salah
   
   Sebelum Anda memutuskan melakukan implementasi BI sebagai aplikasi pendukung keputusan yang sangat strategis, sangat dianjurkan pengguna dapat mengetahui sebelumnya pemahaman konsep-konsep dasar dari BI itu sendiri.
   

set prompt date to default value

Membuat tanggal default prompt date:

drag Date Prompt kedalam body page,
drag HTML Item dan pastekan syntax berikut:

<script language="javascript">
var dDate = new Date();
dDate.setDate(dDate.getDate()-1);
pickerControlStartDate.setValue(getFormatDate(dDate, 0, 'YMD'));
</script>

kemudian klik pada bagian prompt date dan scroll kebawah dibagian Properties
untuk properti Name beri nama: StartDate

Outputnya prompt date akan tampil dengan nilai default tanggal H-1.

createdBy. @aankdavid

step for coloring on grid table

go to Condition Explorer >
create Boolean Variable and paste below function on expression definition:
mod (RowNumber (),2)

go to Page Explorer >
select cell on crosstab
on properties, define Style Variable >
chose Variable Boolean1

chose 'Yes' on Boolean1 when you click Condition Explorer
and change background color to red

chose 'No' on Boolean1 when you click Condition Explorer
and change background color to pink

finish.

createdBy. @aankdavid

BI Reporting

Business Intelligence Reports

Data is collected from various sources and cleansed or transformed into a form required by data warehouse or data mart decision support systems. BI tools provide a means of taking advantage of these data contained within a data warehouse or data mart by providing answer to critical queries related to the business.

The power of business intelligence actually relies on the knowledge acquired through the analysis of various reports taken using business intelligence reporting tools. Business Intelligence Reporting Tools or OLAP Tools provide different views of data by pivotting or rotating the data across several dimensions. BI tools are capable of displaying data in several formats like, tables, pivots, charts, query results or reports. 

Zero Footprint Technology

Earlier, with client/server architecture, business intelligence reporting tools were installed on each user's desktop in order to connect to the database and to take reports. Nowadays, with the revolution of web advancement, Zero Footprint technology allows users to connect to the database and see the reports through web browsers which doesn't require them to install any software to do so. With the help of this Zero Footprint technology, the reports that were only availabale to top executives, now can be seen by any employee making them to participate and contribute towards the decision making.

source: learnbi.com

ETL Process & Data Warehouse

ETL, an acronym for 'Extraction, Transformation and Loading' is a collection of processes associated with extracting the source data, transforming that data and finally loading that data into a data warehouse. Before loading the required into data warehouse, it should be transformed in order to meet the needs of the data warehouse. This transformation involves several processes like data cleansing, data profiling, data type conversion, validating for referential integrity, performing aggregation if needed, denormalization and normalization.

Data Warehouse

Data warehouse is a centralized repository where all the information for analysis is kept in an organization. This is the data collected from variant sources for the purpose of analytical processing and reporting. This data is non-volatile and a data warehouse is built on a dimensional data model. From this data warehouse, data can be extracted for reporting needs with the help of query tools or many data marts can be built based on subject area requirements.

Data Mart

Data Mart is subject oriented, basically a sub-set of data warehouse, built for the purpose of analyzing a particular line of business or department. It holds the data specific to a particular subject area like sales, purchase etc. Data marts can be of derived from a data warehouse or built for the sole purpose of BI directly from the source and like data warehouse, data marts are also constructed from dimensional data models.

source: learnbi.com

Star Schema

In a Dimensional Data Model, a fact table is the centralized table which is connected to multiple dimensions related to that fact. This type of approach is known as the Star Schema design based on which data warehouse and data marts are built. Since BI takes advantage of data displayed in the form of mutli-dimensional cubes, this star schema approach helps analyze and produce complex reports very easy by slicing and dicing the dimensions of interest.

From the sample diagram shown below, the required Fact 'Loan Amount' can be calculated across various dimensions like state, branch, time, product, loan officer and investor dimensions.

Sample Star Schema Diagram

source: learnbi.com

Dimensional Modeling

Dimensional Model comprises a fact table and many dimension tables and is used for calculating summarized data. Since Business Intelligence reports are used in measuring the facts(aggregates) across multiple dimensions, dimensional data modeling is the prefered modeling technique in a BI environment. A Fact table contains various measures or facts like sales amount, loan amount etc., whereas a Dimension table describes the particular entity like time, state etc., based on which the required facts are measured.

source: learnbi.com

Data Modeling

Data Modeling is about representing the real world set of data structures or entities and their relationships in the form of Data Models, required for a database. Simply put, data model is a visual representaion of the database. 

Data Modeling consists of various types and phases like conceptual data modeling, logical data modeling, physical data modeling, enterprise data modeling, relational data modeling and dimensional data modeling.

Conceptual Data Modeling

Conceptual Data Modeling visualizes the overall structure of the database and provides high-level information about the subject areas or data structures of an organization and it does not contain much detailed level of information about attributes.

Logical Data Modeling

Logical Data Modeling is an extension to Conceptual Data Modeling and its includes almost all of the entities, attributes and their relationship. A logical Data Model will not contain any attribute specific information like type, length etc., instead it defines and conveys business information and rules.

Physical Data Modeling

Physical Data Model includes data structures like tables, columns, properties and the relationship between them.

Enterprise Data Modeling

Enterprise Data Modeling is known as global business model as it consolidates the information across the enterprise.

Relational Data Modeling

Relational Data Model revolves around Entity-Relationship Modeling where entities(tables) are normalized to avoid possible redundancy and this type is the prefered technique in OLTP.

source: learnbi.com

Business Modeling

Business Modeling depicts the overall picture of a business like what is it about, what specific business problem it is intend to solve and how the information flows from source to destination. Business modeling involves, business models and diagrams that provide information in a graphical way to the members of an organization to understand and communicate the business rules and processes effectively. Business process modeling, process flow modeling and data flow modeling are sub-categories of business modeling. It is not necessary for the business modeling to go into the details of the project and often it will hide the programming complexities required to achieve the task. Business Modeling strategies and the underlying business models differ from one organization to other depending upon their needs and goals.

Business Process Modeling

A business process modeling is a group of related activities or business processes. Business processes are visually represented as diagrams of simple box with arrow graphics and text labels, better known as Business Process Models.

Process Flow Modeling

Process Flow Modeling is used to graphically describe the various processes that happen in an organization and the relationships between them.

source: learnbi.com

BI Environment

Business Intelligence is all about converting large amounts of corporate data into useful information, thereby triggering some profitable business action with the help of knowledge acquired through BI analysis. Implementing BI is a long process and it requires a lot of analysis and investment. A typical BI environment involves business models, data models, data sources, ETL, tools needed to transform and organize the data into useful information, target data warehouse, data marts, OLAP anaysis and reporting tools.

Setting up a Business Intelligence environment not only rely on tools, techniques and processes, it also requires skilled business people to carefully drive these in the right direction. Care should be taken in understanding the business requirements, setting up the targets, analysing and defining the various processes associated with these, determining what kind of data needed to be analysed, determining the source and target for that data, defining how to integrate that data for BI analysis and determining and gathering the tools and techniques to achieve this goal. 

Following sections explain each of these areas in detail and the sample figure shows a BI environment in its simplest form. 

Sample BI Environment:

Business Intelligence Architecture

source: learnbi.com

What is Business Intelligence?

Business Intelligence(BI) is a terminology representing a collection of processes, tools and technologies helpful in achieving more profit by considerably improving the productivity, sales and service of an enterprise.

With the help of BI methods, the corporate data can be organized, analyzed in a better way and then converted into an useful knowledge of information needed to initiate a profitable business action. Thus its about turning a raw, collected data into an intelligent information by analyzing and re-arranging the data according to the relationships between the data items by knowing what data to collect and manage and in what context. 

Importance of Business Intelligence:

A company's collected raw data is an important asset where one can find solutions to many of an organisation's critical questions like 'what was the net profit for a particular product last year and what will be sales this year and what are the key factors to be focussed this year in order to increase the sales?'. So there arises a necessity of a well planned BI system which can lead to a greater profitability by reducing the operating costs, increasing the sales and thereby improving the customer satisfaction for an enterprise.

With the help of a Business Intelligence System, a company may improve its business or rule over its competitors by exploring and exploiting its data to know the customer preferences, nature of customers, supply chains, geographical influences, pricings and how to increase its overall business efficiency.

source: learnbi.com

Configuration IBM Cognos on Linux

How to configure IBM Cognos 8.3 BI Server on Linux environments

[root@DL01VMBISSR linuxi386]# cd ../..

[root@DL01VMBISSR cognos]# cd c8/bin/
[root@DL01VMBISSR bin]# ./cogconfig.sh 
Using /app/jdk1.6.0_27/jre/bin/java
[root@DL01VMBISSR bin]# 

Installation IBM Cognos on Linux

How to install IBM Cognos 8.3 BI Server on Linux environments
OS: Linux Redhat 64bit
Java32bit: /app/jdk1.6.0_27

Apache: /app/httpd-2.2.21

Oracleclient32bit: /app/oraclient/product/11.2.0/client/

[root@DL01VMBISSR sources]# ls

C1WE9ML.tar.gz  documentation  linuxi386  zipfiles
[root@DL01VMBISSR sources]# cd linuxi386/
[root@DL01VMBISSR linuxi386]# ls

Business intelligence

Business intelligence (BI) mainly refers to computer-based techniques used in identifying, extracting, and analyzing business data, such as sales revenue by products and/or departments, or by associated costs and incomes.

BI technologies provide historical, current and predictive views of business operations. Common functions of business intelligence technologies are reporting, online analytical processing, analytics,data mining, process mining, complex event processing, business performance management, benchmarking, text mining and predictive analytics.

Business intelligence aims to support better business decision-making. Thus a BI system can be called a decision support system (DSS). Though the term business intelligence is sometimes used as a synonym for competitive intelligence, because they both support decision making, BI uses technologies, processes, and applications to analyze mostly internal, structured data and business processes while competitive intelligence gathers, analyzes and disseminates information with a topical focus on company competitors. Business intelligence understood broadly can include the subset of competitive intelligence.

From Wikipedia, the free encyclopedia