Required Slide SESSION CODE: BIP202 Designing and Building an End-to-End Business Intelligence Solution with Microsoft SQL Server 2008 and SharePoint Services Paul Turley BI Architect / Manager Hitachi Consulting
The Business Data Continuum Reports, Charts, Dashboards & Scorecards Relational Data Warehouse Data Consolidation & Transformation(ETL) OLAP Cubes Operational Databases
Right-Sizing Solutions Business Needs
Overcoming Process Over-Engineering Experience has taught us (and many of our clients): • The perfect solution may be the one you can implement today while designing the “perfect” solution for tomorrow • Many business look for the “perfect” data warehouse solution (while they lose business due to lack of direction & profitability) • Short-term solutions can solve immediate problems while longer-term solutions are architected • Use iterative design techniques • Plan for scalability • Encourage executive sponsorship & requirement ownership
Let’s Go Shopping! Web form at sqlserverbiblog.com US Northwest Excersize
BI Solution Design Phases & Components SQL Server Relational Integration Services AnalysisServices ReportingServices SharePointServices
Data Warehouse Table Schemas Star Snowflake Relational De-normalized / Simplified Normalized
Dimensional Data Warehouse Design • SQL Server 2008 includes specific optimizations for data warehouse models • Data type and tuning enhancements • Sparse data optimization • Database compression Date Dimension Customer Dimension EmployeeDimension ProductDimension Sales Fact GeographyDimension VendorDimension
Identify business facts Measures Identify dimensions Attributes Hierarchies Dimension Dimension Dimensional Modeling Dimension Dimension Fact Dimension Dimension Exercise
Data Transformation with SSIS Package • Rapid design • Intuitive & maintainable • Handles complex logic Data Flow Control Flow • SQL Server 2008 includes significant performance and memory management optimizations • Support for script programming using C# Loop Container
Report Design • Business user & developer capabilities • Data Sources • Server Data Sources (relational, cubes, models, SP lists) • Server Reports • Report wizards • Shared datasets • Report part gallery • Sparklines & KPIs • Maps
Required Slide Track PMs will supply the content for this slide, which will be inserted during the final scrub. Track Resources • Hitachi Consulting hitachiconsulting.com • Paul’s blog sqlserverbiblog.com • MSDN forums social.msdn.com/Forums • SQL Server • Integration Services • Analysis Services • Reporting Services • SharePoint • PowerPivot
Required Slide Resources Learning • Sessions On-Demand & Community • Microsoft Certification & Training Resources www.microsoft.com/teched www.microsoft.com/learning • Resources for IT Professionals • Resources for Developers • http://microsoft.com/technet • http://microsoft.com/msdn
Required Slide Complete an evaluation on CommNet and enter to win!
Sign up for Tech·Ed 2011 and save $500 starting June 8 – June 31st http://northamerica.msteched.com/registration You can also register at the North America 2011 kiosk located at registrationJoin us in Atlanta next year
© 2010 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.