HW4
1) This is a hands-on exercise, intended to test your ability to create and browse an SSAS cube. For this exercise, pretend that the Recruiter database, which you used in your previous two homeworks, is your primary DW. And, build a cube to perform MOLAP on the data there.
a) Set up a new data source. Provide a screenshot showing the details, similar to the one below.
b) Now, set up a data source view that includes all the tables from the primary DW.
i) Replace the Poistion table by query. In addition to including all columns from the Position table, this new query should also include a column named ExpOpenings, to store the product (Experience * Openings). Please provide a screenshot showing the query you used, similar to the one below. Note: In place of the string <<< QUERY TO REPLACE THE TABLE >>>, I expect to see your SQL SELECT statement.
ii) Once you have finished the query, take a screenshot of the data source view. Include the screenshot in your write-up. What is the likely purpose of this query, or the additional column created by it?
c) Now, create a cube to analyze the number of openings and the number of openings that require experience, by both industry and state/province. Create a cube for this pupose.
i) What table or table(s) did you select as your measure group tables?
Position
ii) What table or table(s) did you select as your dimension tables?
Employer
iii) How many dimensions does your cube have? Explain your choice.
Only one dimension, namely employer because employer is linked to all the other tables.
iv) Provide screenshot(s) of the dimension structure design screen, to show how you have edited your dimension(s). Note that the screenshot should look similar to the one below.
d) Time to deploy and browse the cube! In this OLAP session, I expect you to do the following:
i) First, display the overall total number of openings as well as the overall total number of openings that require experience. Provide a screenshot of the browser
a) Set up a new data source. Provide a screenshot showing the details, similar to the one below.
b) Now, set up a data source view that includes all the tables from the primary DW.
i) Replace the Poistion table by query. In addition to including all columns from the Position table, this new query should also include a column named ExpOpenings, to store the product (Experience * Openings). Please provide a screenshot showing the query you used, similar to the one below. Note: In place of the string <<< QUERY TO REPLACE THE TABLE >>>, I expect to see your SQL SELECT statement.
ii) Once you have finished the query, take a screenshot of the data source view. Include the screenshot in your write-up. What is the likely purpose of this query, or the additional column created by it?
c) Now, create a cube to analyze the number of openings and the number of openings that require experience, by both industry and state/province. Create a cube for this pupose.
i) What table or table(s) did you select as your measure group tables?
Position
ii) What table or table(s) did you select as your dimension tables?
Employer
iii) How many dimensions does your cube have? Explain your choice.
Only one dimension, namely employer because employer is linked to all the other tables.
iv) Provide screenshot(s) of the dimension structure design screen, to show how you have edited your dimension(s). Note that the screenshot should look similar to the one below.
d) Time to deploy and browse the cube! In this OLAP session, I expect you to do the following:
i) First, display the overall total number of openings as well as the overall total number of openings that require experience. Provide a screenshot of the browser