9:19 AM 10/30/2013 Chapter 0: Show AnyWare attendees how to log on to their system. Is everyone on 2010 or 2013? Microsoft introduced new tools recently - Exporing data uncovering hidden information Cool tools only available in 2013: PowerView and PowerMap Sharepoint also has PowerView Main focus is on PowerPivot in 2010 and 2013 Pivot Tables were available in earlier versions Has everyone already worked with Pivot Tables? With PowerPivot, I can take data from any source and relate it. We will be looking at showing our results in PivotTables We have something similar to Integration Services (PowerQuery), in Chapter 2 What is DAX? How can I get more information from my data? Chapter 3 Chapter 4: Where can I uncover more information? Chapter 5: Enterprise BI (the rest of the coure is about self-service BI) Appendix A: More information DAX functions - DAX has over 300 functions Overall Eval at the end of the course If anything is not satisfactory, let us know Talk about pairing up - talk to your buddy ... you know what you say! The exercise files are about 3Gb of data On your desktop: There is an Exercise Setup link - Demo this Learn from _our_ mistakes Which objectives/chapter is important to you? Come up in pairs Chapter 1: If I am going too fast, I really need you guys to slow me down. Many of you are interested in Chapter 3 We are going to be looking at the tools that have been expanded for BI 2010: PowerPivot is an Add-In and must be downloaded for 2010 Slide 1-2: Review objectives - We will tell you about slicers Slide 1-4: BI: Uncovering things that you would not normally have seen from your data Why do people use Excel as a database: Because they are familiar with Excel If you go to IT, they give it to you in chunks, if it's wrong it takes time to get a new dataset. Reports highlight business insights Liam has said that sometimes your data is poor I will tell you some of the things you can do to clean up your data Prasad: How is PowerPivot different? I'll tell you in the next two slides 1-5 ETL: SSIS 1-6: PowerPivot pulls the data together Suggestion from attendees: Maybe should label OLAP Cube Discussion on SSAS Can relate one cube to another in PowerPivot Liam: Do I have to pull everything in, or can I specify what I want in PowerPivot? - Some work should be done on the server side - PowerPivot can relate all these different things How much data can we handle in PowerPivot? 64-bit Excel for larger datasets (32-bit can't handle 990,000 rows). Where is microsoft competing in this sphere (vs, say, Cognos, Business Objects) - How many people know how to use Cognos, Business Objects, SAS? - How about Excel? - Don't have to go to other tools - PowerPivot is a free add-in - Add-ins are included in 2013 Difference between Enterprise BI and self-service BI discussion 1-7 (suggest moving to before 1-5) - xVelocity engine is a lot faster - Discussion on ODBC/OLE DB, other technologies (e.g. JSON) 1-8: PowerPivot is a data source for ... PowerView, PivotTables, PivotChart PowerMap must still be downloaded - only available to 2013 PowerView: in SSRS 2012, Sharepoint 2010 1-9: PowerPivot Data source created in 2013 cannot be accessed in 2010, but: PivotTable can still be manipulated, even in 2007 1-12:Demo 1-14: Click on Active, vs Click on All We have now created a PivotTable from multiple tables Which tables have been used to build the Pivot Table? Employees, Order Details, Orders, Products which fields from each table are used in the PivotTable? Employees: Full Name Order Details: Quantity Orders: ShipCountry Products: ProductName Explains Time Slicers, regular slicers (briefly) Time Slicers 1-15: What data sources are listed? Access Northwind Excel Order Details Excel Orders Query From Excel Files Text Employees 1-16: Demo diagram view Attendee Q: Where is the source data? 1-17: How many records are in each table? Employees: 9 Order Details: 2155 Orders: 830 Products: 77 Step 7: What is the source for each table? The source (connection) is shown in the properties: Employees: Text Employees Order Details: Excel Order Details Orders: Excel Orders Products: Access Northwind Excel and PowerPivot are separate, except PowerPivot data sources show up in Data Connections. 1-18: Has this removed the data model? No 1-19: Q1: C Q2: B 1-20: Q3: D Q4: A and B [Note: A states that PowerPivot is "... within the Excel window ..." - this is not correct.] 1-22/23: How many people have not worked with Slicer? 4 Demo creating a slicer on ShipCountry. 2010 does not have the Timeline option. There is a Clear Filter button on the right hand side of the Slicer tool. 1-24 Demo Sparklines: Select multiple items Exercise 1.1 Intro: Review the steps Exercise 1.1 Review: If you got to step 15, I'm happy! Emphasize that cities and states are currently disconnected - we will fix this using hierarchies. Cannot create a calculated field for a PowerPivot data source. This is a good thing, because PowerPivot aplies the calculation to the raw data. In Excel, we can see some ad-hoc calculations on the status bar. These are based on the summarized data. Demoed the result of step 23: Questions about how you get there! 1-28: Chapter Review Q1: A Q2: C Q3: D Chapter 2: 2-2: Denormalize: Did you find it easy to find the stuff I asked you to put into the PivotTable? Show you how to simplify the view in the PivotTable field list Query Designer only works with SQL Server - write text versions of SQL queries 2-4: Relationship is like a lookup (VLOOKUP) 2-6: Connections in PowerPivot and Excel (ref: Added Slide diagram) Do Now 2-7: Step 5b: Only goes one level (tables related to the selected table(s)). It doesn't llok at Tables related to those! Can click multiple to times to go down a level. Where can I go to see the relationships? Diagram View Hover over a relationship in diagram view to see the related fields. Also hover over the relationship icons in data view Why not use Access? People are not familiar with Access capabilities; Excel can create dashboards; Excel is familiar SQL Server team developed PowerPivot. Slide 2-10: Who has not done databases? Use VLOOKUP analogy again. Drag and drop to create a relationship. Q: What are the requirements for an Excel lookup table? Must have a unique column Q: Which of the following tables is a lookup table? Employees Attendee question: Do the field names have to be the same? No Can you move columns around? What, like this ... (does it) Read only means that the data cannot be changed, not that the view cannot be changed 2-11: Do in order of the Orders table: Other rows: 4 Peacock Margaret 10267 FRANK 4 3 Leverling Janet 10273 QUICK 3 2 Fuller Andrew 10273 QUICK 2 4 Peacock Margaret 10284 LEHMS 4 Which system (Access or PowerPivot) would catch duplicates in the primary key? Access prevents duplicates in the primary key; PowerPivot will let you know if there is no unique iddentifier in the data. 2-12: Note the use of the word "Lookup" - Excel terminology 2-13 Step 2: Q: Order Details? OrderID to Orders OrderID Q: Employees? EmployeeID to Orders EmployeeID Q: Customers? CustomerID to Orders CustomerID Step 4: Q: Do the pairs of columns that connect each table match the PowerPivot Relationships you saw earlier? Demo deleting a relationship in PowerPivot, and adding it back in. Re-order the tables with Customers, Employees and Shippers at the top, Orders in the middle, and Order Details at the bottom. 2-14: It is _essential_ to keep this discussion simple. This is just about creating a table which contains the key fields we want to report on. Do _not_ be drawn into a long discussion about normalization and denormalization. 2-15: These are the steps 2-18: Square brackets indicates a field, rather than a table. Can you have a primary key/foreign key relationship on calculated columns? Yes. Can you use the CONCATENATE (Excel) Function here? Not eactly - you can use the DAX concatenate function instead. Where is this saved? In the workbook 2-20: Use a single quote (apostrophe) around table names (gives intellisense). 2-21: Q1: C Q2: B Ex2.1 Intro: Start button, type cmd, click on cmd.exe. At the prompt type "whoami". Note: do not add "0" in front of the numbers less than 10. Ex2.1 Review: DAX works on the raw data, the pivot table summarizes it. Slide 2-24 Q: When would you denormalize your data model? A: To simplify the access to the fields Q: What is a risk of denormalization? A: Inflexibility - or inability to answer questions because the data is missing Slide 2-28: Do in order of the Orders table: Other rows: 4 Peacock Margaret 10267 FRANK 4 3 Leverling Janet 10273 QUICK 3 2 Fuller Andrew 10273 QUICK 2 4 Peacock Margaret 10284 LEHMS 4 1 Davolio Nancy - - - 5 Buchanan Steven - - - A right outer join is: 4 Peacock Margaret 10267 FRANK 4 3 Leverling Janet 10273 QUICK 3 2 Fuller Andrew 10273 QUICK 2 4 Peacock Margaret 10284 LEHMS 4 - - - 10249 TRADH 6 - - - 10284 LEHMS 8 A full outer join is: 4 Peacock Margaret 10267 FRANK 4 3 Leverling Janet 10273 QUICK 3 2 Fuller Andrew 10273 QUICK 2 4 Peacock Margaret 10284 LEHMS 4 - - - 10249 TRADH 6 - - - 10284 LEHMS 8 1 Davolio Nancy - - - 5 Buchanan Steven - - - 2-29: Stress that the Design button (step 3) is only available for SQL Server. With Access, create the queries in Access instead. 2-34: Ex 2-2 Intro: Step 10 (page 47) mispositioned box Ex 2-2 Review It doesn't report on blank data 2-38: I don't like copy and paste - what if I want to update the data? I have to delete the old data, and copy and paste append again. It's a manual process. 2-41: I can convert data to Proper case. It uses its own expression language. In the settings button, choose "Show formula bar" to see the formulas. 2-44/45 (done as a demo): - Select some restaurant choices; select weather, say. You don't have to do anything, Microsoft has done everything for you. Can you RSS? It looks at RSS feeds, yes Demo splitting data at, say, commas Also show formula bar. Ex 2-3 Intro: No comments 9:10 AM 10/31/2013 Review: DAX functions that look like Excel functions are limited in some ways, more powerful in other ways than the corresponding Excel functions. Note: Hand-Drawn 8 is better than Slide 1-6 Hand-Drawn 9 is also useful. PowerPivot: Uses DAX functions Size of the row affects the siz of the workbook? Yes Ex 2-3 Review: No Comments Slide 2-50: Chapter 2 Review: Q1: A Q2: C Q3: D 9:30 AM 10/31/2013 Chapter 3: If you haven't worked a lot with functions, this is going to be a hard chapter for you. I'd like you to ... and work the exercises at home. Slide 3-2: Ask questions one to one on extra functions. Show you how one time-intelligence function works - the others, in Appendix A, work the same way. Slide 3-4: A Calculated Field always goes in the values section (it can't be anywhere else); calculated columns determine the context (Filter/Column/Row) Do values always have to be numeric? They usually are, except for KPIs. Slide 3-6: Do Now Try to move the field "Sum of Total Revs" into anywhere other than the Values section - it won't go, because it is a calculated field. Step 3: Ensure you are in Advanced Mode Note that Show Implicit Calculated Fields is on. Switch it on and off. Implicit Calculated Fields cannot be modified (greyed out) You will create Explicit Calculated Fields from now on, because they improve performance Slide 3-10 Review Q1: B Q2: A Q3: C Do implicit calculations always have SUM? No, they can be changed to another aggregate function. Slide 3-11 Cleansing functions ae all in the Appendix Slide 3-13 What is the difference between COUNT and COUNTA? Does it gracefully handle divide by zero? No, it gives you an error. Note the X functions (COUNTX, etc.) Slide 3-16: Note that Table is required, even if you're in the table when you're writing the expression. X stands for eXpression Slide 3-17: Why is there an error? The _DAX_ SUM function only accepts one column as an argument How would you correct the error? Use the SUMX function. Slide 3-18: We can now replace our calculated column with a calaculated field, which is faster. Slide 3-20: We are going to create a loose relationship via our calculated fields. Slide 3-21: One approach: For each table with unit prices (in USD), create a calculated column for each currency converting it to each currency Too much work! We are going to create a calculated field which does the work for us. Ex 3-1 Review: Note that the Calculated Field AveCurRate in the Currency table could have used SUM, MAX etc, because when the slicer is applied only one record is returned. You can imagine the slicer as applying a filter to the data source - you don't see the filter in PowerPivot, though. Now you can take one calculated field instead of multiple calculated columns. Can you have the same name for a calculated field in another table? No, the names must be unique with the data model. Slide 3-25: You already use filters if you use the COUNTIF function (the filter is the criteria). We have SUMIF(S), but do we have MAXIF(S) ... ? No Have you used array functions? How slow is your workbook with those functions? Slide 3-26 We can refer to a column in another table. Demo using DAXFilters.xlsx. Stress that they will be doing this themselves shortly. If you don't specify an aggregate function in Calculate, it defaults to SUM. Slide 3-28: Cannot specify a Column is equal to a calculated field - can use the Filter function to do this. Note that both columns and calculate fields are enclosed in square brackets. Ex 3-2 Intro: You are going to create ratios, which you cannot do in a PivotTable directly. Ex 3-2 Answers: Step 1: Sum of Quantity is implicit, Quantity Measure is explicit Step 6: Sum of Quantity = 968, Quantity Measure = 968 Step 7: Sum of Quantity = 9532, Quantity Measure = 968 Ex 3-2 Review: You get more reliable calculations by using the CALCULATE function. Slide 3-30: When do I use the FILTER function? When its a dynamic calculation referring to a calculated field. Q1: D Q2: C Q3: A Slide 3-32: How many people have not used the Excel IF function? DAX functions include NOT(Logical1). I prefer not to use AND() or OR() in DAX because they are limited to two expressions. Slide 3-33: SWITCH is also similar to SELECT CASE in VBA. If the third SWITCH value was "KC", SWITCH would still return "Ken" Bonus Answers: Large: 60 Medium: 188 Low: 559 Very Low: 49,337 Slide 3-36 Calendar could contain 40 years of dates (twenty years before today, to twenty years after). Could - Create in Excel - Get from web (see Ex 2.1) - Get from the next exercise Slide 3-38 The supplied table is available (free) from the web. Contains approximately six years (1995 to 1999). Slide 3-40 (Ex 3.3) Note: If the attendees have difficulty with the values not updating, tell them to close and re-open the workbook. This exercise shows you how to do a running total - something you cannot do in a normal pivot table. Slide 3-42: A KPI allows you to monitor your results - for example, when it's red, action needs to be taken. Ex 3-4: Some machines have lost their PowerPivot. This can be corrected by removing the COM Add-In and activating it again. Slide 3-46 Q1: D Q2: B Q3: A Chapter 4 Slide 4-26: Can demo growth using the PowerMap example, which shows the growth of power stations in the Us over the period 1900-2008: 1. Open the file US Power Stations-Power Map Demo.xlsx via the hyperlink "PowerMap Example" 2. Insert | Geoflow | Map | Launch GeoFlow 3. Select the pre-created tour PowerStations 1900-2008 (click on the map) 4. Click on the "play" triangle at the bottom to run the visualization. Slide 4-30: Q1: B or C Q2: B Q3: A (supported by B, C, and D) Chapter 5: Slide 5.5: You have created multi-dimensional data in PowerPivot! ------------------