TF04 - Leveraging SQL Server Business Intelligence Tools

(1)

PUBLIC INFORMATION

TF04 - Leveraging SQL Server

Business Intelligence Tools

(2)

www.rsteched.com

Follow RSTechED on Facebook & Twitter.

Connect with us on LinkedIn.

PUBLIC INFORMATION

Commercial Engineering

Le Roy Howe

(3)

Self Service Reporting

Report Builder

Power View

PowerPivot

Application Notes

Self Service Reporting Overview

(4)

The Microsoft Self-Service Reporting

Stack

4

 A perfect ad hoc reporting tool is the goal and Microsoft has a lot of tools.

 PowerPivot

 Excel Add in

 Power View

 PowerPoint

 Excel Add in – 2013

 SQL Server 2012

 Report Builder

 PerformancePoint

 SharePoint Enterprise

(5)

Tool Summary

5

Requirements Reporting Tools Summary

Plant Floor Reporting Report Builder Tabular data, forms, dashboards,

requires expertise but does not prevent

users from accessing advanced features.

Power View Table, matrix and forms.

PowerPivot / Excel Charts, pivot tables integrate multiple

sources with refresh. Easy to use.

Ad Hoc PowerPivot / Excel Ideal for summarizing data using slicers

to change pivot tables and charts

dynamically.

Report Builder Report wizards integrated with query

designer enabling simple table, matrix

and chart reporting. Any user can take

advantage of these base features now.

Power View Easy to use, dynamic visual tool needs a

data model.

Dashboards PerformancePoint Decomposition tree is a fantastic way to

explode details in a visually stunning and

impactful display.

Report Builder Business users can assemble a report

from report parts simply. Report Parts

and advanced features usually require IT.

(6)

PowerPivot

6

 PowerPivot

 Combine data and perform calculations on large data sets from your

desktop.

 Portable and fast

 Slicers, filters, charts

 Supports DAX (Data Analysis Expressions)

 Feels like Excel expressions

 All data is loaded on the your computer in a compressed format

enabling you to work with several million rows of data.

(7)

Power View

7

 Power View

 Add charts, trends and create tables / matrix reports quickly

 Change them from one type to the other

 Visually enhanced charts

(8)

Report Builder

8

 Business user focus

 Create charts, tables and others

 Quick and easy

 Show case what every use should be able to accomplish

 Assemble Report Parts

(9)

Data Source – FT Metrics

9

 Factory Talk Metrics

 Basic Track and Trace

 Batch system including mixer, oven and packaging.

 We will focus on the Mixer.

 Standard Configuration

 Metrics Event Configuration.

 Reporting Options

(10)

Configuration Overview

10

 Record Batches in total with component usage including cost.

 Create one event for each component.

 Actual lbs.

 Recipe / Target lbs.

 Metrics configuration

 Event Data Summary – No summarization

 Event Trigger – on high transition – standard batch complete bit.

 Event Value – Start Value – any component value

 Machine State / Faults – none

 Reporting – Assign a severity level for report filtering.

 Notice the event description and category names.

(11)

Event Data Summary Tab

11

(12)

Event Trigger Tab

12

(13)

Event Value Tab

13

(14)

Machine State / Faults Tab

14

(15)

Reporting Tab

15

(16)

PowerPivot SQL Connection

16

(17)

PowerPivot Demo

17

 Batch Log

 Selectable by Mixer, Production Day and BatchID – these are slicers.



(18)

PowerPivot Demo

18

 We will build a report

(19)

PowerPivot Demo

19

(20)

Power View

20

 Quickly discover new ways to visualize your data now.

(21)

Power View Demo

21

 Lets build something now.

(22)

Report Builder Demo

22

 All business users can get something out of this tool.

 Web based tool not Visual Studio

(23)

Report Builder Demo

23

 Build this report in 15 Minutes

(24)

Report Builder Demo

24

(25)

Closing Thoughts

25

 Business users have more tools to uncover business knowledge than

every before.

 We have only shown a few that leverage our data models / products.

 Speed in reporting is combined with driving business value now.

 Imagine what you could learn with a little bit of time and effort.

(26)

www.rsteched.com

Follow RSTechED on Facebook & Twitter.

Connect with us on LinkedIn.