Working with Multidimensional Cubes in SQL Server –Student Notes MIS 5346 Foundations of Data Warehousing G. Green

Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 1 of 43

Building a Multidimensional Cube Last Updated 3.21.2016

Create an Analysis Services Project ....................................................................... 4 Design the Multi-dimensional Cube ...................................................................... 5 Step 1: Create a Data Source .......................................................................................................... 5 Step 2: Create Data Source View .................................................................................................... 6 Step 3: Design/Build the Analysis Cube........................................................................................... 7

Deploy the Analysis Cube ...................................................................................... 8 Browse the Analysis Cube ..................................................................................... 9 Refining the Cube .................................................................................................. 9 Extending the DSV.......................................................................................................................... 9 • New Tables (Named Queries) ...................................................................................... 10 • New Fields (Named Calculations)................................................................................. 10 • Table Relationships...................................................................................................... 11 Cube Usability .............................................................................................................................. 12 • Add DSV Tables to Cube............................................................................................... 12 • Add DSV Measures to Cube Facts ................................................................................ 12 • Remove Measures from Cube Facts ............................................................................. 13 • Add DSV Attributes to Cube Dimensions ..................................................................... 13 • Treating a Dimension Table as a Fact Table ................................................................. 15 • Hierarchies and Attribute Relationships ...................................................................... 18 • Bypassing Dimension Key Errors .................................................................................. 19 • Time Intelligence ......................................................................................................... 22 Viewing Cube Data in Excel .......................................................................................................... 23 Cube Usability, Continued… ......................................................................................................... 24 • • • •

• • •

Create New Cube Measures (Calculated Members) ..................................................... 24 Hiding Measures .......................................................................................................... 25 Named Sets ................................................................................................................. 26 Cube Formatting .......................................................................................................... 27 Change Attribute Names .............................................................................................. 27 Change Dimension Names ............................................................................................ 27 Change Measure Group Names .................................................................................... 27 Hiding Attributes .......................................................................................................... 27 Conditional Formatting................................................................................................ 27 KPIs .............................................................................................................................. 33 Actions ........................................................................................................................ 36

Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 2 of 43

Updating Cube with Structural Changes ....................................................................................... 38 • • • •

Data Warehouse Column Modifications ....................................................................... 38 Data Warehouse Table Additions ................................................................................. 38 Data Warehouse Row Additions ................................................................................... 38 Data Warehouse is Re-created in its Entirety ................................................................ 38

Analyzing Cube with MDX ................................................................................... 39 Analyzing Cube with Excel ................................................................................... 41

Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 3 of 43

In Class Exercise Create Class Performance Cube Create a folder Create a folder on desktop called in_class_projects to store all our in-class BI objects in. Create an Analysis Services Project Project is a container for one or more BI objects Solution is a container for one or more projects In SS12 there are two approaches for creating BI models: tabular and multidimensional. • Multidimensional is the traditional model based on dimensional models and the cube concept • Tabular uses a relational model and stores all data in-memory for performance advantages Both modes use compression to reduce the size of Analysis services databases relative to the size of the relational DW. Multidimensional compresses to about a third of the original DW size; tabular compresses to up to a tenth of the DW size. But as the tabular model has some limitations as of this semester (e.g. no support for actions, aggregations, or data mining; amount of RAM required exceeds size of data on disk; etc) we will use the traditional multidimensional model. 1. In SSDT, file | new | project… | bi analysis services template | analysis services multidimensional and data mining project a. Type classperformanceAS for the project name 2. Browse… to the desktop in_class_projects folder for the location—click Select Folder 3. Check “create directory for solution” 4. Use “classperformance” for the solution name 5. Click OK to create the project (and solution)

Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 4 of 43

Design the Multi-dimensional Cube Step 1: Create a Data Source Data source (DS) is connection information: it identifies different source data stores that analysis services can connect to, and how it can connect to those sources. Ie, where is the source data for our cube stored? Can be SS, Oracle, Access, DB2, or Teradata. A data source is akin to connection strings in asp and like connection information we provided in SSIS. 1. Right-click data sources folder | New data source… 2. Click Next on opening wizard page 3. Click “create a data source based on an existing or new connection”, then click the New… button 4. Select or type your Server name (e.g. MSSQLSERVERDEV, or . or localhost) 5. Use windows authentication 6. Select or type the database name: classperformanceDW 7. Click “test connection” then OK, and OK again 8. Click Next 9. On Impersonation Information page, click Inherit, then Next Impersonation indicates what authorization or credentials SSAS should use to connect to the data source you’re defining once it’s run-time (eg, deploy and/or process and/or use time). This ID should have READ privileges in the data source system (ie in SSMS). You can authorize someone’s specific ID (or the ID of a specific role/team), the ID of whatever acct starts the SSAS service (typically netwksvc, localsys, or localsvc), or inherit. Choosing Inherit says use the default service account of the data source, often just the Local Service account which is typically specified during installation of the data source’s DBMS. The ID needs to be in active directory of remote data source. 10. Name the data source classperformanceDW DS, then click finish

Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 5 of 43

Step 2: Create Data Source View A Data Source View (DSV) is akin to a view that specifies what tables from your relational data source (e.g., your data warehouse) you want to include in the cube. A DSV can be extended beyond what is in your data source to include new relationships between tables, new fields (named calculations) and/or new tables (named query). We will look at ways to extend the DSV at a later time. For now, we will create a DSV that basically mirrors our source data warehouse tables. Right-click data source views folder | New data source view… Click Next on opening wizard page Ensure the “classperformanceDW DS” data source is highlighted; click Next If you get a dialog pop-up labeled “Name Matching”, then ensure the “Create logical relationships by matching columns” box is checked, and that the “Foreign key matches Same name as primary key” button is selected; then click Next. You might get this window when foreign keys are not defined that link all the tables together. 5. Click the double-right arrow button to select all the tables then click Next

1. 2. 3. 4.

