Data Warehouse Concepts

In a relational data model, for normalization purposes, some lookup tables are not merged as a single table. In a dimensional data modeling(star schema), these tables would be merged as a single table called DIMENSION table for performance and slicing data.Due to this merging of tables into one large Dimension table, it comes out of complex intermediate joins. Dimension tables are directly joined to Fact tables.Though, redundancy of data occurs in DIMENSION table, size of DIMENSION table is 15% only when compared to FACT table. So only Denormalization is promoted in Universe Desinging.

Explain yourself

Cube is used in DWH foar representing multidimensional data logically.

A fact may be measure, metric or a dollar value. Measure and metric are non additive facts.

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.

A non additive fact, for eg 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'

which cant be summed up with any columns in the table(all dimension keys)

EX: ratio columns,profit margin

Datawarehouse is the place where the data is stored for analyzing

where as OLAP is the process of analyzing the data,managing aggregations,

partitioning information into cubes for indepth visualization.

ODS:- A collection of tables created in the Data warehouse that maintains only current data.

OLTP:- Maintains the data only for transactions, these are designed for recording daily operations and transactions of a business.

True

OLAP (online analytical processing) works by analysing aggregated data to give final reports to top management to take action/decisions on business which is same as DSS.(Decision support system)

ODS:- It is nothing but a collection of tables created in the Data warehouse that maintains only current data.

Where as OLTP maintains the data only for transactions,these are designed for recording daily operations and transactions of a business.

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. The marketplace is coming of age as we progress from first-generation "passive" decision-support systems to current- and next-generation "active" data warehouse implementations

Simply speaking, BI is the capability of analyzing the data of a datawarehouse in advantage of that business. A BI tool analyzes the data of a datawarehouse and to come into some business decision depending on the result of the analysis.

Business Intelligence is a collection of broad category of application programs and techniques used to querying,retrieving,reporting and analyzing the business informations multidimentionally.

Business Intelligence is a collection of application specifications which allow the client applications to retrieve business informations from the DataWare House in order to make some business decissions.

Data warehousing system is used in OLAP systems. Systems in which mainly the analysis of the data is needed. High level and Top executives use this system for analysis purpose so that they can make correct decisions that can boost the productivity of the org.

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.

Aggregate table contains summarized data. The materialized view are aggregated tables.

for ex in sales we have only date transaction. if we want to create a report like sales by product per year. in such cases we aggregate the date?vales into week_agg, month_agg, quarter_agg, year_agg. to retrive date from this tables we use @aggrtegate function.

aggregate table is one of the data transaction function and some time it is create a protect per year.this aggregate value is week agg ,month agg quarter agg function

Fact less table means only the key available in the Fact there is no measures available.

Fact less fact table means it does not contain any facts(measures).It is used when we are integrating fact tables.