Data Integration in Excel: Enhancing Productivity and Analysis

1. Data Integration in Excel Dr. Nitin Paranjape MVP (Office System) Chairman and MD, Maestros

2. Version information • Excel 2007 for all demos • Many features exist in 2003 as well • Keep Excel open to understand better • But, don’t try to do what I am showing • Using Beta version • Complicated process of broadcasting voice • Please bear with any crashes, disconnections

3. Objectives • Understand available integration options • Mapping business scenarios to available features • Understanding strengths and limitations of data handling

4. POLL • How many of you have used Excel for data handling in your applications?

5. Data integration areas • Data capture within Excel • Getting external data into Excel • Accessing Excel data from outside • By Exporting it to another format • By keeping it within Excel • Processing data within Excel • Using Excel as a report writer

6. Primary purpose of spreadsheet is to analyze data. NOT to store data.

7. Data sources • ODBC / OLEDB • SPS list • XML • OLAP • Text • Query files

8. Import tools • Wizard (only ODBC) • MS Query (only ODBC) • Text import • Text import VBA code • Data connection wizard

9. Query files and connections • This facility has been available for years • It works on ODBC sources only • Uses either a wizard or MS Query • Demo (Wizard and MS Query)

10. Text Import • Highly complex text import possible • Fixed width or Delimited • Multiple delimiters supported • Decide data types while importing • Manage multiple delimiters • Manage preceding negative signs • Demo

11. Text import programmatically! With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\temp\test.txt", _ Destination:=Range("$A$1")) .Name = "test" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 437 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = True .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 4, 3, 9, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With

12. Importing ODBC vs OLEDB • Import External Data = OLEDB • New database query = ODBC • Web query = import of HTML tables from web sites • More options added in 2007 • SQL Server direct • XML • Access • Analysis services

13. Connections dialog in Excel 2007 • This is a major improvement • You can see all available Existing connections in a single place • You can filter local, network and file based connections in one dialog • This helps you reuse any past connections quickly • Demo

14. Deploying connections across an organization • You can store Excel files in a special library • Connection library in SharePoint • Users can point to a SPS site and ask for connection information • This ELIMINATES the need to deploy / send query files to multiple desktops • It also provides centralized access control

15. Capturing data within Excel • Very commonly used by end users • Creates lots of problems • Accuracy, validation, structure is missing • Users send files to each other creating confusion and multiple copies

16. Native features of Excel for capturing data • Linear data to auto-form : Demo (Ccard.xls) • Validations : Demo • Forms & Control Toolbox controls • User based editing : Demo

17. Understanding the importance of Excel List • We work on blocks of data in Excel • When more data is added, formulas DO NOT refresh automatically • This can lead to lot of errors • List corrects that problem • In addition, it integrates with SPS • In 2003, it is two way sync • In 2007, it is one way (from SPS to Excel) • Demo

18. Capturing data in SPS list • Better alternative • Keeps data central • Users can update individually • Shows only their own data • No Manual consolidation required • DEMO

19. Exporting data • Supported formats • Delimited • XML spreadsheet • XML • Sounds like a limitation? • How do you pick up data from Excel and put it directly into your database? • How do you use it with ETL tools like DTS?

20. Excel as ODBC source • Excel ODBC driver has been available for years • Create a named range • Create ODBC source • The Excel file is considered the database • And Named ranges become tables • Now it can be used in any tool which supports ODBC. Including Excel! • DEMO

21. Report generation using Excel • Connect to data source • Process the data • Analyze data • Render as report • Finally let user manipulate the read only snapshot of report in Excel

22. Report generation options • Raw data with formulas and custom formatting • You make the report yourself by adding data from a recordset into Excel • Use SQL Reporting services to render it as Excel • Pivot table : Demo • Create a base view of report • Let users do further analysis in a flexible way

23. Eliminating the row limit of Excel • The 65000+ limit of Excel is a misconception • There is no need to get data in Excel • Most want to do analysis using Pivot Table • Pivot table can be created DIRECTLY • In this case, there is no practical limit on the number of rows • The Pivot Cache is available OFFLINE automatically. • Pivot drag drop does NOT fire database queries • Only REFRESH button fires database level re-query

24. Pivot Table and dashboards • Multiple copies of Pivot tables showing different views of data • Multiple pivot tables from different sources • Pivot Tables and Pivot Graphs combined

25. Importance of learning Pivot Table • Extremely powerful • Eliminates complex SQL code • Users can change format anytime – eliminating further custom code • Data connection libraries + AD security + pivot table is a very secure, zero code solution for reporting

26. GetPivotData function • Initially it is confusing • For Pivot based calculations outside the table • Very useful when you are managing Pivot Tales programmatically • In this case you cant’ see the Row, Column coordinates to put formulas • How to use it?: Demo

27. OLAP reporting in Excel • 2003 was primitive support • 2007 provides better support • Many more cube functions added • The User interface is native and fast • Office web components are also more responsive even if the data is large

28. Consolidation • Very powerful when data comes with Row and Column headings (Cross tab format) • Can be used programmatically • Saves lots and lots of coding • Results in Pivot Table: Demo

29. XML • XML based, open, Excel file format • Server side data crunching without Excel possible • For Excel functions, use Excel Services • Custom schema based editing, validation and import export • Custom actions and element sensitive Task Pane UI using .NET code attached to XML schema (Smart Documents)

30. Finally Excel Services based reporting and data capture • Renders Excel data as pure web page in a secure manner • Create Excel sheet. Name required ranges. • Post it in SPS library • Configure library to use Excel services • Now users can view Excel as a simple web page • Interactivity is maintained • In-line editing is NOT possible • Specific, named cell values can be captured • Demo

31. Usage scenarios • Interactive • Programmatic

32. Interactive • Complex delimited text file imports • Consolidation

33. Programmatic • Data capture forms / surveys • Reporting from OLTP / OLAP with end user level report format editing capabilities • Dashboards • SQL reporting services – render as Excel by default • SPS Lists based reporting • SPS List – Business Data Catalog – Excel reporting • Enterprise wide data connections in SPS lists • Office web component – PivotTable to deliver reports on Web

34. Summary • Learn Excel first • There are many ways in which Excel can be used for data capture, processing and reporting • Map the application business needs to the appropriate data handling method • This will make users happy and will eliminate lots of complex code we write everyday

35. References • Old interface to new interface http://go.microsoft.com/?linkid=5174798 • Excel cell referencing (very useful while coding in VBA)http://www.expresscomputeronline.com/20021216/techspace1.shtmlhttp://www.expresscomputeronline.com/20021223/techspace1.shtml • Excel Help!

36. Thank you nitin@maestros.net www.nitinparanjape.com/blog Learn and Grow

37. Quiz • This is not a POLL • You have to send your answers to contact@erfolgcs.com • Format should be • 1-A, 2-B and so on

38. Question 1: What is the number of rows available in Excel 2007? • 65365 • 1.5 million • 1.1 million • 1.04 million

39. Question 2: In Office 2007, the data How does the data synchronization between Excel 2007 Table and SPS 2007 List work? • Changes in SPS change data in Excel table • Changes made in Excel change data in SPS • Changes can be bi-directional • No changes possible. It is a snapshot

40. Question 3:Technology used to expose business data in external databases in SharePoint 2007 is called… • Excel Services • Business Data Catalog • Data Connection Library • None of the above

41. Question 4:Which type of data Excel CAN NOT import? • DBF • SYLK • Visio • MDB