NOTE: the filter box is like a search box—it allows you to quickly find available objects. Handy when you have a long list of tables. Also, if there are FK relationships between tables, you can click Add Related Tables. For example, try just selecting FactEnrollment as your only included object, then click add related tables—the other tables become included because they are linked together. 6. Name the DSV ClassPerformanceDW DSV then click finish 7. You can see the underlying data for any of these tables by: a. Right-clicking table b. Choose Explore data This is handy when you are unfamiliar with the data. It can help you identify ways you may need to either extend the DSV and/or refine your cube later on. If you ever make changes to the structure of your Data Warehouse, you will need to “Refresh” your DSV. Do this while in the DSV design view by clicking the button that resembles a Refresh button. If any Data Warehouse changes were made that impact your DSV, you will be shown what the changes are, and you can choose to accept the changes.

Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 6 of 43

Step 3: Design/Build the Analysis Cube Designing a cube (or building a cube in SSDT terminology) is a process that basically creates XML scripts that define the structure of your cube. It’s somewhat akin to doing a CREATE statement though not exactly equivalent. Basically by saving your DS, DSV, and Cube objects, you’re doing a build; conversely, when you click on Build, you’re basically just saving scripts. Building a cube basically starts with a previously-created DSV, then indicates which DSV objects are dimensions, which objects are fact tables (or measure groups in SSDT terminology), and which field(s) within each fact table represents measures. NOTE: you can manually take each table in your DSV and define them as dimensions, THEN combine the dimensions with the numeric data in fact table(s) to create the cube. BUT we will instead use the cube wizard to both create the dims AND connect them to the fact table(s) to create the cube. 1. Right-click Cubes folder in solution explorer | New Cube… 2. Click Next on opening wizard page 3. Choose “Use Existing Tables” | Next 4. Choose the classperformanceDW DSV data source view; then choose which table(s) contain your measures—these tables are called measure groups in SSDT. Measures should be in fact tables, so you should be selecting fact tables only. Click Next 5. Select which measures (i.e., numbers) you want to be included in the fact table(s); click Next NOTE that SSAS will automatically create user-friendly dim/fact/column names for you. You can change these now by right-clicking, or change later after the cube is created. Also note that SSAS will automatically create a measure in each fact table that represents a Count of rows. For an individual fact record, this count value will be 1. But when you start aggregating (e.g., how many students enrolled for mis5346 in spring 2011), then this “count” gets aggregated and basically becomes a count of all the records that meet the aggregation criteria. This is a very handy field. 4. Ensure all your dimensions are selected; click Next NOTE: the screen will show the name of the dimension, and the table(s) it will use to create the dimension. For snowflake schemas, you’ll often see the related snowflaked table in addition to the main table to create one dim. 5. Name the cube ClassPerformanceDW CUBE; click Finish

Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 7 of 43

Deploy the Analysis Cube Before you can actually use a cube for analysis, you need to Deploy the cube to the SQL Server analysis server (SSAS). Try browsing the cube now (i.e., go to the Browser tab)—it will tell you that no classperformanceAS database exists. That is because up to this point cube definitions exist in the “projects” folder on your desktop. We must move these definitions to an Analysis Services server AND put data in the structures in order to access the cube for analysis. When you deploy a cube, SSAS builds files describing the cube in the project bin folder, copies this structure information from your SSDT project folder to the SSAS server (i.e., the equivalent of moving CREATEs to the server), creates the analysis services “database” (cube), AND it Processes the cube unless you have chosen to “not process” in the Deployment tab of the project properties. Processing a cube means that data from the DW is used to populate the cube on the server (i.e., the equivalent of INSERTing data from our source DW into the cube). Before deploying the cube, check the following settings: • In SSMS o Expand the Security | Login folder o Ensure there is a login that has OLAP somewhere in the name. If not then do the following:  If you are in the classroom, issue this command using the Master database: CREATE LOGIN [NT Service\MSOLAP$MSSQLSERVERDEV] from windows;  If you are on your own computer, issue this command using the Master database: CREATE LOGIN [NT Service\MSSQLServerOLAPService] from windows; o Right-click the OLAP login | Properties | User Mapping o In the Mapping area near the top, check the Map box next to the ClassPerformanceDW database o In the “Database role membership” area below, check the “db_datareader” and “db_datawriter” boxes o Click OK o Connect to the SQL Server Analysis Services engine (use the Connect button and change Database Engine to Analysis Services) o Right-click the Analysis Services server name | Properties | Security o Ensure the OLAP login is there—if not, add it •

In SSDT (IF you’re on a lab computer) o Right-click the project in Solution Explorer | Properties | Deployment tab o If you are in the classroom AND if localhost (or local) is there for the server, replace it with the SQL Server name on your computer (in the lab, this name usually corresponds to your computer name—e.g., fos-304XX\MSSQLSERVERDEV)

To deploy the cube: 1. In SSDT, Under the Build menu item, select Deploy classperformanceAS Once you get the green Deployment Completed message, notice that in Solution Explorer, you now have a cube built AND you have dimensions created. Also notice that you now have measures defined in the left-hand-side panel. And via SSMS, you could also see that a classperformanceAS database now exists in SSAS. Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 8 of 43

