Data Warehousing Concepts

Here are a few basic questions we must understand and learn in order to Maintain a descent level understanding regarding basic data warehousing concepts.

1.Define data warehouse

Data warehouse is a relational database that is designed for query and analysis rather than for transaction processing.It is termed as corporate memory, though it usually contains historical data that is derived from transaction data, but can include data from other sources as well. It separates analysis workload from transaction workload and enables a business to consolidate data from several sources.

2.What are Different types of data warehouses?

According To William Inmon There Are 4 Types Of Data Warehouse

Subject Oriented: Data warehouse are designed to help you analyze data. For example, to learn more about your company sales data, you can build a warehouse that concentrates on sales. Using this warehouse, you can answer the questions like “who was our best customer for this item last year?” This ability to define data warehouse by subject matter, sales in this case makes the data warehouse subject oriented.

Integrated: Integration is closely related to subject orientation. Data warehouse must put data from disparate sources into a consistent format. They must resolve such problems as naming conflicts and inconsistencies among the units of measures. When they achieve this they are said to be integrated.

Nonvolatile : Nonvolatile means that once entered into the warehouse, data should not be change. This is logical because the purpose of a warehouse is to enable you to analyze what has occurred and whatever once happened never changes.

Time variant: In order to discover trends in business, analysts need large amounts of data. This is very much in contrast to online transaction processing (OLTP) systems, where performance requirements demands that historical data be moved to an archive. A data warehouse’s focus on change over time is what is meant by the term time variant.

3.Define Fact-table

Fact Table: Fact table contains the measurements or metrics or facts of business processes. If your business process is Sales, then a measurement of this business process such as "monthly sales number" is captured in the fact table. In addition to the measurements, the only other things a fact table contains are foreign keys for the dimension tables.

Fact table contains the actual business process measurements or metrics called facts. Usually these facts are numeric.

These facts are generally Additive.

Sometimes the facts are semi additive such as balances

Sometimes they are non-additive such as unit price

4.Define Dimension-table

Dimension Table

Dimension table contains the textual description of a business. It means context of the measurements are represented in dimension tables. You can also think of the context of a measurement as the characteristics such as who, what, where, when, how of a measurement (subject). In your business process Sales, the characteristics of the 'monthly sales number' measurement can be a Location (Where), Time (When), Product Sold (What).

5.What do you mean by Detail in a table?

Details are the dimension beyond which an analyst is not interested to analyze. A detail is always a dimension. If we drill down region dimension table as region-sub region-country-state-city in that case city is detail being the last dimension.

6.What is Measures?

Special dimension that contains measurable quantities upon which the business is measured. They are always numeric quantities. They are the ones on which we perform aggregations. It includes cost, profit, or taxes.

7.What is a critical column?

The huge collection of data within a warehouse is useful for analysis only if the data in it is consistent. However there exists some information which when updated in a warehouse makes the data in a warehouse inconsistent. The column that contains such vital information is identified as critical columns.

9.What is Surrogate-key?

A surrogate key to a data warehouse is what a primary key is for an OLTP source. As a possible solution for a critical column we use surrogate keys by inserting a new record as and when a critical column gets updated at an OLTP source. However it violates the primary key constraint that might have been specified for the column Customer ID. Hence we no longer maintain the Customer ID as a primary key but we have one more new column acting as the so called surrogate keys which typically contains a value generated from a sequence.

So whenever a customer changes city, we insert a new record into our warehouse with the same Customer ID but with a different surrogate key. Customer’s latest city can be identified by the largest surrogate key value for that customer.

8.Define Primary-key

Each row in a dimension table is identified by a unique value of an attribute designated as the primary key of the dimension. In a product dimension table, the primary key identifies each product uniquely.

10.Define Foreign-key

Each dimension table is in a one-to-many relationship with the central fact table. So the

Primary key of each dimension table must be a foreign key in the fact table. If there are

Four dimension tables of product, date, customer, and sales representative, then the primary key of each of these four tables must be present in the orders fact table as foreign keys.

11.What Is A Star Schema?

Star schema is highly-denormalised. As it looks like a STAR. Hence it is also known as Star Schema.

