data warehousingfaqs,OLAP

1.What Is Data warehousing?

Ans: The process of designing, building, and maintaining a data warehouse system.?

2. What are the types of dimension tables?

Ans: Type 1 ->Doesn’t maintain any history and is update only.

Type 2-> Maintains full history. Insert row when certain attributes change else update row.
The latest record is identified by a flag or date field which is called the MRI or Most Recent Indicator
Type 3->Maintains partial history. History is maintained as columns and not rows.
Hence it is predecided as to till how many levels of history you want to maintain.

3. How do u connect one report to another report?

Ans: By Using Drill Through we can easily Connect to next report.

4. How to enter user defined variables into Ab Initio graphs?

Ans: All environment variables which have been exported in the shell which
is invoking abinitio.eg. all variables exported in .profile ) will also be available in Abinitio graph.

5. What are the various Reporting tools in the Market ?

Ans: OLAP tool are

Cognos

Business Objects

SAS

Microsoft Source analyzer

MSTR

Hyperion (BRIO)

6. What is a Star Schema?
Ans: A relational database schema organized around a central table (fact table) joined to a few smaller tables (dimension tables) using foreign key references. The fact table contains raw numeric items that represent relevant business facts (price, discount values, number of units sold, dollar value, etc.)

7. What Snow Flake Schema?
Ans: Snowflake schemas normalize dimensions to eliminate redundancy. That is, the dimension data has been grouped into multiple tables instead of one large table. For example, a product dimension table in a star schema might be normalized into a products table, a product category table, and a product manufacturer table in a snowflake schema. While this saves space,it increases the number of dimension tables and requires more foreign key joins. The result is more complex queries and reduced query performance.
8. What is the Difference between OLTP and OLAP?
Ans:OLTP
        Current data
        Short database transactions
       Online update/insert/delete
       Normalization is promoted
       High volume transactions
       Transaction recovery is necessary

       OLAP
         Current and historical data
       Long database transactions
       Batch update/insert/delete
       Denormalization is promoted
       Low volume transactions
       Transaction recovery is not necessary
10. What is ETL?

Ans: ETL is extraction,trasformation and loading,ETL technology is used for extraction the information
from the source database and loading it to the target database with the necessary transformations done in between
.
11. What is SCD1, SCD2, SCD3?

Ans: SCD 1: Complete overwrite
SCD 2: Preserve all history. Add row
SCD 3: Preserve some history. Add additional column for old/new.

12. What is ODS?

Ans: ODS stands for Online Data Storage. It is used to maintain, store the current and up to date information and the transactions regarding the source databases taken from the OLTP system. It is directly connected to the source database systems instead of to the staging area. It is further connected to data warehouse and moreover can be treated as a part of the data warehouse database.
13.What is a CUBE in data warehousing concept?

Ans: Cubes are logical representation of multidimensional data. The edge of the cube contains dimension members and the body of the cube contains data values.

14. What is ER Diagram?

Ans: ER - Stands for entity relationship diagrams. It is the first step in the design of data model which
will later lead to a physical database design of possible a OLTP or OLAP database.
15. How do you load the time dimension?

Ans: Every Data warehouse maintains a time dimension. It would be at the most granular level
at which the business runs at (ex: week day, day of the month and so on). Depending on the
data loads, these time dimensions are updated. Weekly process gets updated every week and monthly process, every month.

16. What is Dimensional Modeling?

Ans: In Dimensional Modeling, Data is stored in two kinds of tables: Fact Tables and Dimension tables.
Fact Table contains fact data e.g. sales, revenue, profit etc..... Dimension table contains dimensional data such as Product Id,product name, product description etc.....

17. What are the possible data marts in Retail sales?

Ans: Product information, sales information.

18. What is a linked cube?

Ans: A cube can be portioned in 3 ways.Replicate, Transparent and Linked. In the linked cube the data cells can be linked in to another analytical database. If an end-user clicks on a data cell ,you are actually linking through another analytic database.

19. What is data validation strategies for data mart validation after loading process?

Ans: Data validation is to make sure that the loaded data is accurate and meets the business requriments.Strategies are different methods followed to meet the validation requirements.

20. What is the main difference between Inman and Kimball philosophies of data warehousing?

Ans: The main difference b/w the Kimball and Inman technologies is..
         marts first then combining tehm up to form a data warehouse
         Inmon----Creating datawarehouse --- then datamarts