Browse the Analysis Cube We can now analyze the multidimensional cube similar to the way we would analyze data using a pivot table—simply drag and drop. 1. Click on the Browser tab in the Cube editor 2. If you see the “database does not exist” message, click the Reconnect button underneath the Cube Structure tab 2. Expand all the items in the Measures folder and Fact Enrollment subfolder 3. Drag/drop the Fact Enrollment Count measure into the data pane area (remember this is the measure that SSAS automatically created for us in the Fact Enrollment measure group) 4. Expand the class dimension; drag/drop the class SK field into the data pane area 5. Expand the time dimension; drag/drop the date key onto the data pane area 6. Expand the professor dimension; drag/drop the professor SK field into the filter pane area and choose a professor from the “Filter Expression” dropdown (slice/dice) 7. Drag the student SK field to the data pane area (pivot) 8. Drag Fact Enrollment Count back to measures, and Drag the Course Grade measure to the data pane area 9. Drag the Date Key back to its dimension While we now have a feel for how easy it is to analyze our class performance data in our newly-created cube, as you can see, the information is rather unusable and difficult to interpret for several reasons: 1. the only dimension attributes we have available to us are the primary keys 2. we have no ability to do any drill-downs or rollups of values 3. the course grade measure is summing up when we aggregate for anything above an individual student in a specific class In remaining sections, we will learn how to refine our cube (including extending our DSV) in order to address such items as noted above, and make the cube more useful and informative for analysis. Refining the Cube After creating a basic cube, there are several things that we can change and/or add in order to make analysis of the cube easier and more useful. Some of these refinements are to the DSV itself; others are to the cube. We’ll look at some of these refinements next. Extending the DSV Extending the DSV involves adding new keys, relationships (table and/or attribute), tables, and/or fields to the DSV design. Hopefully your data mart will reflect all the tables, fields, and table relationships that you want to setup. But if those things don’t already exist in your data mart (data source) you can create them on the SSAS side in your DSV; these extensions will exist in SSAS only. NOTE: We could have extended our DSV prior to creating a cube, but we will instead extend the DSV at this time, after the cube has already been built.

Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 9 of 43

•

New Tables (Named Queries) You can use named queries when you want to create a new table in your DSV based on data from existing DSV tables, OR from other data sources not in your current DSV. a. Create Fact Academic table i. Go to the classperformanceDW DSV design tab. If this tab isn’t open, open it by double-clicking on the classperformanceDW DSV in the Solution Explorer pane ii. Right-click in the DSV surface area | choose New Named Query… iii. Named Query Name: FactAcademic - Data Source: class performanceDW DS - Query definition (in the bottom area of the window): select student_sk, gpa, gmat from dimstudent where student_sk -1; - Click OK and save your work Notice the new table has a different table “icon” next to its table name than the existing ones. Also notice that the table is not yet linked to any other tables/dims. We’ll do that shortly.

•

New Fields (Named Calculations) You can use named calculations when you want to create derived and/or decoded fields based on existing fields. a. Create FullName for student i. In the DSV design view, right-click the table name area of dimstudent | New Named Calculation… ii. Column name: fullname - Expression: lastname + ', ' + firstname - Click OK b. Create GMAT_Rating for student i. In the DSV design view, right-click the table name area of dimstudent | New Named Calculation… ii. Column name: gmat_rating - Expression: case

-

Click OK

When gmat < 500 then 'low' When gmat between 501 and 700 then 'medium' When gmat > 700 then 'high' Else '-1' End

You can also create a Named Calculation based on an existing field in another table, e.g: case

end

when (select distinct drg.zippopulation from drg where drg.zip = dimlocations.zip) < 22000 then 'low' when (select distinct drg.zippopulation from drg where drg.zip = dimlocations.zip) between 22000 and 45000 then 'medium' when (select distinct drg.zippopulation from drg where drg.zip = dimlocations.zip) > 45000 then 'high'

Notice the new field has a calculator “icon” next to its field name. c. On your own: create a fullname field for dimprofessor Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 10 of 43

•

Table Relationships If primary keys, foreign keys, and/or relationships were not already been created between tables in the DW, you can create these in your DSV. a. Create primary key for FactAcademic table i. Right-click the student_sk field in factacademic | click Set Logical Primary Key b. Create relationship between FactAcademic and DimStudent i. In the DSV design view, right-click in the DSV surface area | choose New Relationship… ii. Source (foreign key) table: factacademic iii. Destination (primary key) table: dimstudent iv. Source columns: student_sk v. Destination columns: student_sk vi. Click OK

Be sure to save (i.e., Build) all your changes above, then re-deploy the project before proceeding. NOTE: the new table, new fields, and relationship are all in our DSV but NOT yet in our analysis cube— we will see how to refresh our cube with this information next…

Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 11 of 43

Cube Usability As you can see it is relatively easy to create a basic multidimensional cube from our data warehouse. However its usefulness is somewhat limited due to lack of meaningful attributes in the dimensions, lack of the ability to drill-down/roll-up, lack of robust time-based analyses, lack of formatted data, lack of visual performance indicators, etc. In the following sections we learn how to add these kinds of features to our cube to make it more usable to our business analysts.

•

Add DSV Tables to Cube The new DSV fact table that we created previously is not yet a part of the cube, so we must add it to the cube design. Remember that a fact table is considered a measure group in SSDT terminology. And a measure group is a part of a cube. So we must go to the Cube Design view to add this measure group to our cube. a. In cube design view, Cube Structure tab, in the Measures panel, right-click the Class Performance CW Cube| New Measure Group… b. Select the factacademic table; click OK (move it next to dimstudent if needed) If we want to add a new DSV dimension table to a cube, first we have to define it as a dimension, then we can add the dimension to our existing cube (we are NOT doing at this time): a. Right-click the Dimensions folder; New Dimension… b. Use an existing table; Next c. Set the Main Table to the new table you created; Next; Next d. Name the new dimension appropriately; Finish e. From the Dimension Usage tab (next to Cube Structure tab), right-click and choose Add Cube Dimension… f. Select your new dimension; OK

•

Add DSV Measures to Cube Facts If you added new fields (i.e., named calculations) to an existing Fact table in the DSV, you need to add the new DSV measures to the Fact Table in the Cube as follows (we are NOT doing at this time): a. In cube design view, Cube Structure tab, in the Measures panel, right-click the Fact table name | New Measure… b. Highlight the new named calculation in the Source column area that you want to add to the Fact table; OK

Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 12 of 43

•

Remove Measures from Cube Facts If you removed measures from an existing Fact table in the DSV, you will need to remove the measures from the Fact Table in the Cube as follows (we are NOT doing at this time): a. In cube design view, Cube Structure tab, in the Measures panel, right-click the measure in the Fact table that you want to remove | Delete b. If you created any Calculated Measures that were based on the measures you just deleted, you will need to modify or remove those Calculations as well via the Calculations tab (highlight the measure in the Script Organizer pane then click the Delete (X) key).

•

