Designing and Business Solution by using Microsoft SQL Server 2008

Designing and Managing Reports

• Design report architecture:
  linked reports, drill-down reports, drill-through reports, migration strategies, access report services API, sub reports, Code-Behind strategies
• Design a data acquisition strategy:
  data query parameters, creating appropriate SQL queries for an application (MDX queries), managing data rights and security, extracting data from Analysis Services, balancing query-based processing vs. filter-based processing, managing data sets through the use of stored procedures
• Define a report parameter strategy:
  report cache parameters, expressions, Code-Behind
• Design a report layout:
  selecting report components (crosstab report, TABLIX, design chart), report templates (Report Definition Language), Report Designer, generating report template RDL, adding third-party controls, Visual Studio Report and Development platform
• Design reports by using Report Builder:
  designing a Report Builder model, using Report Builder templates
• Manage a report environment:
  specifying users and roles, subscriptions strategy, designing data sources, designing a deployment plan for completed reports, integrating Microsoft Office SharePoint Server 2007 or Microsoft Windows SharePoint Services 3.0

Designing Data Mining Models

• Design a mining model and structure:
  assigning a data source, specifying filter properties, reconciling heterogeneous data sources, selecting a refresh strategy (partial or full)
• Design strategies for staging data for mining:
  selecting a method for cleaning data (closed-loop process), specifying partitioning of data into testing and training sets
• Select a strategy for visualizing data mining results:
  DMX queries (drill-through queries, structured and unstructured columns, column aliasing), data mining Microsoft Office Excel add-in, designing a report by using reporting services
• Select data mining algorithms:
  sequence, clustering, time series, neural net, association, classification, decision tree
• Refine testing models:
  applying predictions, analyzing results, performing cross-validation

Administering a BI Solution

• Maintain server health:
  Resource Governor, performance counters, Analysis Server dynamic management views (DMV)
• Select a subscription strategy:
  data-driven rights management
• Manage integration services:
   loading and scheduling an SSIS package to run, SSIS storage solutions, migration of packages
• Design a backup strategy:
  offsite backup, setting up SSAS backup, Reporting Services backup (databases and keys)
• Plan and manage upgrades:
  data migration, code migration, data security, parallel systems
• Plan and manage reporting services configuration:
  Reporting Services Configuration Manager, SSL management, encryption key management, mail management, Web service interface management, configuration files

Designing the BI Architecture

• Design integration between data visualization components:
  MOSS, SSRS, SSAS, designing Excel PivotTable reports, application architecture (Microsoft ASP.NET, Web Service, MicrosoftReportViewer Windows Forms control), Windows Mobile, other data visualization tools (Microsoft ProClarity, Microsoft PerformancePoint)
• Design the data warehouse:
  datamarts, integrating legacy data, creating a dimensional model (star schema, snowflake, matrix bridge, conformed dimensions), utilizing upsert in design (TSQL-merge)
• Plan for scalability:
  selecting the appropriate edition of SQL server, selecting the right hardware topology (service distribution), designing the caching strategy (report snapshots, on-demand reports, on-demand-from-cache reports), managing historical data (report history, archiving), selecting the appropriate service host, designing resource utilization of the SSIS package, Read-Only Analysis Server scale-out
• Plan for performance:
  Resource Governor, Proactive Cache, selecting the appropriate storage settings (ROLAP, MOLAP, partition)
• Manage team development issues:
   integrating Microsoft Visual SourceSafe, TFS, VSTS for database pro, selecting BI entry points (SSIS, reporting services), online or offline cube deployment (synchronization)
• Plan the configuration of a primary data source:
  configuring CDC in a server, insert over DML
• Design a security strategy:
  cube, project, reports (security roles, folders, field-level security), encryption keys, SQL services accounts management, encrypted data transmission, SSIS vs. SQL server authentication issues (derail Trojan attacks)

Designing and Deploying SSIS Packages

• Design control flow:
  tasks and precedence constraints, fuzzy logic control, maintenance tasks, accessing remote data (Web services, FTP), using appropriate variables, data profile
• Design data flow:
  data transformation, blocking transformations (lookup task), extractions (design constraints), designing the load process, creating a package to load facts, creating an SSIS package to load dimension (SCD2), using capture CDC to locate changed data for SSIS
• Plan the deployment of SSIS packages:
  design the structure of packages, troubleshoot and design form problems with package, create a package that reports any errors to a log
• Design the configuration file for an SSIS package:
  enabling configurable values to be consumed by a package
• Design a logging and auditing strategy:
  exception handling, data flow exceptions

Designing an Analysis Services Database

• Design cube architecture:
  creating and populating a cube, creating KPIs, actions, calculated members, drillthrough, designing SSAS aggregation, creating and manipulating dimensions (Ragged hierarchy, Flexible, Rigid, Semi-additive, Periodicity, Fact Relationships), selecting a processing mode
• Design for international implementation:
   currency conversion, translation, localization
• Design a data source view:
  named queries, denormalization strategy
• Design perspectives:
  enhancing usability through the use of perspectives
• Design and create business-driven Multidimensional Expressions (MDX) calculations:
  calculated measures, creating a named set in MDX
  Analyze cube performance:
  optimizing SSAS aggregation, query cube design
  

I Candidati a questo corso hanno 2 o 3 anni di esperienza in:

• SQL Server Analysis Services (SSAS), SQL Server Integration Services (SSIS), e SQL Server Reporting Services (SSRS)
• Utilizzo di SQL Server 2005 and SQL Server 2008 Business Intelligence Development Studio (BIDS)
• Progettazione in entrambi gli schemi  relational e multidimensional database
• Scrittura delle queri relational e multidimensional
• Progettazione e costruzione dei cubi
• Progettazione e creazione dei reports
• Applicazione data mining algorithms per business problem domains
• Aggregazione data da risorse multiple all'interno di un data warehouse in un ambiente data eterogeneo
• Progettazione e gestione di soluzione eterprise-level per databases multidimensionali
• Coordinare e interpretare i business requirements
  

Il corso prepara all' Esame 70-452: PRO: Designing a Business Intelligence Infrastructure Using Microsoft SQL Server 2008 per la certificazione

Microsoft Certified IT Professional (MCITP): Business Intelligence Developer 2008