Question

In: Statistics and Probability

Create a snowflake schema diagram (you do not have to mark every possible level) and include...

Create a snowflake schema diagram (you do not have to mark every possible level) and include your implicit assumptions for each level of the dimension.

Starting with the base cuboid [customer, date, product, store], what are the specific OLAP operations (e.g., roll-up student to the department (level)) that one should perform in order to list the average sales of each cosmetic product since January 2005?

Solutions

Expert Solution

) Lets go step by step first understand what is dimension and fact table

Dimension table:-

  • Dimension tables provides descriptive information for all the measurements recorded in fact table.
  • Dimensions are relatively very small as comparison of fact table. in our example dimensions are customer, date, product and store.

Fact Table:

  • It contains all the primary keys of the dimension and associated facts or measures(is a property on which calculations can be made) like in our question sales is fact table.

I am writing schema for four dimensions like below-

customer(CustID,Name,Address,City,Country,PhoneNo,)

date (Day, DayofWeek, MonthID, QuarterID, Year)

product(ProdID,ProdName,SupplierID,Unit,Price)

store(StoreID,StoreName.ContactName,Type,Address)

Fact table-

sales(CustID,Day,ProdID,StoreID,QtySold,Amt, Sales)

Measures can be described as, total count = count(*) and avg sales= avg(sales).

2) retrieve data from joining of product, date and sales tables and find the avg sales where product is grouped by on the basis of type equal to "cosmetic",product name and date is between Jan 2005 to Current Date.


Related Solutions

You have been requested to design a schema (as an E/R diagram) for the contact-tracing database....
You have been requested to design a schema (as an E/R diagram) for the contact-tracing database. Your group is busy working to design and field a contact-tracing database to be able to trace rapidly people who may have been in close proximity (contact) to someone diagnosed with a highly infectious disease. The Contact Tracing Domain To be able to trace with whom a person has been in contact, we need to know where that person has been and when. We...
Create a scatter diagram of Y = ‘Annual % ROI’ against X = ‘Cost’. Include the...
Create a scatter diagram of Y = ‘Annual % ROI’ against X = ‘Cost’. Include the trendline, the equation of the trendline, and the coefficient of determination on the graph. Obtain b0 and b1 of the regression equation defined as y ̂ = b0 + b1X. Hint from Dr. Klotz: You can get this right from the trendline. Calculate the estimated ‘Annual % ROI’ when the ‘Cost’ (X) is $160,000. Conduct a full hypothesis test:   You will be in charge....
Assume that you have three objects. Think and describe whether it is possible to create a...
Assume that you have three objects. Think and describe whether it is possible to create a state where all three objects are going to be at rest taking gravitational interaction among them into account. Assume that we have three objects with electric charges, two with a positive and one with a negative electric charge and think and describe whether it is possible to realize such a state where all three are static. Assume that it is not just a single...
Draw the Use Case Diagram for the case You have to create a Community Library Borrowing...
Draw the Use Case Diagram for the case You have to create a Community Library Borrowing System based on the following requirements: When new books arrive, these books must be cataloged into the system by a librarian (i.e., entered in the system). The system also needs to record who has borrowed what books. When someone (called a patron in library terms) borrows a book, the system checks his or her library card to ensure that it is still valid and...
What do you understand by Circular wait? Is it possible to create a deadlock using only...
What do you understand by Circular wait? Is it possible to create a deadlock using only the Mutual exclusion? Explain in your own way.
1.Create a Database in Access with the information The database must include: Database name: Monaco Enterprise  Mark...
1.Create a Database in Access with the information The database must include: Database name: Monaco Enterprise  Mark Johnson #87451 Table name: Contacts Delete the Primary key. Fields name and data type are (remember to choose the data type): Field Name Data Types Employee Name Short text Name Short text Last Name Short Text Work Yes/No 2.Go to the “Datasheet View” and enter the data. * Remember to save the table. 3.Move the last name field after the employee name. 4.The (data)...
a. Draw a process diagram to produce AlN powders by carbothermic reduction process. Include all possible...
a. Draw a process diagram to produce AlN powders by carbothermic reduction process. Include all possible steps, and briefly explain each step if needed. b. In this process, what would be the keys to obtain high quality sinterable AlN powders?
Create a level 1 Dataflow diagram for a ticket booking system based on the information from...
Create a level 1 Dataflow diagram for a ticket booking system based on the information from a usecase below The priority will be high The actor will be the employee The use case is about getting net profit, cost, and revenue reports The type is external The trigger is that net profit, cost and revenue repords will be generated by the employee The preconditions are: 1.) the data of the ticket price can be accessed after the user logs in...
Create a level 1 Dataflow diagram for a ticket booking system based on the information from...
Create a level 1 Dataflow diagram for a ticket booking system based on the information from a usecase below The priority will be high The actor will be the employee The use case is about getting net profit, cost, and revenue reports The type is external The trigger is that net profit, cost and revenue repords will be generated by the employee The preconditions are: 1.) the data of the ticket price can be accessed after the user logs in...
Create a level 1 Dataflow diagram for a ticket booking system based on the information from...
Create a level 1 Dataflow diagram for a ticket booking system based on the information from a usecase below The priority will be high The actor will be the employee The use case is about getting net profit, cost, and revenue reports The type is external The trigger is that net profit, cost and revenue repords will be generated by the employee The preconditions are: 1.) the data of the ticket price can be accessed after the user logs in...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT