Transcript “Intelligent” Business Intelligence

“Intelligent”
Business Intelligence Solutions
Being more Productive with Microsoft BI Tools
Robert Dennett
Strategic Information Manager
Agenda



Personal historical perspective
BI and relational data management
Analysis Server power intelligently




Handling time within OLAP cubes
Merging / integrating data within OLAP
Data Mining Models
Delivering BI /OLAP intelligently



Sending OLAP “offline”
OLAP over the web
Linking with SharePoint
Please ask
questions at
any time!!
My 20 Year BI Journey
Why things are now so exciting …

The Vision is Born

John Little (1971) founder of IRI Express software
“Models and Managers: the concept of a decision calculus “

Elusive “Express” years – What’s a Prime?

“Powerplay” - to the desktop but no scale..

“Holos” – when being “co-operative” becomes difficult!

“PC Express” – Solid, but alas an island!

The Odds and Sods ….

Microsoft OLAP Server Arrives (1999)
Analysis Server
The BI “Engine Room” …..

New levels of price performance - scalable

Open interfaces – back, front and middle

Powerful hierarchy & calculation functions

Data mining tools

Easy to deploy & use

Challenge: Be “intelligent” in use.
Deliver the business benefits – at last!
Analysis Server
More than a data server….
Limited
View of
Analysis
Server
Transactions
Data
(Relational)
Data
(MOLAP)
Data
(Reports)
Microsoft BI Tools
Add “intelligence” at every layer ….
Data Enrichment
Relational Data
(SQL Server)
Advanced Analytics
Data Mining
Forecasting
Data merging
MOLAP
ROLAP
HOLAP
(Analysis Server)
Intelligence Navigation
Delivery over the Net
Off line OLAP
Knowledge / Insights
(SharePoint / Aladdin
MS Office + More..)
Our End-to-End BI Solution
Intelligently leverage tools to lower incremental investment
Core Business
Transactions
Nielsen
COG
ABS
AS/400.
Offline
OLAP
DTS Load
& Map Data
Unix
OLAP
Our Domain
E-Mail
Ftp
RPC
Win Copy
Http
Doc
Libraries
SharePoint
Compass Portal
Email
Agent
Remote
Win
Office
SOE
Other Security Domains
Compass
SQL
Warehouse
Marketing
Business
Dictionary
PC Based
Transaction
Other
Email
Agent
Internet
Remote
Win NT
File Server
Oracle
Firewall
3rd Party Data
Win2K
Office
SOE
Make Relational Data Intelligent
An attribute rich relational data model adds analytical power ….


Go well beyond transaction data
Capture analytical business meta data



Be creative



Entities & Attributes = Analysis Dimensions
Analysis Dimensions = Business Value
Add Supplier / Customer’s view of the world
Add Department specific attributes
Future proof the data design


Add many-to-many tables around key entities for
yet “unknown” attributes
Adhere to proper normalisation
Make Analysis Server Intelligent

Three key things

MDX – Multidimensional Expression Language
MDX – Multidimensional Expression Language
MDX – Multidimensional Expression Language

Think dimensionally!!

Benefits of MDX




Eliminate procedural programming – meet changing user needs
fast
Cost off the client – the analytical power is on the server
Create new options for data delivery – web / remote
Very powerful functions including data mining
Essential References
Using MDX to Tame “Time”

“Time” hierarchy dimension


Common OLAP problem is how best to
manage “Time” aggregates?




Day / Week / Month / Year
Year to Date - YTD
Month to Date - MTD
Moving Annual Total – MAT
Solutions ?



Aggregate & store “time” totals as MOLAP data
User sum “time” members in the client.
Create a “Time Utility” dimension & unleash MDX
Time “Utility” Dimension
Use MDX to create relative time aggregates on the fly….
Customer
Time
Year
Month
Week
Day
Periodicity
Current
YTD
MTD
MAT
Time “Utility” Dimension
Use MDX to create relative time aggregates on the fly….
Customer
Time
No Grouping
of time
Year
Year to Date
as at each
Day/Week/Month
for every
customer
Month
Week
Day
Periodicity
Current
YTD
MTD
MAT
MDX Syntax Samples


Create a new “Periodicity” calculated member
YTD – sum from first day of Year to current time
SUM ( PeriodsToDate([Time].[Year], [Time].CurrentMember) )

MAT – sum from this day a year ago (plus 1day) to
current time
SUM( { ParallelPeriod( [Time].[Year], 1, [Time].CurrentMember).NextMember
: [Time].CurrentMember } )
Analysis Server Data Mining Tools
Mine Data Attributes to Extract Hidden Knowledge
Out of the box functionality
Which input(s) best
“explain” or predict changes
is some key output measure.
Tightly integrated feature
Sales
End users can directly tap
benefits of data mining using
everyday tools like Excel
Sample Data Mining Output
Top 2 factors …
Sales
Sample Data Mining Output
Top 3 factors …
Sales
Use MDX to Take Analysis “Offline”
& onto the road


Use MDX to create “cube files” which are
then independent of Analysis Server
Powerful method of delivering remote BI





Local cubes have limitations. Plan ahead to
avoid these




Laptop users – sales reps
Share BI with customers & suppliers
Where web browsing is NOT possible
Use same client tools as server cube.
Size – calculated members become real data
Security – there is none!
Default members and roles can cause a few
problems
Create Cube syntax is somewhat complex
Creating “Offline” OLAP Cubes
Harness powerful low cost 3rd party tools
Local Cube Task
SDG Computing, Inc.
http://www.localcubetask.com/index.htm
SQL Server DTS Add-in
“LocalCube Task”
Complete “shrink wrap”
solution :•Defining local cubes
•Production updates in DTS
•Managing Cube
Distribution
Compression
Email
File copy
•Build & pack OWC web
pages along with data
Local Cube Task
Highly functional tool at bargain basement prices!!
Take Analysis onto the Web


Analysis Server / IIS can be linked
through ASP
Very powerful method of delivering BI
using only a web browser




Zero client - HTML
Empower wireless laptop / PDA users
Customers, suppliers, sales reps….
OLAP over the web has few limitations


Security – authentication firewall issues
User expectations for fancy graphics
Take OLAP to the Web
Again, harness powerful low cost 3rd party tools
Strategic Data Management’s “Aladdin”
http://aladdin.sdm.com.au/





IIS ASP Engine
Takes MDX
queries, renders
HTML output
Includes starter
templates
SQL Report
Repository
Write you own
ASP pages
OLAP Over the Web
Delivering Integrated BI
Using a SharePoint Portal as “BI umbrella”


All data cannot be integrated into one cube
BI “using experience” can be integrated into
single using environment




OLAP reports (hard data)
Business plans, research reports (soft data)
Blended – business plans with dynamic OLAP
content
Avoid user BI tool clutter



The power is on the server not the client!
Reduce BI costs significantly
A case for no more than two tools


MS Office / Excel
1 Other
Linking Analysis Server & SharePoint
Add Intelligence to SharePoint Subscriptions

Use SQL DTS
Package

Update OLAP cube

Refresh pivot table

“Check In” to
SharePoint

SharePoint
“subscribers” receive
notification email
with URL to new data
Final Thoughts - Dr Codd (1994)
White paper in which he developed the term “OLAP”…
“Attempting to force one technology or
tool to satisfy a particular need for
which another tool is more effective
and efficient is like attempting to drive a
screw into a wall with a hammer when a
screwdriver is at hand:
…. the screw may eventually enter the
wall but at what cost?”
Thank you Dr Codd….