Advantages of Star Schema

Star Schema is very easy to understand, even for non-technical business managers

Star Schema provides better performance and smaller query times

Star schema is easily extensible and will handle future changes easily

12.What is a snowflake schema?

Snowflake-Schema-Design: this looks like a snow flake, hence this type of schema is called Snowflake Schema.  General rule of thumb is keep away from snow flake schemas as even though they may save you some space, they will cost a lot in terms of query times. It is highly-normalized

 

 

13.What is the major difference between star and snowflake schema and on what basis the preference is made between the two?

The major difference between the two is a star schema is highly de-normalized whereas snowflake schema is partially normalized.

Snow flake schema is preferred when a data warehouse is most of the time used as a source for one more rather high end data warehouse whereas star schema is preferred when a data warehouse is used for direct analysis.

14.What is metadata

Meta data is to warehouse what a card catalogue is to traditional library. We can say it is Data about data means data that describes data and other structures, such as objects, business rules, and processes. For example, the schema design of a data warehouse is typically stored in a repository as metadata, which is used to generate scripts used to build and populate the data warehouse. A repository contains metadata.

15.What are various types of of metadata

There are three main types of metadata:• Descriptive metadata describes a resource for purposes such as discovery and identification. It can include elements such as title, abstract, author, and keywords.

• Structural metadata indicates how compound objects are put together, for example, how pages are ordered to form chapters.• Administrative metadata provides information to help manage a resource, such as when and how it was created, file type and other technical information, and who can access it.

There are several subsets of administrative data; two that are sometimes listed as separate metadata types are:

− Rights management metadata, which deals with intellectual property rights,
and
− Preservation metadata, which contains information needed to archive and preserve a resource.

16.What is the difference between ODS and OLTP

ODS is an Operational Data System, which contains the snapshot of currently loaded data for clerical day to day analysis and includes a little history, 30-60 days of historical data in addition to the current data. It is nothing but a collection of tables created in the Data warehouse that maintains only current data. Having data with Data warehouse that will be stand alone. No further transaction will take place for current data which is part of the data ware house. Current data will be change once you upload through ETL on schedule basis. 

OLTP is Online Transaction Processing System, which contains only the current data and keeps changing, not suitable for analysis due to temporal inconsistency unlike ODS which contains consistent data. It maintains the data only for transactions; these are designed for recording daily operations and transactions of a business. Having data with on line system which connected to network and all update on transaction happened in seconds. Every second data summarized value will get changed.

17.What is snapshot?

You can disconnect the report from the catalog to which it is attached by saving the report with a snapshot of the data. However, you must reconnect to the catalog if you want to refresh the data.

It’s a permanent local copy of the data in a report which can be used to create the reports. Snapshot is static data source, it is permanent local copy or picture of a report, and it is suitable for disconnected networks. We can't add any columns to snapshot. We can sort, grouping and aggregations.it is mainly use for analyze the historical data

18.What is the difference between datawarehouse and BI?

DATAWAREHOUSE: To make Business Analysis effective and efficient we require specialized form of storage. This special form of storage of data is called Data Warehouse and the process Data Warehousing. Data warehouse is integrated, time-variant, subject oriented and non-volatile collection data in support of management decision making process. Data warehouse is a centralized database. Extract data from desperate systems; transform the data into one single format and load this transformed data into target/data warehouse.

BUSINESS INTELLIGENCE: Business Intelligence is the process of extracting the data, converting it into information and then into knowledge base is known as Business Intelligence BI is the mechanism of using data according to type of industry for predictive analysis, fault findings, process improvement etc. business intelligence is a group application which is used to gather information, providing access to, analyze the information and proving this information to end user to take timely decisions.

19.What is the difference between OLAP and datawarehouse

Don't get confused; OLAP and Data warehouse are the terms that can be used interchangeably in some places. Online Analytical Processing is a concept whereas Data warehouse is the collection of data in denormalized form so that it can help in fast and frequent analysis.

DW:

• Data from different source systems is stored in a relational database for end use analysis.
• Data is organized in summarized, aggregated, subject-oriented, non-volatile pattern.
• Data in a DW is consolidated, flexible collection of data, which supports analysis of data, but not online analysis of data.

OLAP:

• A tool to evaluate & analyze data in a DW using analytical queries.
• A tool which helps organizes data in a DW using multidimensional models.
• Supports analysis in real time & online analysis with speed and flexibility.

20.What do you mean by data granularity?

Granularity or Grain of Fact Table

The level of detail of the fact table is known as the grain of the fact table. In information management, granularity refers to the degree of detail or precision contained in data. In another example the grain of your sales fact table is monthly sales number per location per product.

21.What is Multidimensional database

Multidimensional databases consolidate and calculate data to provide different views. With Multidimensional databases users can pivot the data to see the information from different view point. It contains Outlines that defines all the elements of the database.

22.What is active data warehousing?

Active Data ware house means. Every user can access the database any time 24/7 that is called Active dwh

An active data warehouse provides information that enables decision-makers within an organization to manage customer relationships nimbly, efficiently and proactively. Active data warehousing is all about integrating advanced decision support with day-to-day-even minute-to-minute-decision making in a way that increases quality of those customer touches which encourages customer loyalty and thus secure an organization's bottom line.

23.What do you mean by data marts?

Data Mart is the subset of data warehouse. You can also consider datamart holds the data of one subject area. For an example, you consider an organization that has HR, Finance, Communications and Corporate Service divisions. For each division you can create a datamart. The historical data will be stored into datamarts first and then exported to data warehouse finally.

24.Is OLAP databases are called decision support system ?

Yes, OLAP databases are called decision support systems because it helps in analyzing the historical and present data of the organizations and thereby helping in taking intelligent business decisions

25.What are additive/non-additive/semi-additive facts?

There are basically three types of Facts:
1) Additive Facts
2) Non- Additive Facts
3) Semi-Additive facts

Additive facts -Additive facts are those facts which we can aggregate or we can add. For e.g. 2+7=9.Dollar value is additive fact. If we want to find out the amount for a particular place for a particular period of time, we can add the dollar amounts and come up with the total amount.

Non-additive facts-A fact may be measure, metric or a dollar value. Measure and metric are non-additive facts. Non-additive Fact tables in which data in columns data cannot be aggregated or calculations can’t be possible for producing the expected results or known as non-additive facts. Examples are Ratios, Averages & Variance facts
A non-additive fact, for e.g. measure height(s) for 'citizens by geographical location' , when we rollup 'city' data to 'state' level data we should not add heights of the citizens rather we may want to use it to derive 'count'

Semi-additive facts are those facts which we can aggregate according to the context. If you want to sum up a measure for some dimensions but not for others then it became your semi-additive. And while creating a cube you take care of this thing. Like "balances".

26.What is fact less fact table? Where you have used it in your project?

Fact less table means only the key available in the Fact there is no measures available Used only to put relation between the elements of various dimensions. It contains nothing but dimensional keys. It is used to support negative analysis report. For example a Store that did not sell a product for a given period.

27.Explain in detail about
type 1,
type 2(SCD),
type 3 ?

Type1: Rows containing changes to existing dimensions are updated in the target by overwriting the existing dimension. In the Type 1 Dimension mapping, all rows contain current dimension data. Use the Type 1 Dimension mapping to update a slowly changing dimension table when you do not need to keep any previous versions of dimensions in the table.

Type 2: The Type 2 Dimension Data mapping inserts both new and changed dimensions into the target. Changes are tracked in the target table by versioning the primary key and creating a version number for each dimension in the table. Use the Type 2 Dimension/Version Data mapping to update a slowly changing dimension table when you want to keep a full history of dimension data in the table. Version numbers and versioned primary keys track the order of changes to each dimension.

Type 3: The Type 3 Dimension mapping filters source rows based on user-defined comparisons and inserts only those found to be new dimensions to the target. Rows containing changes to existing dimensions are updated in the target.

28.What is ER-Modeling and Dimensional modeling?

