Faqs on DW - Page5

Can a dimension able to contain numeric values?

Yes. But those datatype 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 aggregate table and aggregate fact table? Any examples of both?

Aggregate table contains summarized data. The materialized views 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 values into week_agg, month_agg, quarter_agg, year_agg. To retrieve data from these tables we use @aggregate function.

What is active data warehousing?

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

What is the main difference between schema in RDBMS and schemas in Datawarehouse?

RDBMS Schema
* Used for OLTP systems
* Traditional and old schema
* Normalized
* Difficult to understand and navigate
* Cannot solve extract and complex problems
* Poorly modeled

DWH Schema
* Used for OLAP systems
* New generation schema
* De Normalized
* Easy to understand and navigate
* Extract and complex problems can be easily solved
* Very good model

What is hybrid slowly changing dimension?

Hybrid SCDs are combination of both SCD 1 and SCD 2.
It may happen that in a table, some columns are important and we need to track changes for them i.e. capture the historical data for them whereas in some columns even if the data changes, we don’t care. For such tables we implement Hybrid SCDs, where in some columns are Type 1 and some are Type 2.

What are the different architectures of datawarehouse?

There are two main things
1. Top down - (bill Inmon)
2.Bottom up - (Ralph kimbol)

1. What is incremental loading?
2. What is batch processing?
3. What is crass reference table?
4. What is aggregate fact table?

Incremental loading means loading the ongoing changes in the OLTP.
Aggregate table contains the [measure] values, aggregated /grouped/summed up to some level of hierarchy.

What is junk dimension? What is the difference between junk dimension and degenerated dimension?

Junk dimension: Grouping of Random flags and text attributes in a dimension and moving them to a separate sub dimension.

Degenerate Dimension: Keeping the control information on Fact table ex: Consider a Dimension table with fields like order number and order line number and have 1:1 relationship with Fact table, In this case this dimension is removed and the order information will be directly stored in a Fact table in order eliminate unnecessary joins while retrieving order information..