Add DSV Attributes to Cube Dimensions If this is a new cube, then up to now we have only primary keys of dimensions in our cube. This greatly limits our ability to understand and analyze our data. We will now add attributes to our dimensions that are useful for analysis. a. In the solution explorer, dimensions folder, double-click the DimStudent dimension. This opens up the dimension editor which allows you to make modifications to a specific dimension. b. The data source view pane on the right shows you all the student attributes defined for that table in the DSV. To include any of these attributes in the cube dimension, simply drag and drop the attribute from the DSV pane to the attributes pane. i. ii. iii.

Because we have created a separate fact table that contains the student’s gpa and gmat scores, we do not need to include these attributes in the cube. Because we have created a fullname field, we do not need to include first and last name in the cube. Student_id may not be a useful value either, unless the IDs are like BearIDs and frequently referenced.

Save the dimension and repeat the above steps for the time (include actualdate, monthofyear, year, monthname), class, and professor dimensions, then re-deploy the project. Then do the following analyses: 1. Enrollments by Student and Year a. Clear the data pane b. Move the Fact Enrollment Count measure into the data pane c. Move the DimStudent fullname into the data pane d. Move the DimTime Year field onto the data pane. If you dump this to excel, you’ll notice that while we have more Time attributes to analyze by, we still don’t have drill-down yet. Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 13 of 43

2. GPA and GMAT by student classification a. Clear the data pane b. Move the DimStudent Classification field onto the data pane c. Move the GMAT and GPA measures from the new FactAcademic measure group onto the data pane d. Notice our issue with GMAT and GPA—they are SUMming instead of Averaging—we will correct this later.

Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 14 of 43

•

Treating a Dimension Table as a Fact Table In an earlier example we created a new table in the DSV (FactAcademic) using a Named Query, linked it to an existing dimension table in the DSV (DimStudent) using Relationships, then added the new fact table to the cube as a New Measure Group. However with our current structure, we are limited to analyzing the FactAcademic measures only by DimStudent dimensions. You can see this if you try to analyze GMATs and GPAs by class enrollment year (again, understanding that our GMAT and GPA fields are not yet averaging):

Our GMAT and GPA values are not varying by year because those measures are only linked to students, NOT to dates. And with this design, we could not link/relate the FactAcademic table directly to the DimTime dimension because there is no Date associated with the FactAcademic table. A different approach is to have the ability to treat the DimStudent dimension as both a dimension table AND a fact table. However we will demonstrate how to do this by using a different scenario— the ability to analyze the number of classes associated with enrollments over time. a. Go to the cube structure tab b. Right-click the DimClass table name and select New Measure Group from Table

Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 15 of 43

Result: DimClass now has the color of a Fact table AND any numeric fields in the dimension have now been added as measures in the DimClass fact table.

Next, to be able to analyze the number of classes registered for over time, the DimClass fact table needs to be linked to the DimTime table with a Many-to-Many relationship, using the FactEnrollment table as the “linking” (i.e., Associative Table) entity. c. Click on the Dimension Usage tab d. Click the Relationships button as shown below e. Set values as shown below

Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 16 of 43

Save and redeploy the cube and analyze the number of enrollments AND the number of different class sections represented by those enrollments as shown below.

Running the query below in SSMS verifies the numbers for 3.24.2002 and 3.24.2003 that are shown above: select count(*), count(distinct from dimclass c, factenrollment where c.class_sk = f.class_sk and t.Date_sk = f.date_sk and ActualDate = '3/24/2002' union select count(*), count(distinct from dimclass c, factenrollment where c.class_sk = f.class_sk and t.Date_sk = f.date_sk and ActualDate = '3/24/2003' ;

Student Notes: Create Class Performance Cube

crn) f, dimtime t

crn) f, dimtime t

3/21/2016 9:07 PM

Page 17 of 43

•

Hierarchies and Attribute Relationships Once dimensions are created, you can create attribute hierarchies that are useful for doing drilldowns and rollups in cube data analysis. In general, hierarchies imply a one-to-many relationship between parent values and child values. In other words, a parent value can have many child values, but a child value can have only one parent value. This is called a natural hierarchy. We’ll create a natural hierarchy in the student dimension that relates each city to a unique state. There are also non-natural hierarchies that we can create for ease of navigation. These hierarchies may contain at least one many-to-many relationship. We’ll create two non-natural hierarchies in the time dimension. One will map a specific date to a month (natural), and a specific month to a year (non-natural). The reason the latter is non-natural is because a specific month can occur in many years, and a year can have many months. The second non-natural hierarchy that we will create is in the TermYear dimension—it will link a specific semester to a specific year . Again, the latter is really a many-to-many as a particular semester can occur in many years, and a year can have many semesters. Also, the “natural” hierarchy described previously would actually be a nonnatural hierarchy in the real world. For both natural and non-natural hierarchies, we will create attribute relationships. Attribute relationships actually let SSAS treat the hierarchy as a natural hierarchy, which will improve performance (effective storage, pre-aggregation, etc). For the non-natural hierarchies, we will have to do additional work to handle the many-to-many nature of the attribute relationships. Specifically, we’ll need to modify the key columns, name column, and order by properties of the attributes that can have more than one parent. Later we will also modify the visibility property of the attribute so that the user can only view the attribute through the hierarchy. Create a location hierarchy: a. Go to the dimension structure view of the student dimension by double-clicking on dimlocation in the solution explorer b. Drag and drop the following attributes from the attributes panel to the hierarchies panel in the order stated: State City (should go directly beneath state) c. Rename the hierarchy to Location by right-clicking and renaming d. Click on the attribute relationships tab e. Drag/drop the city node onto the state node f. Save the changes, close the dimension editor, and re-deploy the project

Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 18 of 43

Create a hierarchy in the dimtime table: Calendar Year Month

(Year ->

Month

-> Date)