E-R modeling is the relation between entities in the form of normalization whereas dimensional modeling is the relation between dimensions in the form of de-normalization. So E-R modeling is entity relation between two tables.
Dimension modeling is contain three table Aggregate, Fact table, Dimension table

ER modeling:
  - focused how data will be efficient for processing (insert, update, delete)
  - Minimalize (limit to zero) data redundancies

Dimensional modeling:
  - focused how data will be efficient for retrieving
    (example, by report and analysis tools).
  - Many data redundancies
  - Consist of Fact and Dimension table

29.How many types of facts and what are they?

There are 6 types of facts theses are as follows: Fact less Facts:Facts without any measures.Additive Facts:Fact data that can be additive/aggregative.Non-Additive facts: Facts that are result of non-additionSemi-Additive Facts: Only few columns data can be added.Periodic Facts: That stores only one row per transaction that happened over a period of time.Accumulating Fact: stores row for entire lifetime of event.

30.What is the main difference between Inmon and Kimball philosophies of data warehousing?

Both differed in the concept of building the data warehouse...i.e.

Kimball--First DataMarts--Combined way ---Data warehouse

Inmon---First Data warehouse--Later----Datamarts

Kimball views data warehousing as a constituency of data marts. Data marts are focused on delivering business objectives for departments in the organization. And the data warehouse is a conformed dimension of the data marts. Hence a unified view of the enterprise can be obtained from the dimension modeling on a local departmental level. He follows bottom-up approach i.e., first creates individual Data Marts from the existing sources and then create Data Warehouse.

Inmon believes in creating a data warehouse on a subject-by-subject area basis. Hence the development of the data warehouse can start with data from the online store. Other subject areas can be added to the data warehouse as their needs arise. Point-of-sale (POS) data can be added later if management decides it is necessary. He follows top-down approach i.e., first creates Data Warehouse from the existing sources and then create individual Data Marts.  

31.What is materialized view

A pre-computed table comprising aggregated or joined data from fact and possibly dimension tables is known as materialized view. It is also known as a summary or aggregate table.

32.What is normalization

In a relational database, the process of removing redundancy in data by separating the data into multiple tables.

33.What do you mean by parallelism?

Breaking down of a task so that several processes do part of the work. When multiple CPUs each do their portion simultaneously, very large performance gains are possible.

34. What is a Staging Area?

We need to clean and process our operational data before putting it into the warehouse. These operations are done at a special dedicated area wherein all the data from all the sources are first dumped off; this area is known as staging area.

35. Guys At the end we must understand and will be able to draw the architecture of a warehouse as needed. So just go through it…

Data Warehouse Architecture (Basic)

Figure below shows a simple architecture for a data warehouse. End users directly access data derived from several source systems through the data warehouse.

Here in this figure the metadata and raw data and summary data of a traditional OLTP system is present. Summaries are very valuable in data warehouses because they pre-compute long operations in advance. For example, a typical data warehouse query is to retrieve something like August sales.

Architecture of a Data Warehouse (Basic)

Data Warehouse Architecture (with a Staging Area)

In this figure staging area is included as we know we need to clean and process our operational data before putting it into the warehouse. These operations are done at a special dedicated area wherein all the data from all the sources are first dumped off, well known as staging area. A staging area simplifies building summaries and general warehouse management.

Figure1. Architecture of a Data Warehouse with a Staging Area

Data Warehouse Architecture (with a Staging Area and Data Marts)

You may want to customize your warehouse's architecture for different groups within your organization. You can do this by adding data marts, which are systems designed for a particular line of business. Figure below illustrates an example where purchasing, sales, and inventories are separated. In this example, a financial analyst might want to analyze historical data for purchases and sales.

Figure2. Architecture of a Data Warehouse with a Staging Area and Data Marts

Cognos Framework Manager Best Practices

                                 Cognos FM Best Practices

There are a number of best practices that should be followed when building models using Framework Manager:

