8.You need to create the AD Sales measure.Which aggregation function should you use? A. SumB. AverageC. ByAccountD. AverageOfChildrenAnswer: D 9.Topic 2, Contoso, LtdBackgroundYou are the business intelligence (BI) solutions architect for ContosoLtd, a multinational sales company with offices in London, Madrid,page 5 / 24
Paris, Brisbane, Tokyo, and New York. Contoso sells office consumable products such as pens, printer ink, and paper. You produce solutions by using SQL Server 2012 Business Intelligence Edition and Microsoft SharePoint Server 2010 Enterprise Edition with SP1. Technical Background Contoso's products are categorized by using four levels while some use only two or three levels. Products are categorized as shown in the following table. Contoso sells products through mobile sales staff, direct marketing, and its website. Sales personnel are located in various regions around the world, and each region has a sales manager who is paid a quarterly bonus based on the total sales in the region during the quarter. Regions are categorized as shown in the following table. page 6 / 24
SQL Server Analysis Services (SSAS) is used to host a multidimensional database. The database contains a single cube named Sales and three database dimensions named Products, Regions, and Date. A single measure named Sales Total has been defined in the cube. The data source for the database is a SQL Server data warehouse. The Products dimension contains a single user-defined hierarchy named Products. To prevent the display of empty members when users browse the Products dimension, the Extract, Transform, and Load (ETL) process populates all missing values as shown in the following diagram. The structure of the Products hierarchy is shown in the following diagram. page 7 / 24
The Regions dimension contains a single user-defined hierarchy named Sales Regions. The dimension is based on a single dimension table in the data warehouse and the attribute relationships have not been modified since the dimension was created by using the Dimension wizard. The structure of the Sales Regions hierarchy is shown in the following diagram. The Date dimension contains a single user-defined hierarchy named Calendar. The structure of the Calendar hierarchy is shown in the following diagram. A role named UserRegions has been created in the SSAS database that will be used to filter members in the Regions dimension based on page 8 / 24
the authenticated user. Administrative staff from around the world will produce sales reports with Microsoft Excel 2010 based on the Sales cube. Developers will produce reports with SQL Server Reporting Services (SSRS) based on the Sales cube and the reports will be delivered to users through report subscriptions and a web browser-All users log on to an Active Directory Domain Services (AD DS) domain named contoso.com. All client computers and servers are joined to the contoso.com domain.
You've reached the end of your free preview.
Want to read all 24 pages?
- Winter '20
- Microsoft SQL Server