a. Go to the dimension structure view of the time dimension by double-clicking dimtime in the solution explorer b. Drag and drop the following attributes from the attributes panel to the hierarchies panel in the order stated: Year Month of Year Actual Date Key c. Rename the hierarchy to Calendar Year Month by right-clicking and renaming d. Click on the attribute relationships tab e. Drag/drop the month of year node onto the year node f. Drag/drop the actual date key node onto the month of year node g. Save the changes Re-deploy the project, and you should expect warning and error messages. The real problem is the duplicate key warning. The reason is because we did not handle the many-to-many nature of the timerelated hierarchies. We will do that next. a. From the dimension structure view of the time dimension, click on the month of year attribute in the attributes panel b. In the properties window for month of year (should be directly below solution explorer; if not, be sure the deployment window is closed), locate KeyColumns property & click ellipsis button i. Select the Year column in available columns, then click the right arrow key ii. In the Key columns area ensure Year listed first (highlight & click the up arrow if needed) iii. Click OK. We have just indicated to SSAS that concatenating year with month makes our month values unique. c. With month of year properties still visible, go to the NameColumn property and set its value to month name; click OK. We just indicated that the actual value that should be displayed to users is the Month Name as opposed to the key value (Month of Year) which gives the month number. d. With month of year properties still visible, go to the OrderBy property and set its value to Key. e. Save the changes and re-deploy the project. •

Bypassing Dimension Key Errors If you create a hierarchy using the steps described above and still get a “duplicate key” warning, it is still possible to deploy the cube by bypassing the error. One method is described below: a. On the Cube Browser tab, click the Process button (1st button on the left, underneath Cube Structure tab) b. On the Process Cube dialog page, Click the Change Settings… button c. On the Change Settings dialog page, Click the Dimension key errors tab d. Ensure settings set as below then click OK e. On the Process Cube dialog page, click Run… f. If the processing successfully completes, click Close then re-attempt to deploy the cube

Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 19 of 43

If the above method does not resolve the problem, try selecting “Ignore error count” above instead of the option shown, and try deploying again.

Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 20 of 43

At this point, we can now browse our cube and by including our hierarchies in the analysis area, we can drill-down and rollup—but not in the cube browser—that ability will be there when we analyze the cube in Excel or other desktop clients.

Before we leave our time dimension, there is an additional feature of SSAS that has built-in calculations that support the analysis of data over time, including: • • • •

Period to date. Period over period growth. Moving averages. Parallel period comparisons.

But before we can use these built-in analysis features, we have to let SSAS know that we actually have a time dimension. We do that by adding “time intelligence” to our dim time dimension.

Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 21 of 43

•

Time Intelligence a. In the Cube Design view, cube structure tab, click on the Add Business Intelligence button which is the left-most button on the cube structure toolbar b. Click Next on the wizard intro page c. Highlight Define dimension intelligence; click Next d. Choose the DimTime dimension; click Next e. Choose dimension type of Time, check the Include box and set the dimension attributes as indicated below, then click Next: Year  Year Month  Month of Year Date  Actual Date f. Click Finish, then save and re-deploy project g. Restart the Add Business Intelligence wizard and this time highlight Define time intelligence h. Choose the “DimTime\Calendar Year Month” hierarchy and select the following calculations: Year to Date Year Over Year Growth % i. Click Next, then select all measures; click Next; finish j. Save, re-deploy the project, then go to cube browser tab and recreate screenshot above k. Expand the Dimtime dimension and you should see an extra set of calculations have been added. Drag/drop that attribute onto the data pane.

Once again we are unable to see the effects of the extra set of calculations in the cube browser—those values will be visible from our desktop analysis client (e.g. Excel). So at this point, let’s view our cube in Excel.

Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 22 of 43

Viewing Cube Data in Excel With SQL Server 2012, you are no longer able to do pivot table-like multidimensional analyses within Analysis Services. Instead, Microsoft added the Analyze in Excel button in the cube browser that will open up the cube in Excel so it can be more robustly analyzed there. a. In the Cube Browser click the Excel button just below the Cube Design tabs b. When asked to enable data connections, click Enable. This allows Excel to load the current cube data into your excel worksheet. c. Recreate the screenshot above in Excel but placing the Calendar Year Month hierarchy as Row Labels and the Coursename as Column Labels. You should now be able to drill-down on the Year, Month, and Dates associated with the courses.

d. Add the DimTime time intelligence calculations as column labels. You are now able to see the Year-to-Date numbers and the Year-Over-Year Growth % numbers for each course. e. Save the workbook in the inclass_projects desktop folder as ClassPerformanceAnalyses.xlsx. Note that the #NUM! value appears for the Growth% numbers for the beginning year (2002). This is because unfortunately the Growth% calculation does not check for nulls before attempting the division part of the calculation. Also notice that values for the two Year-based time intelligence numbers really only make sense at the Year level of granularity. If you expand to the semester or actual date level of granularity, most values will be null. The above two issues can actually distract from cube usability so use them with caution, and when used, ensure your business analysts clearly understand how to interpret these values.

Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 23 of 43

Cube Usability, Continued… We return to additional features in Analysis Services that allow us to make analysis cubes more useful. •

Create New Cube Measures (Calculated Members) New measures are business measures that were not in the source system that you make up on the SSAS side, based on data already in the cube. Creating a new measure in SSAS can be done either by (1) right-clicking in the Measures pane of the Cube Structure tab to create a new measure, or (2) by creating a calculated member. New measures go in measure groups (i.e., fact tables). New calculated members can go in a fact or dimension tables; basically the time intelligence information that we added previously were calculated members added to the time dimension. How do you know whether to create a new measure or a new calculated member? - If it’s a new number that will be stored in your fact table, and can be determined by using a count, sum, min, or max, create a new measure - If it’s a new number that will be stored in your fact table, and requires you to reference mathematical operators and/or logical expressions in order to determine the number, create a new calculation - If it’s a new number that will be stored in a dimension, create a new calculation We’ll create new calculated members to correct the problems we have with gmat, gpa, and course grade measures that are currently being summed instead of averaged upon aggregation. NOTE1: It is Extremely important that you add new calculated members to the top of the list of calculations. So when right-clicking, to add new members, ensure that if any calculation is highlighted it is the CALCULATE calculation. NOTE2: It is Extremely important that you leave the CALCULATE; calculation in the Calculations tab. This pre-defined calculation is the one that tells SSAS to aggregate all measures in the cube. If you inadvertently delete this command, none of your measures will show up in the cube. a. In cube designer, click on the Calculations tab. You should see some calculations already created in the Script Organizer pane (i.e., the time intelligence calculations). b. Right-click on the Command line in the Script Organizer panel; click New Calculated Member c. Name the new calculation [Average GMAT] d. Expression: [measures].[gmat] / [measures].[fact academic count]

