Design a multidimensional business intelligence (BI) semantic model

Thought experiment

In this thought experiment, demonstrate your skills and knowledge of the topics covered in this chapter. You can find the answers to this thought experiment in the next section.

Humongous Insurance is a company that sells insurance policies to individual consumers and companies in a single country and has offices in four different regions in this country. The amount for which each insurance policy is sold is called a premium. Each year, the sales manager projects monthly quotas for insurance premiums by region. You have been hired as a BI developer at Humongous Insurance to create an SSAS multidimensional database that enables users to analyze sales and quotas.

The current database environment includes one OLTP system called Sales, and another OLTP system called Quotas. Both databases currently run on SQL Server 2016.

Before you can create the multidimensional database, you must design and populate a data warehouse that can answer the following types of questions:

• How many policies have been sold by line of insurance (Personal or Commercial), customer, sales territory, and date?

• What is the revenue, cost, profit, and profit percent of policies sold by line of insurance, customer, sales territory, and date?

• What is the quota for insurance policies by sales territory and month?

To satisfy the business requirements, the multidimensional database must have the following characteristics:

• A customer can have multiple policies, but each policy is limited to one customer.

• The customer dimension must include the customer name and the customer’s phone number. The phone number must be available for reporting, but not for slicing and dicing operations.

• The sales territory dimension must support drilling from region to state/province to city.

• The date dimension must support drilling from year to month to date.

Based on this background information and business requirements, answer the following questions:

1. Sketch the fact tables, dimension tables, and relationships in the star schema that meets the business requirements.

2. Describe the steps necessary to support the drilldown functionality in the sales territory dimension with optimal query performance.

3. What property do you set to hide the phone number attribute in the customer dimension from slicing and dicing operations, but allows it to be included in the dimension as a member property?

   1. AttributeHierarchyOrdered

   2. AttributeHierarchyVisible

   3. AttributeHierarchyEnabled

   4. IsAggregatable

4. What property do you set for each measure to improve the legibility of values in a client application?

   1. DataType

   2. MeasureExpression

   3. AggregateFunction

   4. FormatString

5. When you browse the date dimension, you notice the members of the Month attribute sort in alphabetical order. How do you correct this problem?

6. What is the granularity attribute to configure when defining the relationship between the quota measure group and the date dimension?

7. How do you support the profit percentage in your solution design?

   1. Add a fact table column and set the FormatString for the new measure to Percent.

   2. Create a calculated measure to divide profit by revenue.

   3. Add a new measure group to the cube to isolate the measure.

   4. Define the MeasureExpression property on a new measure to divide profit by revenue.

8. When you process and then query the cube to view quota by customer, you see the same quota value repeating on each row. What step can you take to prevent the display of these values?