Transcript DataMart
DataMart - Beginner Presenter: Dave Bennett Agenda Introduction to Datamart Datamart and its tools Finding data Connecting to Microsoft SQL Building a simple report What is DataMart? What is Datamart? • The interface between Providex, the database Jonas currently uses, and Microsoft SQL providing access to the data through other software applications. What Software Applications can be used? • Familiar applications such as Excel, Excel with PowerPivot, Access, Crystal Reports, and other ODBC compliant applications. What is Required? • 2008 Windows Server R2 Standard or higher, 2008 SQL Server R2 Standard or 2012 SQL Server Business Intelligence or Enterprise edition, Windows 7 64bit with 4gigs of RAM, 2010 Microsoft Excel with 2010/2012 Microsoft PowerPivot (Free downloadable add-on) • Datamart Interface service. This will need to run 100% of the time to insure the data between Providex and Microsoft SQL is in sync. What data is available in Microsoft SQL? • Almost all the customer, vendor, employee, accounting, inventory, work order, job costing and equipment management data. What is DataMart What is Datamart? • The interface between Providex, the database Jonas currently uses, and Microsoft SQL. What is Microsoft SQL (Data Warehouse)? • A relational database whose primary function is to store and retrieve data as requested by other software applications. Is my information secure? • Microsoft SQL provides various levels of password protection to help prevent unauthorized access to your information. What data is available in Microsoft SQL? • Almost all the customer, vendor, employee, accounting, inventory, work order, job costing and equipment management data. What is DataMart What Software Applications can be used? • Familiar applications such as Excel, Excel with PowerPivot, Access, Crystal Reports, and many others. What is Required? • 2008 Windows Server R2 Standard or higher, 2008 SQL Server R2 Standard or 2012 SQL Server Business Intelligence or Enterprise edition, Windows 7 64bit with 4gigs of RAM,2010 Microsoft Excel with 2010/2012 Microsoft PowerPivot (Free downloadable add-on). • Datamart Interface service. This will need to run 100% of the time to insure the data between Providex and Microsoft SQL is in sync. What is Excel PowerPivot? • PowerPiviot is a free add-in to Excel 2010 and will be a built-in function for Excel 2013. It extends the capabilities of PivotTable data by introducing the ability to import data from multiple sources, in a familiar ‘Table’ type format. • You can create simple pivot table style reports or create something exceedingly complex by defining your own calculated fields. There is a whole set of functions defined for exactly that purpose called DAX (Data Analysis Expressions) functions. What are tables? A set of data elements (values) that are organized using a model of vertical columns (identified by name) and horizontal rows. How do I find the data I am looking for? Datamart comes with a “Data Dictionary” available through “Help” within Jonas. How do I find the data I am looking for? Usually the names are pretty close to how they appear within a particular Jonas Screen. One of the exception to this will be spare or user defined fields among others. How do I use the Data Dictionary? Double click on the field name ‘JobNumber’ and it will show a list of tables where the filed ‘JobNumber’ exists. Note: The table names are fairly descriptive to give you an idea of what is in them. How do I use the Data Dictionary? Ctrl+F will bring up a ‘Finder Box’ within Internet Explorer. Find the field ‘JobNumber’. Note: There are 90 tables with the field ‘JobNumber’. How do I use the Data Dictionary? Double click the table name, and review the other fields of data available with in this table. Connecting to Microsoft SQL (Data Warehouse) Open Excel -> PowerPivot -> From Database -> From SQL Server. Enter your connection information within the appropriate boxes. Test the connection. Connecting to Microsoft SQL (Data Warehouse) Select the desired method to import the data. Here we will use ‘List of tables’ Connecting to Microsoft SQL (Data Warehouse) Select the table(s) you want to import data from. This is usually based on the findings from within the data dictionary. Connecting to Microsoft SQL (Data Warehouse) Import results based on the selected table(s). Note: The tab(s) on the bottom of the window contains the name of the table(s) selected. Make a PowerPivot report From the PowerPivot window, select the PivotTable type. Make a PowerPivot report Choose where to create the PivotTable. Note the coordinates and adjust as desired. Make a PowerPivot report Now you are ready to layout your report!! Make a PowerPivot report From the PowerPivot field list, select a field, drag and drop in the ‘Row Labels’ box. To change the order and grouping of the fields, from the ‘Row Labels’ box, select and drag the desired field to the new view/group position. Make a PowerPivot report From the PowerPivot field list, select a value field, drag and drop in the ‘Values’ box below. Right mouse click on the field within the ‘Values’ box, select ‘Measure Settings’ and change the aggregate type to counting, average, sum and others. Make a PowerPivot report To add ‘Slicers’ aka Interactive Filters, select the field from PowerPivot field list, drag and drop to Horizontal or Vertical Slicers box. Make a PowerPivot report Voilà!!! Slice away ……. Choose any individual filter or hold down the shift or control key for contiguous/noncontiguous filter selections within a Slicer. Make a PowerPivot report To remove any filter within a Slicer, click the filter icon with the RED X. Time permitting ….. A look at the included templates provided with Datamart • Project Management / Job Costing • Service Bill / WorkOrders • General Ledger A look at custom reports designed for clients Questions and Answers ……..