e. Set Format string to 0 (gives us whole numbers) f. Set Associated measure group to Fact Academic; Save g. Repeat the above steps for GPA and for Course Grade (in the Enrollment measurements group). Be sure to allow for decimal positions in the format string for these two. And be sure to follow NOTE2 above. Save all changes and re-deploy the cube. Display the average course grades in the cube browser as shown below:

Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 24 of 43

Now that we have GMAT, GPA, and Course Grade measures that will average when aggregated, we can optionally hide the original measures from the view of the user so they will not be accidently selected. •

Hiding Measures a. On the Cube Structure tab of the cube editor, in the Measures panel, right-click on the CourseGrade measure (double-click on Factenrollment if you don’t see it); click Properties b. Change the Visible property to False c. Repeat the above steps for the GPA and GMAT measures d. Save, re-deploy the cube Return to the cube browser, refresh it, and you should notice the measures have been hidden in the measure group area:

Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 25 of 43

•

Named Sets Before we leave the topic of Calculations, you should know that it is possible to create a calculation that represents a pre-defined subset of cube data; like a filter, but you define the filter in advance. This is called a Named Set. For example, we can create a subset of data based on freshmen and sophomore students only. To do this: a. In the cube designer Calculations tab, right-click on the Command line in the Script Organizer panel and select New Named Set b. Name the named set “Freshmen, Sophomore Data” c. For the expression, type (or copy/paste) the MDX code as shown below {[dimstudent].[classification].&[freshman], [dimstudent].[classification].&[sophomore]}

- OR Type in an open brace, drag the classification member “freshman” into the expression box, type in a comma, then drag the classification member “sophomore” into the expression box, then type a close brace. d. Leaving the type as Dynamic means SSAS will determine the freshmen and sophomore students dynamically each time the named set is used. e. Save and re-deploy the project. f. Drag/drop the named set into the filter area of the cube browser so that only data for freshmen and sophomores are displayed.

Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 26 of 43

•

Cube Formatting As noted before, SSAS attempts to assign user-friendly names to dimensions, fact tables, and attribute names. This is done primarily by replacing dashes and underscores with spaces, and attempting to break up concatenated names with spaces. However this still often results in abbreviated, user-unfriendly names. We can change the way these names appear in the cube. Change Attribute Names a. In the solution explorer, double-click the Class dimension b. In the attribute pane, right-click CRN; rename to Class Registration Number c. Save Change Dimension Names a. In the cube structure tab, Dimensions pane, right-click the dimclass dimension b. Rename to Class Information Change Measure Group Names a. In the cube structure tab, right-click the Fact Enrollment measure group; rename to Enrollment Measures b. Right-click the Fact Academic measure group; rename to Academic Measures c. If calculated members were created for measure groups, go back to the Calculations tab and change the Associated Measure Group names to the new names Hiding Attributes a. In the dimension editor for the student dimension, right-click the city dimension and click Properties b. Change the AttributeHierarchyVisible property to False c. Hide the state attribute as well d. In the Time dimension, hide the Year, Month of Year, Month Name, Actual Date attributes e. Save, re-deploy, and browse the cube using the Calendar Year/Month hierarchy, the Student Location hierarchy, and Factenrollment Count as fields (if filter is present, highlight the filter and click Delete key on keyboard)

•

Conditional Formatting We can apply formatting to values based on one or more conditions, similar to what you would do in MS Excel. Let’s change the font of very low and very high average course grades. a. In the cube designer Calculations tab, Script Organizer panel, click on the Average Course Grade calculation b. Click to the left of Color Expressions to expand it c. In the Fore color text box, type the following expression to flag grades under 3.0 in red (255), and those 3.5 and higher in green (32768): iif ([measures].currentmember > 0 and [measures].currentmember < 3.0, 255, iif ([measures].currentmember >= 3.5, 32768, 0))

Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 27 of 43

d. Save and re-deploy, then click Refresh in the cube browser. e. Setup the data pane as indicated below using the Student Location hierarchy, Coursename, and Average Coursegrade:

If you are using SSDT version 2010 or higher you will not be able to see the conditional formatting in the Cube Browser. Open the cube in Excel, go to the Pivot Table Options tab in Excel, click on the Refresh button, and create a pivot table that includes the Average Course Grade measure. If you are using SSDT version 2010 or lower, you will be able to see the formatting.

The above approach only works for calculated members. If we want to do conditional formatting with regular measures in SSAS, we need to resort to MDX code to do it. Let’s say we want to format donation amounts exceeding $1,000 with a green color. We would need to do the following MDX code placed under the CALCULATE command: Scope([Measures].[Amount]); Fore_Color(This) = Iif ([Measures].[Amount] > 1000, 32768, 0); End Scope

If you are using SSDT 2012, the conditional formatting is no longer visible in Excel either. So we must do the conditional formatting in Excel. Refer to the screenshots below to accomplish the formatting of high and low average course grades in Excel:

Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 28 of 43

1. Highlight all the cells containing the values you want to conditionally format (cells B5:I9 in the screenshot below) then click Conditional Formatting then Manage Rules:

2. Create the “high” condition by clicking New Rule…

Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 29 of 43

3. In the “Select a Rule Type:” section, click the last option, enter the formula as indicated below, then click Format…:

4. Select the options as indicated below then click OK twice:

Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 30 of 43

5. Create the “low” condition by clicking New Rule…

6. In the “Select a Rule Type:” section, click the last option, enter the formula as indicated below, then click Format…:

7. Select the options as indicated below; click OK twice

Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 31 of 43