1. Never allow Cognos to create joins automatically when importing data sources. Cognos rarely chooses correctly and this inevitably creates future rework. Creating joins manually gives full control and avoids cardinality and join issues.
2. Always separate your model into the following three namespaces. Separating into three layers may seem difficult initially, but this organization makes the model easier to maintain. If the underlying data structures change, you will only need to reflect these changes in a single location.
   1. Database Layer Namespace – This namespace simply contains all the query subjects brought in from your data source. No joins or renaming of query items occur in this layer. By leaving this layer untouched, future model changes will only need to be changed in this layer. All other layers can remain untouched.
   2. Logical Layer Namespace – Relationships between query subjects should be created in this layer. If query subjects need to be merged, the merge should be created here. Any model filters should be applied and query subjects and query items should be translated to business names in this layer. All query subjects in the Logical layer should reference back to the database layer.
   3. Presentation Layer Namespace – This layer contains shortcuts to the logical namespace and organizes the data for the business user.
3. Assign query usage in the database layer. Understanding the query usage property and setting it in this layer will ensure that it is always set correctly throughout the rest of the model. Each query subject that is created from the base layer will inherit these properties.
4. Never expose query subjects to the user that could result in cross product query. As an additional precaution, use the governor setting to deny cross product queries.
5. Use the governor settings to limit the query execution time. This will prevent users from developing run-away queries that can impact database performance.
6. Maintain a history of your models using a source control system. Framework Manager has direct connections to Visual SourceSafe and CVS- be sure to take advantage of this feature. CVS is an open source versioning system so cost should not be an excuse!.
7. When modeling your data, avoid creating situations where multiple join paths or loop joins can occur. To prevent these situations, try aliasing the query subject multiple times to force a single path through the data.
8. If possible, try to model in a star/snowflake schema. In a perfect world, this is done for you in the data model and the underlying ETL processes, but in reality we rarely get this lucky. It is possible to create a virtual star schema by merging query subjects to de-normalize data and creating new query subjects that contain only measures. The drawback of this method is that there can be significant performance impacts. Understanding the queries that are being created against the data is critical to making a good design decision here.
9. Set security at the highest level allowable, starting with the package, then the object and if needed the data. Applying security at a high level and then drilling down to more detailed security levels will save maintenance and troubleshooting time. Keep security as simple as possible while still meeting the security requirements and you will thank yourself at the end of the day.
10. Use data level security settings in Framework Manager only when absolutely necessary. Data security is a powerful feature but maintenance and troubleshooting with row level security become complex quickly. If you must use it, make sure to have it well documented for future developers and administrators. If you do choose to use data level security, be aware that it supercedes all other security settings in Cognos Connection.
11. Check cardinality for accuracy and test cardinality to make sure it is behaving as expected. Avoid many to many relationships in your model. If you cannot avoid them, organize data so that the user is less likely to create a report with a many to many relationship.
12. Name query subjects and query items using business terms. As simple as this seems, many organizations leave the original table and column names, creating confusion for the end user.
13. Only publish data that is in the presentation layer, and only expose query subjects that are useful for the end user.
14. Document the model. This is another simple task that is often overlooked,or more likely skipped to save time. Even if the model developer will be maintaining the model, six months from deployment the developer will not remember all the details of the model. Proper documentation expedites making future changes and fixing bugs Document each layer, the joins associated, and any calculations or merges that were done.

Cognos 10.2.2 Release Features

                                Cognos 10.2.2 New Features
1.    Pre-Defined Templates and Style References
A template is a report without metadata from which other reports can be created against any package in order to expedite and standardize the development process

2.    Tabs for Report Output
Allow the report author to specify whether report pages should function as pages or as tabs in the report output

3.    Dynamic Cubes Hardware Sizing Wizard
The Hardware Sizing wizard helps take the guesswork out of allocating memory, CPU, and hard disk to your dynamic cubes. Simply right click on your cube to open the Input Dialog where you will enter information about your cube, such as the number of users, members, and attributes.

4.    Live Preview of Active Report
With the new Active Report live preview feature within Report Studio, you no longer have to download an MHT file every time you want to view and test and Active Report. You can now preview your report directly from Report Studio with the click of a button.

