Short note on Star Schema.

-

 Start Schema

  • It is the data warehouse schema that contains two types of tables: Fact Table and Dimension Tables. Fact Table lies at the center point and dimension tables are connected with fact table such that star share is formed.

→ Fact Tables: A fact table typically has two types of columns: foreign keys to dimension tables and measures that contain numeric facts. A fact table can contain fact data on detail or aggregated level.

→ Dimension Tables: Dimension tables usually have a relatively small number of records compared to fact tables, but each record may have a very large number of attributes to describe the fact data. Each dimension in the star schema has only one dimension table and each table holds a set of attributes. This constraint may cause data redundancy. The following diagram shows the sales data of a company with respect to the four dimensions, namely time, item, branch, and location.



  • There is a fact table at the center. It contains the keys to each of the four dimensions. The fact table also contains the attributes, namely dollars sold and units sold.

Advantages and Disadvantages of Star Schema 

  • Since star schema contains de-normalized dimension tables, it leads to simpler queries due to a lesser number of join operations and it also leads to better system performance. 
  • On the other hand, it is difficult to maintain the integrity of data in star schema due to de-normalized tables. It is the widely used data warehouse schema and is also recommended by oracle
Defining a Star Schema in DMQL

define cube sales_star [time, item, branch, location]:
dollars_sold = sum(sales_in_dollars), avg_sales = avg(sales_in_dollars), units_sold = count(*)
define dimension time as (time_key, day, day_of_week, month,quarter, year)
define dimension item as (item_key, item_name, brand, type,supplier_type)
define dimension branch as (branch_key, branch_name,branch_type)
define dimension location as (location_key, street, city,province_or_state, country)

Issues Regarding Star Schema

• Dimension table keys must be surrogate (non-intelligent and non-business related), because:
– Keys may change over time
– Length/format consistency

• Granularity of Fact Table–what level of detail do you want?
– Transactional grain–finest level
– Aggregated grain–more summarized
– Finer grains  better market basket analysis capability
– Finer grain  more dimension tables, more rows in fact table

• Duration of the database–how much history should be kept?
– Natural duration–13 months or 5 quarters
– Financial institutions may need longer duration
– Older data is more difficult to source and cleanse

                   OR,



Comments

Post a Comment

Popular posts from this blog

What are different steps used in JDBC? Write down a small program showing all steps.

-
 T he fundamental steps involved in the process of connecting to a database and executing a query consist of the following: Import JDBC packages. Load and register the JDBC driver. Open a connection to the database. Create a statement object to perform a query. Execute the statement object and return a query resultset. Process the resultset. Close the resultset and statement objects. Close the connection. These steps are described in detail in the sections that follow. 1. Import JDBC Packages This is for making the JDBC API classes immediately available to the application program. The following import statement should be included in the program irrespective of the JDBC driver being used: import java.sql.*; Additionally, depending on the features being used, Oracle-supplied JDBC packages might need to be imported. For example, the following packages might need to be imported while using the Oracle extensions to JDBC such as using advanced data types such as BLOB, and so on. import o...
Read more

Suppose that a data warehouse for Big-University consists of the following four dimensions: student, course, semester, and instructor, and two measures count and avg_grade. When at the lowest conceptual level (e.g., for a given student, course, semester, and instructor combination), the avg_grade measure stores the actual course grade of the student. At higher conceptual levels, avg_grade stores the average grade for the given combination. a) Draw a snowflake schema diagram for the data warehouse. b) Starting with the base cuboid [student, course, semester, instructor], what specific OLAP operations (e.g., roll-up from semester to year) should one perform in order to list the average grade of CS courses for each BigUniversity student. c) If each dimension has five levels (including all), such as “student < major < status < university < all”, how many cuboids will this cube contain (including the base and apex cuboids)?

-
Image
a) A Snowflake Shema is shown in figure below: b) The specific OLAP operations to be performed: 1. Roll-up on course from course_id to department. 2. Roll-up on a student from student_id to university. 3. Dice on course, student with department ="CS" and university = "biguniversity" 4. Drill-down on a student from the university to student_name. c) N = 4 dimensions The cube will contain 54 = 625 cuboids.
Read more

Discuss classification or taxonomy of virtualization at different levels.

-
Image
Taxonomy of virtualization Virtualization is mainly used to emulate the execution environment, storage, and networks. The execution environment is classified into two: – Process-level – implemented on top of an existing operating system.  – System-level – implemented directly on hardware and does not or minimum requirement of the existing operating system. OR,  Virtualization covers a wide range of emulation techniques that are applied to different areas of computing. A classification of these techniques helps us better understand their characteristics and use  V irtualization is mainly used to emulate  ● Execution Environments: To provide support for the execution of the programs eg. OS, and Application.  ○ Process Level: Implemented on top of an existing OS that has full control of the hardware  ○ System Level: Implemented directly on Hardware and do not require support from existing OS.  ● Storage: Storage vi...
Read more