8. Ensure the rules appear as shown below in the ordering shown then click OK:

Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 32 of 43

•

KPIs Key performance indicators visually display progress towards a business goal. KPIs are based on: - The current Value of a previously-created calculated measure you’re interested in, - Your business Goal for the measure (typically a constant), - Status (a comparison of the current value to the goal) depicted visually - Trend (a comparison of current value to previous time periods), depicted visually. Trend expressions must be time-based. Optional field. We will create a KPI to show the user how well they are performing against their course grades goal. a. In the cube designer, click on the KPIs tab; then click on the New KPI button at the top (to the left of the delete button) OR right-click in the KPI Organizer pane and choose New KPI b. Name the KPI Course Grades KPI c. Change the Associated measure group to Enrollment Measures d. Drag the Average Course Grade measure from the Measure Group pane (metadata tab) to the Value Expression box OR type in the Value Expression as shown in screenshot e. Our average course grade goal is 3.5 per course; enter 3.5 into Goal Expression f. Choose the Gauge as the visual Status Indicator g. Type an MDX Status expression that lets us determine how close we are to meeting our goal. Here is the logic: -

When our actual average GPA value EXCEEDS our GPA goal, indicate a “high” status (status value of 1). When our actual average GPA value is under 95% of our GPA goal, indicate a “low” status (status value of -1) When our actual average GPA value is between 95 - 100% of our GPA goal, we will indicate an “acceptable” status (status value of 0)

Below is the MDX Status expression: CASE WHEN THEN WHEN THEN WHEN AND THEN END

kpivalue 1 kpivalue -1 kpivalue kpivalue 0

("course grades kpi") / kpigoal ("course grades kpi") > 1 ("course grades kpi") / kpigoal ("course grades kpi") < .95 ("course grades kpi") / kpigoal ("course grades kpi") =.95

h. Save and re-deploy the project, then switch to KPI Browser view to view the KPI (the KPI browser view is accessed by clicking the button to the left of the ABC checkmark icon).

Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 33 of 43

By default, the overall average GPA for the cube is being shown. We can see that over all courses and timeframes, we are not meeting our 95% goal. To see the status of our GPA goal at different points in time: a. Choose the DimTime dimension up top b. Choose the “Calendar Year Month” hierarchy c. Choose various timeframes in the Filter Expression to see the changes in the status indicator. You will need to click somewhere in the display structure area to update the information.

Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 34 of 43

KPI Example 2: KPI for Enrollment numbers—5 enrollments per course per year Let’s do another KPI for Enrollment. Our goal is to have 5 students per class per year. If we’re within 90% of that goal we’re OK. 1. The KPI Name is Course Enrollment KPI 2. Associated measure group is Enrollment Measures (or Factenrollment) 3. The MDX Value Expression is: [Measures].[Factenrollment Count]

OR you can drag/drop the Factenrollment Count measure into the Value Expression box. 4. The Goal Expression is 5 5. A Status indicator of Shapes is used 6. The MDX Status Expression is: CASE WHEN kpivalue ("course kpi") > 1 THEN 1 WHEN kpivalue ("course kpi") < .9 THEN -1 WHEN kpivalue ("course kpi") =.9 THEN 0 END

enrollment kpi") / kpigoal ("course enrollment enrollment kpi") / kpigoal ("course enrollment enrollment kpi") / kpigoal ("course enrollment enrollment kpi") / kpigoal ("course enrollment

Save, Deploy, then view the KPI in the cube browser. Note that the filters are applied to both KPI’s.

Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 35 of 43

•

Actions Actions give users a way to take additional steps during their analysis of cube data. They are events that are user-initiated by right-clicking in cells. There are three main categories of actions you can create: -

Report Standard

(invokes a previously-created SSRS report based on the current cube data) (a category of five actions that lets you do things like branch to a website specific to the data you clicked on—good for things like linking to a map) Drillthrough (provides you with detail-level data/records that produced that cell in the cube)

We will create a drillthrough action for our cube. Whenever any of the “Enrollment Measures” measures is right-clicked, we’ll define a new action called Enrollment Details that will display information about all the records that are rolled-up into that measure. a. In the cube designer, click on the Actions tab b. Click the New Drillthrough Action button OR right-click in the Action Organizer pane and choose New Drillthrough Action c. Name the action Enrollment Details d. Action Target should be Enrollment Measurements. Action Target specifies what types of cells the action should be available for. e. You can optionally enter an MDX expression for a condition. A condition is used when you want to further limit the types of cells the action should be available for. The condition must result in a Boolean value (i.e., the condition must evaluate to either True or False). We won’t need a condition for this action. f. In the Drillthrough Columns area, you’re selecting the details you want displayed when the user chooses this action for the currently-selected cell. Select the Dimension and the columns within the dimension that you want returned to the user when the Drillthrough action is clicked. We will select the following Dimensions and Return Columns: From Dimension Dim Student

Class Information

Measures

Return These Values Classification Undergrad or Grad Fullname CourseNumber Semester Year CourseName CourseGrade

g. Save and re-deploy the project, then go to the cube browser to try out the new action using the Factenrollment Count measure. NOTE: drillthrough actions do NOT work for calculated measures such as Average Course Grade . NOTE2: drillthrough actions do NOT work if you filter on 2 or more specific values. In SSDT 2012, the Action does not appear when viewing the cube in the SSAS browser. You must be in Excel to use this feature.

Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 36 of 43

Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 37 of 43

Updating Cube with Structural Changes There will be occasions where you will make changes to the data warehouse—new data rows will be added to fact and/or dimension tables, tables will be added or removed, and/or columns will be added or removed. To reflect table or column changes you will need to update your DSV then re-deploy the cube. Then assuming you are using a MOLAP structure for your cube (i.e., all data stored in cube, not just aggregate data), to add new data rows to the cube you will need to re-process the cube. •

Data Warehouse Column Modifications 1. Open the DSV for the cube in Design view 2. Right-click the Dimension or Fact Table(s) that has had column(s) changed 3. Choose Delete Table from DSV | OK