5.    My Data Sets Feature
The new My Data Sets feature allows users to do more robust analyses of data that lives outside of a Cognos framework model or cube. This feature allows end users to import their own Excel or text files, which are turned into Cognos packages that the user can then use for analysis or reporting.
My Data Sets differs from the current External Data feature in three main ways
•    First, with My Data Sets, much higher volumes of Excel and text data can be imported
•    Second, this data now persists on the Cognos server for as long as the user or administrator specifies.
•    Finally, rather than being incorporated into an existing Cognos package, My Data Sets are created as standalone packages.

6.    Extensible Visualization Enhancements
New Extensible Visualizations
•    Chord chart
This visualizes relationships between categories by the thickness of the line between the categories
•    Combination chart
Allows for both bar and line chart, leveraging different axes, to be displayed on the same visual.
•    Tornado chart
It is a bar chart where categories are listed vertically, typically with two metrics to be compared to one another.

7.    Cube Designer Enhancements
•    Custom Relative Time
The new Custom Relative Time ability allows the modeler to establish custom relative time members in Cube Designer using the same properties available in Transformer
•    Import Framework Model Metadata to Cube Designer
Another new feature in Cube Designer is the ability to import Framework Manager Metadata directly and use it as the basis for a Dynamic Cube.

8.    In Memory Aggregates
•    User Defined In Memory Aggregates
User Defined In Memory Aggregates allow the Cognos administrator to define new aggregates manually, rather than relying solely on what the Aggregate Advisor recommends
•    Autonomic Optimization of In Memory Aggregates
Enabling autonomic optimization of in memory aggregates on a dynamic cube will allow the cube to periodically run a limited version of the Aggregate Advisor in the background while the cube is running.

9.    Framework Manager Enhancements
•    Bridge Query Subjects
The new Bridge Query Subject in Framework Manager allows the modeler to now simply set the usage property of a query subject to ‘Bridge’ while maintaining a one to many relationship and Cognos will identify this as a bridge table rather than a fact table
•    Filter Join Optimization
This new feature allows Cognos to filter one side of a join using the values retrieved by the other side
•    Parameter Map Refresh
These values were previously cached for the duration of a user’s session, now you can specify down to the second, how long the values will be cached.

•    Summary Query Subjects
New Summary Query Subjects in Framework Manager allow you to specify grouping and aggregation in the framework model query subject

10.    User Interface Profiles
This tool allows administrators to customize the UI elements and default behaviors for each studio in Cognos. Once enabled, administrators will see the User Interface Profiles section under the Library tab.

Google Map in Cognos Report

Use Google Map into Cognos Report

• Launch Report Studio. When prompted, select the Go Sales(query) package and a new list report template.
• Expand the Sales (query) namespace and drag Country, City etc.

• From the Insertable Objects tab, drag 4 HTML items into the City data item (unlock cell first) and 1 HTML where you want to show Map.

• Open HTML items and insert the mentioned code.
• HTML1: <div onClick="AddMarker('
• HTML2: Change the Source type to “Report Expression” and drag City from query.

• HTML3: ')">
• HTML4: </div>

• Click Ok.
• HTML5:

<script type="text/javascript" src="http://maps.google.com/maps/api/js?sensor=false"></script>

<div id="map" style="width: 700px; height: 400px"></div>

<script type="text/javascript">

var latlng = new google.maps.LatLng(40.756, -73.986);

var options = {

center : latlng,

zoom : 1,

mapTypeId : google.maps.MapTypeId.ROADMAP

};

// Creating the map

var map = new google.maps.Map(document.getElementById('map'), options);

var geocoder = new google.maps.Geocoder();

var marker= new google.maps.Marker(null);

function AddMarker(address)

{

geocoder.geocode( {'address' : address}, function(results, status)

{

if (status == google.maps.GeocoderStatus.OK)

{

//map.setCenter(results[0].geometry.location);

var marker = new google.maps.Marker( {map : map,position : results[0].geometry.location });

var infowindow;

if (!infowindow)

{

infowindow = new google.maps.InfoWindow();

}

infowindow.setContent(address);

google.maps.event.addListener(marker, 'click', function()

{

infowindow.open(map,marker);

});

}

});

}

</script>

• Run the report, it will generate the output like below.

• Click on any City to get marker.