MSBI

SQL Server 2005 Analysis ServicesWhat Is Microsoft BI?Core concept – BI is the cube or UDMExample cube as seen using Excel pivot tableMS BI is comprehensive – more than Analysis Services on SQL ServerDemonstration of SQL Reporting Services with cube as data sourceOLAP ModelingModeling source schemas—stars and snowflakesUnderstanding dimensional modeling— Dimensions (Type 1, 2, or 3) or rapidly changingUnderstanding fact (measures) and cube modelingOther types of modeling—data mining etc…Using SSAS in BIDSUnderstanding the development environmentCreating Data Sources and Data Source ViewsCreating cubes – using the UDM and the Cube Build WizardRefining Dimensions and Measures in BIDSIntermediate SSASKPIsPerspectivesTranslations – cube metadata and currency localizationActions – regular, drill-through and reportingAdvanced SSASUsing multiple fact tablesModeling intermediate fact tablesModeling M:M dimensions, Fact (degenerate) dimensions, Role-playing dimensions, write back dimensionsModeling changing dimensions – Dimension Intelligence w/ WizardUsing the Add Business Intelligence Wizards – write-back, semi-additive measures, time intelligence, account intelligenceCube Storage and AggregationStorage topics – basic aggregations, MOLAPAdvanced Storage Design – MOLAP, ROLAP, HOLAPPartitions – relational and Analysis Services partitionsCustomizing Aggregation Design - Processing DesignRapidly changing dimensions / ROLAP dimensionsWelcome to the Real Time – Proactive CachingCube processing optionsBeginning MDXBasic syntaxUsing the MDX query editor in SQL Server Management StudioMost-used Functions & Common tasksNew MDX functionsIntermediate MDXAdding calculated membersAdding scriptsAdding named sets.NET AssembliesSSAS AdministrationBest practices – health monitoringXMLA scripting (SQL Mgmt Studio)Other Documentation methodsSecurity – roles and permissionsDisaster Recovery – backup / restoreClustering – high availabilityIntroduction to Data MiningWhat and why?Examples of using each of the 9 algorithms (MS Clustering, MS Decision Trees, Naïve Bayes, MS Sequence Clustering, MS Time Series, MS Association Rules, MS Neural Network)Data Mining dimensionsData Mining clientsProcessing mining modelsIntroduction to Reporting ClientsExcel 2003 Pivot TablesSQL RS & Report BuilderSPS RS web parts & .NET 2.0 report viewer controlsBusiness Scorecards 2005 & ProClarityFuture Directions – Integration with Office 12SharePoint 12 and ASReport Center (type of dashboard) uses KPIs, Reports, Excel Web, FilterExcel Services 12 and AS (Web Services)SQL Server 2005 Integration ServicesIntroduction to SQL Server Integration ServicesProduct HistorySSIS Package Architecture OverviewDevelopment and Management ToolsDeploying and Managing SSIS PackagesSource Control for SSIS PackagesSSIS for DBAs: Using SQL Server Management StudioThe Import and Export WizardImporting and Exporting DataWorking with PackagesDatabase Maintenance PlansCreating Database Maintenance Plans using SSMSScheduling and Executing PlansExamining Database Maintenance Plan PackagesChanges in SQL Server 2005 Service Pack 2Business Intelligence Development StudioLaunching BIDSProject templatesThe package designerThe ToolboxSolution ExplorerThe Properties windowThe Variables windowThe SSIS menuIntroduction to Control FlowControl Flow OverviewPrecedence ConstraintsThe Execute SQL TaskThe Bulk Insert TaskThe File System TaskThe FTP TaskThe Send Mail TaskAdvanced Control FlowContainers – grouping and loopingThe Web Service TaskThe WMI tasksThe Analysis Services tasksThe Execute Process TaskThe Execute Package TaskIntroduction to Data FlowData Flow OverviewData SourcesData DestinationsData TransformationsThe Copy Column TransformationThe Derived Column TransformationThe Data Conversion TransformationThe Conditional Split TransformationThe Aggregate TransformationThe Sort TransformationData ViewersVariables and ConfigurationsVariables OverviewVariable scopeSSIS system variablesUsing variables in control flowUsing variables in data flowUsing variables to pass information between packagesProperty expressionsConfiguration OverviewConfiguration optionsConfiguration disciplineDebugging, Error Handling and LoggingSSIS debugging overviewBreakpoints in SSISSSIS debugging windowsControl Flow: The OnError event handlerData Flow: Error data flowConfiguring Package LoggingBuilt-in log providersAdvanced Data FlowRevisiting data sources and destinationsThe Lookup TransformationGetting Fuzzy: The Fuzzy Lookup and Fuzzy Grouping TransformationsThe Multicast TransformationThe Merge and Merge Join TransformationsThe Data Mining Query TransformationThe Data Mining Model Training DestinationThe Slowly Changing Dimension TransformationExtending SSIS through Custom CodeIntroduction to SSIS scriptingThe SSIS script editorThe SSIS object modelScript in Control flow: The Script TaskScript in Data flow: The Script ComponentIntroduction to SSIS component developmentSSIS Package DeploymentConfigurations and deploymentThe deployment utilityDeployment optionsDeployment securityExecuting packages – DTExec and DTExecUISSIS Package ManagementThe SSIS ServiceManaging packages with DTUtilManaging packages with SQL Server Management Studio Scheduling packages with SQL Server AgentSQL Server 2005 Reporting ServicesIntroducing SQL Server Reporting ServicesTour of FeaturesReporting Services ArchitectureReporting Services TerminologyReporting Services EditionsTaking Reporting Services for a SpinCreating ReportsDeconstructing Reporting ServicesShared Data SourcesCreating Reports from ScratchCalculations and FormattingCreating ExpressionsUsing the Global CollectionsFormatting ItemsConditional FormattingGrouping and SortingCreating GroupsCalculating Totals and PercentagesInteractive SortingCreating Drill-Down ReportsReport ParametersCreating Report ParametersCreating Drop-Down ParametersMulti-Valued ParametersDebugging Parameter IssuesCreating Matrix Reports and ChartsCreating a Basic Matrix ReportMatrix SubtotalsCreating a Basic ChartExploring the Charting PossibilitiesManaging Reporting ServicesDeploying Reports and Data SourcesExporting Reports and PrintingUsing the Web-Based Report ManagerUsing SQL Server Management Studio-based Reporting Services ManagerReporting Services SecurityTwo Parts to Reporting Services SecuritySecuring Access to ReportsData Source SecurityProgramming Reporting ServicesThe Many Reporting Services APIsIntegrating Reporting Services into Applications using URL AccessUsing the Report Viewer ControlsAdvanced Reporting Services ProgrammingUsing the Reporting Services Web ServiceWorking with Custom AssembliesSnapshots and SubscriptionsCaching and SnapshotsCreating Standard SubscriptionsCreating Data-Driven SubscriptionsManaging SubscriptionsAd-Hoc Reporting with Report BuilderThe Report Builder ArchitectureCreating Report Model ProjectsRunning Report Builder