4. Right-click in an empty space in the DSV Design view 5. Choose Add/Remove Tables…

6. Select the previously-deleted table from Available objects and move it to Included objects | OK 7. Save the DSV 8. At this point, open each changed dimension table in design view and ensure all desired DSV fields have been moved to the left as attributes 9. After all changes are verified, rebuild/deploy the cube •

Data Warehouse Table Additions 1. Open the DSV for the cube in Design view 2. Right-click in an empty space in the DSV Design view 3. Choose Add/Remove Tables… 4. Select the new table(s) to include in the DSV | OK 5. Save the DSV

•

Data Warehouse Row Additions 1. In Solution Explorer, under the Dimensions folder, right-click the dimension that has had data added/updated 2. Choose Process… 3. If it asks if you want to build and re-deploy, choose Yes 4. Click Run… on the Process Dimension page

•

Data Warehouse is Re-created in its Entirety 1. Assuming the DW structure has not changed, in the Cube Browser, click the Process button 2. Click the Run… button 3. Click Close when processing done 4. Follow the steps in Deploy the Analysis Cube section

Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 38 of 43

Using a Multidimensional Cube Analyzing Cube with MDX Multidimensional Expressions language (MDX) is the query language for data stored in multidimensional data files. We’ve already seen that MDX is the basis for creating calculated members, KPIs, and actions in SSAS cubes. It is also the basis for general querying of SSAS cubes such as the analyses we do in the cube browser. One of the basics in MDX is knowing how to refer to a “member name”, which is basically a field: [dimension name] . [hierarchy name] . [hierarchy level name] . [member name] This is, of course, similar to SQL where you can refer to fields by their “formal” names such as: [database name] . [schema name] . [table name] . [column name] And just as with SQL, you do not have to include all parts of the name unless you’re dealing with names within queries that may be ambiguous. Another key aspect of MDX is being able to navigate through hierarchies with operators such as: Parent Children PrevMember CurrentMember NextMember… We will not explore MDX in detail, but here are some examples of VERY simple MDX queries you can do in SSMS directly against multidimensional data. Let’s do an example with our class performance data. a. b. c. d.

Connect to analysis services in SSMS Open a New Query window if not already open—should put you in MDX Query mode Make sure you are in MDX Query mode (button is above the Execute button) Make sure you are pointing to the ClassPerformanceAS analysis services database

Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 39 of 43

Copy and paste the following queries into a new window, then highlight and execute one at a time: /* show average gmat (note you can drag/drop tbls onto query window) */ /* this measure is in the academic measures folder */ /* it is the equivalent of dragging only average gmat onto SSDT browser */ Select [measures].[average gmat] on columns From [classperformancedwcube]

/* average gmat by student classification (will also show the ALL level of hierarchy by default) */ Select [measures].[average gmat] on columns, [dimstudent].[classification].members on rows From [classperformancedwcube]

/* average gmat by student classification and major */ Select [dimstudent].[major].members on columns, [dimstudent].[classification].members on rows From [classperformancedwcube] where [measures].[averagegmat]

/* show how many students by classification and major */ /* note the measure defaults to Count of ALL records meeting the criteria */ Select [dimstudent].[major].members on columns, [dimstudent].[classification].members on rows From [classperformancedwcube]

Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 40 of 43

Analyzing Cube with Excel Rather than use MDX in SSMS, or SSDT to browse a cube, most users prefer to analyze cube data using tools that are user-friendly and with a familiar user interface. Excel is a popular user interface for analyzing cube data. To use Excel with SSAS, we need to create an office data connection (ODC) to a specific SSAS cube. As you’ve seen previously, when you click the Excel button in the SSAS browser, a connection is created for you automatically. However you can open Excel and create a connection yourself. These steps are outlined below. a. b. c. d. e. f. g. h. i. j. k.

Open Excel Click the Data tab In the Get External Data group click From Other Sources then click From Analysis Services In the Data Connection Wizard, Connect to Database Server page, Server Name box, type either MSSQLSERVERDEV if you are in the classroom, OR localhost or a period if you are using your own computer Select Use Windows Authentication; click Next Select the database that contains the data that you want from the dropdown menu—in our case, we will select classperformanceAS Ensure the Connect To A Specific Cube or Table box is checked Highlight the ClassPerformanceDWCube, then click Next On the last page, give the ODC a filename—in our case, use the name ClassPerformanceAS.odc. Be sure to Browse to your in_class_projects folder so the ODC will be stored there Click the “Always attempt to use this file to refresh this data” box—this ensures that all updates to the cube will be available to any Excel workbook that uses this ODC. Click Finish; by default, ODC’s are stored in the My Documents\My Data Sources folder

At this point, we are ready to bring the cube data into Excel: a. The Import Data window should open after creating the ODC (if not, then click on the Data tab, select Existing Connections, then select the classperformanceAS odc) b. Click on PivotChart and PivotTable report. This puts the cube data in both a Pivot chart and a pivot table report. c. By default Excel will place the PivotTable report in cell A1 of Sheet1 (i.e., Existing worksheet). We will use this default. Alternatively, you could place these items on a “new” worksheet. Click OK, and the data is now available to browse in Excel

Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 41 of 43

Setup the pivot table and chart as shown in the screenshot below:

We can format the table and chart in Excel

Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 42 of 43

We can add hierarchies to do drilldowns.

Note that as I change the filter and/or drilldown in pivot table, the chart changes as well. We can invoke an action by right-clicking a gpa and selecting either Show Details or Additional Actions. BUT NOT with our calculated averages—would have to use Count measures. We can move the chart and/or table to other sheets. We can access our KPIs: a. Select a new worksheet, open our existing connection via Data tab, then click OK for a new pivot table report b. Expand one KPIs folder, and select all 3 checkboxes --Value, Goal, and Status. c. To make analyzing the KPI more useful, drag one of the time hierarchies into the Row labels area d. Then drag course name into the report filter section e. Repeat the above on the same sheet for the 2nd KPI folder

Student Notes: Create Class Performance Cube

3/21/2016 9:07 PM

Page 43 of 43