Transcript BI on a Dime - The RP Group

BI on a Dime
OR HOW I LEARNED TO STOP
WORRYING AND LOVE BI
Agenda

Get up to speed on terms

Feather River College case

See business intelligence in action

Strategy – Architecture – Infrastructure

Data Warehouse ERD Exercise
What are we talking about?

Business Analytics: is the extensive use of data,
statistical and quantitative analysis, explanatory
and predictive models, and fact-based
management to drive decisions and actions.

Business Intelligence (BI): A set of technologies
and processes that use data to understand
and analyze business performance. BI includes
both data access and reporting, and analytics.
What are we talking about?
What are we talking about?
Last slide on this, I promise!

Transaction Database: A system
designed to process transaction data.
Optimized for putting data into the
system quickly.

Data Warehouse: A system designed to
store validated data for the long haul.
Optimized for fast querying.

Data Mart: A user interface to present
data. Should be designed to be intuitive.
FRC Case

First: Where the
hell is Feather River
College?
FRC Case

The Problem: The institution had matured
to the point that it was good at putting
data into the ERP system – Banner.
Unfortunately we weren’t any good at
getting it out.

Limited resources:

Staff

Money

Technical Expertise
FRC Design and Implementation

Sample – Data Flow Diagram
Data Flow Diagram
FRC Design and Implementation

SQL Server - Parts
SSIS
Management
Studio
Analysis
Services
Reporting
Services
FRC Design and Implementation

It’s Demo Time
 Finished
Examples
A QUICK Lesson in Database
Design

Databases power this stuff so you should learn about how
to design them

Scenario: You want to track which employees are on
committees and when they joined or left the committee.
EmployeeID
EmployeeID
FirstName
LastName
Committee
WhenStarted
WhenLeft
FirstName
LastName
Committee
WhenStarted
12345 Brian
Murphy
SLOAC
4/1/2010
12345 Brian
Murphy
SEM
4/1/2010
12345 Brian
Murphy
SPC
4/1/2010
WhenLeft
4/1/2014
A QUICK Lesson in Database
Design

Start breaking data out by dependency

Look at relationships
Committee
Employee
*EmployeeID
FirstName
LastName
?
*CommitteeID
CommitteeName
A QUICK Lesson in Database
Design

Because employees can serve on multiple committees
and committees have multiple employees on them, we
need an linking table.
Employee
*EmployeeID
FirstName
LastName
EmployeeCommitte
*EmployeeID
*CommitteeID
StartDay
EndDay
Committee
*CommitteeID
CommitteeName
FRC Design and Implementation

It’s Demo Time - Making Sausage

FRC ERD

FRC SQL Server Stuff
Ideas for Development
Remember the audience

Different data marts for different folks

Understand at a glance

Simplify


You still have IR to take care of the hard stuff
Train
Ideas for Development
Fast,
Inexpensive,
Always improving,
Always on
Ideas for Development
Minimum Information System Requirements

SQL Server 2012 (Enterprise Edition)


Adequate ram very important
SharePoint 2010 (Enterprise Edition)

Running on SQL Server 2012 platform

Silverlight installed on clients

Communication between servers

Administrative rights on SQL Server
Ideas for Development
Future Compliant Information System
Requirements

SQL Server 2012 (Enterprise Edition)


Adequate ram very important
SharePoint 2013 (Enterprise Edition or adequate
Office 365 version)

Running on SQL Server 2012 platform

Office 2013

Communication between servers

Administrative rights on SQL Server
Ideas for Development

Suggested Reading

Murach’s SQL Server 2012 for Developers

Delivering Business Intelligence with Microsoft SQL
Server

Competing on Analytics
Wrap Up
Thank
you!