If you plan to attend SQL Saturday 267 in Lisbon, Portugal; I hope you can join me. My three presentations include a full-day preconference on Friday, the 11th of April on Building a Complete BI Solution with Office & SQL Server. There is still plenty of time to register.
On Saturday, the 12th of April, I have a session at 3:30 PM titled Visualization Choices: What, When, and How? and at 5:00 PM titled Tabular Models: Easier & Faster Than Cubes; Really?
Here are the preconference details:
Building a Complete Business Intelligence Solution with Microsoft Office 2013 & SQL Server 2012
This full-day, interactive demonstration will show attendees how to use Business Intelligence tools to create a complete solution delivering dashboards, self-service and mobile BI. We will create a solution with and without SharePoint integration using on-premises data sources.
Attendees should have a basic knowledge of SQL Server design and management tools and should know how to use Office Excel 2013. At the conclusion, attendees will know how to use SQL Server Data Tools (SSDT), the Excel PowerPivot add-in, Report Builder and Power View to create and deliver dashboard and self-service reports. We will also demonstrate the use of 3rd-party tools to deliver mobile extensions to an on premise BI solution.
BI Solution Components
Begin with a brief and high-level introduction to the components of traditional BI solutions to help attendees appreciate the usual scale and cost. Discuss the options to scale these items for small and large projects. Discuss the trade-offs for each and the value of centralized solutions, data quality and master data management. Discuss the challenges of designing, building and maintaining large-scale BI solutions.
- Data loading & transformation (ETL using SSIS)
- Staging (SQL Server relational)
- Dimensional model (SQL Server relational)
- Semantic model (SSAS Tabular)
- Report portal (SharePoint & native)
- Dashboards (SSRS & Power View)
- Reports (SSRS & other tools)
Introducing Self-Service Semantic Models
- Introduce xVelocity (briefly compare PowerPivot & Tabular SSAS)
- Introduce the PowerPivot add-in
- Importing data
- Accessing & integrating data sources
- Add an Excel linked table
- Add relational, multidimensional & text files
- Use an SSRS report Atom Feed
Build a PowerPivot Model
- Import tables from multiple sources
- Model relationships
- Set table & column properties
- Create calculated columns
- Create simple calculated measures using auto sums
- Browse the model using an Excel pivot table
- Set data types, formatting & basic content types
Advanced PowerPivot Properties
- Create KPIs
- Create hierarchies
- Browse the model using Excel
Sharing Models & Collaboration
- Deploy the Excel model to SharePoint
- Discuss settings, security & reporting options
- Demonstrate the PowerPivot Gallery
- Refresh options & scheduling
Introduce Power View
- Create basic reports in Power View
- Add tables, matrices, column & bar charts
- Convert tables to slicers & analyze measures
- Add a KPI
- Use a hierarchy to drill-down in a Power View matrix
Use Advanced PowerPivot Features to Enable Power View Visuals
- Add geographic content types
- Add date part content types & a hierarchy
- Create a map visual with a geographic hierarchy
- Visualize geographic hierarchies with a drill-down map
- Visualize data points using latitude & longitude
- Add Power View filters
- Add multiples
- Add cards & panels
- Create multiple views in Power View
- Style a Power
Introducing DAX Calculations
- Relationship navigation
- Contextual filters
- Time intelligence functions
- Advanced & conditional aggregation
- Add a time intelligence calculation (rolling summary, parallel period, etc.)
- Create a hierarchy with calculated date columns
- Browse the added calculations & columns using Power View
Using Excel as a BI Dashboard & Reporting Tool
- Use the Excel pivot table with measures & calculations
- Use advanced pivot charts
- Add slicers
- Conditional formatting rules & visuals
- KPI indicators
- Deploy Excel to SharePoint and open report in browser
- Discuss sharing & collaboration options for Excel Services
Tool Choices & Criteria
- Solution options with SharePoint and without SharePoint
- Discuss the criteria for choosing among different tools for browsing & presenting results.
- Create a dashboard using SSRS Report Builder
- Create dashboard elements using Report Builder
- Balanced scorecard with KPI indicators & drill-down
- Gauge panel (bullet graphs)
- Column or line chart for trending & comparisons
- Create dashboard elements using Report Builder
- Use Power View to create a dashboard
- Considerations for mobile dashboard design & self-service using tablet devices (iPad & Windows Surface)
Enterprise Scale
- Migrating PowerPivot to a tabular model
- Introduce partitioning
- Introduce SSAS security
- Introduce perspectives
- Discuss advanced business solution scenarios
Final Steps & Summary
- IT & business partnering, support tasks
- Business user education
- Report sandboxing & branding
- Periodic report review & consolidation
- Planning your first implementation
