Faqs on DW - Page4

Why De-normalization is promoted in Universe Designing?

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 De-normalization is promoted in Universe Designing.

What is the difference between ODS and OLTP?

ODS:- It is nothing but a collection of tables created in the Datawarehouse 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

What is the difference between datawarehouse and BI?

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.

Can a dimension table contains numeric values?

Yes. But those data type will be char (only the values can numeric/char)

What is the difference between view and materialized view?

View - store the SQL statement in the database and let you use it as a table. Every time you access the view, the SQL statement executes.

Materialized view - stores the results of the SQL in table form in the database. SQL statement only executes once and after that every time you run the query, the stored result set is used. Pros include quick query results.

What is meant by metadata in context of a Datawarehouse and how it is important?

Meta data is the data about data; Business Analyst or data modeler usually capture information about data - the source (where and how the data is originated), nature of data (char, varchar, nullable, existence, valid values etc) and behavior of data (how it is modified / derived and the life cycle ) in data dictionary a.k.a metadata. Metadata is also presented at the Datamart level, subsets, fact and dimensions, ODS etc. For a DW user, metadata provides vital information for analysis / DSS.

Differences between star and snowflake schemas?

Star schema
A single fact table with N number of Dimension
Snowflake schema
Any dimensions with extended dimensions are know as snowflake schema

Difference between Snow flake and Star Schema. What are situations where Snow flake Schema is better than Star Schema to use and when the opposite is true?

Star schema contains the dimension tables mapped around one or more fact tables.
It is a de-normalized model.
No need to use complicated joins.
Queries results fastly.
Snowflake schema
It is the normalized form of Star schema.
Contains in-depth joins because the tables are splitted into many pieces. We can easily do modification directly in the tables.
We have to use complicated joins, since we have more tables.
There will be some delay in processing the Query.

What is VLDB?

The perception of what constitutes a VLDB continues to grow. A one terabyte database would normally be considered to be a VLDB.

What’s the data types present in bo? What happens if we implement view in the designer n report?

Three different data types: Dimensions, Measure and Detail.
View is nothing but an alias and it can be used to resolve the loops in the universe.