8- SQL-BI-SkillTransfer(内部资料)

BI/BSC行易SQL技術轉移楊志強台灣微軟特約資深講師MCT/MCSD/MCDBAMicrosoftBI架構ManagementToolsDevToolsVisualStudio.NetExcelVisioProjectOWCSharePointPortalServerProjectServerDataAnalyzerWindowsServerWindowsClientMicrosoftBusinessSolutionsBIApplicationsBusinessScorecardsBIPortal2003ExcelOLAPReportingSolutionAcceleratorsSQLServerRelationalEngineReportingServicesAnalysisServicesOLAP&DataMiningOfficeSystem微軟商業智慧平台市場佔有率Source:OLAPReport(August18,2003)byNigelPendseBusinessIntelligencePlatformsMagicQuadrant(August2002)NO.1SQLServer2000ReportingServices報表服務SQLServer2000完整的企業資料庫解決方案AnalysisServicesDataTransformationServicesXMLsupport,32/64bit開發工具管理工具OLAP&DataMining商業智慧資料轉換服務資料庫報表服務ReportingServices報表服務-完整的三個階段SQLServerCatalogReportServer報表服務架構DeliveryDeliveryTargets(E-mail,Fileshare,Custom)SecurityServices(NT,Passport,Custom)SecurityDataSources(SQL,OLEDB,ODBC,Oracle,Custom)OutputFormats(HTML,Excel,PDF,Custom)ReportProcessingBrowserManagementCustomAppURLWMIWebServiceDataRetrievalRendering報表製作使用MicrosoftVS.NET2003或任何支援RDL的3rdpartytools單一報表可支援同時使用多種資料來源(Access,OLEDB,ODBC,Oracle,and.NETdataproviders)多樣化報表多樣化工具Freeform/Table/Matrix/Chart參數化報表(Dynamic,Hierarchical)Sorting/Filtering/Grouping/Aggregates互動化報表(Drill-down,Drill-through)在報表製作環境中可直接進行報表預覽報表製作完成可直接公佈至ReportServer提供RDLXML標準具備客製擴充性報表傳送提供On-demand(“pull”)和event-based(“push”)的傳遞方式提供多樣化報表格式(HTML,WORD,PDF,TIFF,CSV,EXCEL,XML)提供報表訂閱機制使用者可自行選擇報表傳送方式、格式及時間傳送內容可為報表超連結、附加檔案或內嵌整份報表提供”個人化報表”系統預設訂閱傳送方式E-mail傳送方式FileShare可客製化報表管理管理程式為WebServiceAPIs提供Web的使用者管理介面報表管理程式功能排程機制整合SQLServerAgent提供最有效能的報表處理機制(live,cacheorsnapshot)提供歷史報表管理機制,提供報表稽核及保存之用Role-based安全機制報表系統於企業應用的最新動態金融業•VIP客戶報表•客戶關係管理•安全性•自動傳送報表(訂閱/定時)•客戶從入口網站訂閱報表•取代傳統的帳單所有行業•取代傳統紙張的報表•使用者可自行訂閱報表•成為真正的資訊工作者政府單位•電子表單•公文/文件的傳遞•大型資訊系統的管理報表電信業•電子帳單製造業•生產製程的管理報表零售業•銷售業績管理報表SQLServer2000報表服務demoBIPortal2003提供完整企業資料(OLAP/Relational/Filesetc.)的視覺化(Pivottable,Chart,Gauges,Spreadsheet…)商業智慧整合方案TreeViewDataAnalysis我的最愛各種檢視BIPortal2003與Office整合BIP2003Add-inBIPortal2003與Office整合BIP2003Add-in微軟平衡計分卡介紹MicrosoftBalancedScorecardFrameworkBSC的四個構面VisionandStrategyObjectivesMeasuresTargetsInitiatives財務“Tosucceedfinancially,howshouldweappeartoourshare-holders?”ObjectivesMeasuresTargetsInitiatives學習與成長“Toachieveourvision,howwillwesustainourabilitytochangeandimprove?”ObjectivesMeasuresTargetsInitiatives顧客“Toachieveourvision,howshouldweappeartoourcustomers?”ObjectivesMeasuresTargetsInitiatives內部流程“Tosatisfyourshareholdersandcustomers,whatbusinessprocessesmustweexcelat?”Source:TheBalancedScorecardCollaborative股東與顧客的外界衡量有關重大企業流程,創新能力,學習及成長的內部衡量衡量過去努力成果驅動未來績效策略地圖&因果關係Cause&EffectTheme:Smart,ProfitableExpansion50%RevenuefromNewStoresbyYear3IncreaseSalesEfficiencyDevelopmentProjectManagementGreatNewLocationsCorporateDigitalNervousSystemBusinessIntelligenceUseFact-basedsiteselection%revenuefromstoresopenedinlast3yearsRevenuefromnewstoresAvg.#ofdaystobreakevenRevenueperFTEAvgdailycustomersatnewstoresinfirst6months#repeatcustomersAvgpurchaseamountLagbetweenmarketselectionandsiteacquisitionProjectduration:siteacquisitiontoopeningday%ofstoresopenedonschedule#EligibleemployeestrainedFinancialPerspectiveCustomerPerspectiveOperationalPerspectiveEmployees&TechnologyPerspectiveTheBusinessScorecardsolutionisasolutiontoarticulate,communicate,drive,andmeasurestrategyArticulateDefinethestrategy:StrategyMapBuilderBuildsthescorecards:ScorecardBuilderManageinfoaboutstrategy:DocumentLibrariesCommunicateIntegratedintoCollaborativePortal:SharePointv2DisplaysKPIs:DashboardwebpartsDriveRelevanttotheindividual:PersonalizationviaSharePointActionableandInteractive:BIPlatform(drilldown,slice,analyze)MeasureInfrastructuretomanagescorecardsandmetrics:ScorecardEngineDataManagement:SQLServer2000,AnalysisServicesScorecards:商業價值ScorecardBuilder提供完整工具建置平衡計分卡Scorecards:SolutionArchitecture•UIforenteringyourscorecarddata•WebServiceScorecardBuilder•DataentryinterfaceformanuallyenteredtargetsDataEntry•Deployyourscorecards•Customizeyourviews•SharePointPortalServer•Solution-specificwebpartsScorecardPresentationScorecardEngine•UnderstandsyourBSCdata•Buildsthescorecard•UIforbuildingyourstrategymaps•VisioStrategyMapBuilder•DefineKPImappingsanddatasourcesDataSourcesScorecardDB•Capturesinformationaboutyourscorecard•SQLServerSourceSystemsScorecardCubes•Scorecardcube(s)•RecommendedSSABI-based,customizable微軟平衡計分卡策略地圖(StrategyMap)ExcelOLAPReportingAccelerator提供使用者利用Excel即可快速產生彈性化及多樣化的商業報表為OfficeXP&Office2003的Add-in與SQLServerAnalysisServices整合功能可連結多個不同的OLAP資料來源(DataSource,Cube,Dimention)可在報表內容中隨時加入Row和Column可進行條件篩選(Top5,前10%)可引用Excel內建的formatting和sorting功能可使用CrossJoin方式存取資料可使用AnalysisServices的”動作(Actions)”,Dillthrough,WritebackExcelOLAPReportingAccelerator強大的商業智慧整合解決方案-BIPortal2003-ExtendingOWCWordExcelSQLReportserver&OWCcharts微軟完整的商業智慧整合解決方案平衡計分卡報表系統商業智慧績效衡量(KPI)SQLServer2000商業智慧整合系統demo結論微軟商業智慧解決方案能快速地將既有的線上作業資料轉換成有用的決策分析資料BusinessScorecards企業策略導入及監控執行效能的強大工具SQLServer2000ReportingServices彈性的訂閱和傳送選項搭配視需要的報表功能，可確保使用者輕鬆地存取最新的企業資訊。BIP2003/ExcelOLAPReporting提供完整企業資料(