“Intelligent” Business Intelligence Solutions

1. “Intelligent”Business Intelligence Solutions Being more Productive with Microsoft BI Tools Robert DennettStrategic Information Manager

2. 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!!

3. My 20 Year BI JourneyWhy 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)

4. Analysis ServerThe 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!

5. Analysis ServerMore than a data server…. LimitedView of Analysis Server Transactions Data (Relational) Data (MOLAP) Data(Reports)

6. Microsoft BI ToolsAdd “intelligence” at every layer …. • Advanced Analytics • Data Mining • Forecasting • Data merging • Intelligence Navigation • Delivery over the Net • Off line OLAP • Data Enrichment Relational Data (SQL Server) MOLAPROLAPHOLAP (Analysis Server) Knowledge / Insights (SharePoint / Aladdin MS Office + More..)

7. Nielsen COG ABS E-Mail Ftp RPC Win Copy Http Our End-to-End BI SolutionIntelligently leverage tools to lower incremental investment Core Business Transactions Firewall 3rd Party Data Internet Oracle AS/400. RemoteWin NT File Server RemoteWinOfficeSOE EmailAgent OfflineOLAP DTS Load & Map Data Unix OLAP Compass SQL Warehouse Other Security Domains Our Domain MarketingBusinessDictionary PC BasedTransaction SharePointCompass Portal Win2KOfficeSOE EmailAgent DocLibraries Other

8. Make Relational Data IntelligentAn attribute rich relational data model adds analytical power …. • Go well beyond transaction data • Capture analytical business meta data • Entities & Attributes = Analysis Dimensions • Analysis Dimensions = Business Value • Be creative • 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

9. Make Analysis Server Intelligent • Three key things • MDX – Multidimensional Expression LanguageMDX – Multidimensional Expression LanguageMDX – 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

10. Essential References

11. Using MDX to Tame “Time” • “Time” hierarchy dimension • Day / Week / Month / Year • Common OLAP problem is how best to manage “Time” aggregates? • 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

12. Time “Utility” DimensionUse MDX to create relative time aggregates on the fly…. Customer Time Year Month Week Day Periodicity Current YTD MTD MAT

13. Time “Utility” DimensionUse MDX to create relative time aggregates on the fly…. Customer Time No Groupingof time Year Year to Date as at eachDay/Week/Monthfor every customer Month Week Day Periodicity Current YTD MTD MAT

14. MDX Syntax Samples • Create a new “Periodicity” calculated member • YTD – sum from first day of Year to current time • MAT – sum from this day a year ago (plus 1day) to current time SUM ( PeriodsToDate([Time].[Year], [Time].CurrentMember) ) SUM( { ParallelPeriod( [Time].[Year], 1, [Time].CurrentMember).NextMember : [Time].CurrentMember } )

15. 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 • End users can directly tap benefits of data mining using everyday tools like Excel Sales

16. Sample Data Mining Output Top 2 factors … Sales

17. Sample Data Mining Output Top 3 factors … Sales

18. 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 • Laptop users – sales reps • Share BI with customers & suppliers • Where web browsing is NOT possible • Use same client tools as server cube. • Local cubes have limitations. Plan ahead to avoid these • 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

19. Creating “Offline” OLAP CubesHarness powerful low cost 3rd party tools • 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 TaskSDG Computing, Inc. http://www.localcubetask.com/index.htm

20. Local Cube TaskHighly functional tool at bargain basement prices!!

21. 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

22. Take OLAP to the WebAgain, 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

23. OLAP Over the Web

24. Delivering Integrated BIUsing 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

25. Linking Analysis Server & SharePointAdd 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

26. 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….