Hi,
Here am explaining the junk dimension.
A number of very small dimensions might be lumped together to form a single dimension, a junk dimension - the attributes are not closely related. Grouping of Random flags and text Attributes in a dimension and moving them to a separate sub dimension is known as junk dimension.
http://sqlblog.com/blogs/marco_russo/archive/2007/09/02/datetool-dimension-an-alternative-time-intelligence-implementation.aspx
MSBI (SSIS/SSRS/SSAS) Online Training
Thursday, March 19, 2009
SSIS - Using a checksum to determine if a row has changed
Hi,
How can I check if a record exists and if so, how can I check (quickly) if it has changed.
About this Posted by Phil Brammer.
http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed/
How can I check if a record exists and if so, how can I check (quickly) if it has changed.
About this Posted by Phil Brammer.
http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed/
CRM-Microsoft Dynamics CRM 4.0
Hi,
Last week, I attended MS-CRM-DYNAMICS training. It’s really very nice one.Microsoft Providing best services in ERP and CRM Products also.Just I have added some links regarding to the brief description about CRM-DYNAMICS 4.O
Official site:http://crm.dynamics.com/Microsoft Dynamics CRM-Historyhttp://en.wikipedia.org/wiki/Microsoft_Dynamics_CRMArchitecture:-Blogshttp://dmcrm.blogspot.com/http://ronaldlemmen.blogspot.com/2006/01/quick-create-export-functionality.htmlhttp://blogs.msdn.com/sanjayjain/archive/2008/02/21/microsoft-dynamics-crm-4-0-isv-mobile-connector-from-logotec.aspxInstallation and Deployment in Microsoft Dynamics CRM 4.0http://www.microsoft.com/learning/en/us/syllabi/8911afinal.mspx
Microsoft Dynamics ISV Architect Evangelismhttp://community.dynamics.com/blogs/crmisvarchevan/comments/10861.aspxhttp://crm/adventureworkscycle/sdk/list.aspxhttp://www.microsoft.com/learning/en/us/exams/dynamics/422.mspxhttp://blogs.msdn.com/mscrmfreak/http://www.mscrm.com.au/GettingStarted/Training/tabid/64/Default.aspxhttp://www.unitek.com/training/microsoft/crm/index.php?option=com_content&task=view&id=102&Itemid=79
Cheers,
Ravindra
Last week, I attended MS-CRM-DYNAMICS training. It’s really very nice one.Microsoft Providing best services in ERP and CRM Products also.Just I have added some links regarding to the brief description about CRM-DYNAMICS 4.O
Official site:http://crm.dynamics.com/Microsoft Dynamics CRM-Historyhttp://en.wikipedia.org/wiki/Microsoft_Dynamics_CRMArchitecture:-Blogshttp://dmcrm.blogspot.com/http://ronaldlemmen.blogspot.com/2006/01/quick-create-export-functionality.htmlhttp://blogs.msdn.com/sanjayjain/archive/2008/02/21/microsoft-dynamics-crm-4-0-isv-mobile-connector-from-logotec.aspxInstallation and Deployment in Microsoft Dynamics CRM 4.0http://www.microsoft.com/learning/en/us/syllabi/8911afinal.mspx
Microsoft Dynamics ISV Architect Evangelismhttp://community.dynamics.com/blogs/crmisvarchevan/comments/10861.aspxhttp://crm/adventureworkscycle/sdk/list.aspxhttp://www.microsoft.com/learning/en/us/exams/dynamics/422.mspxhttp://blogs.msdn.com/mscrmfreak/http://www.mscrm.com.au/GettingStarted/Training/tabid/64/Default.aspxhttp://www.unitek.com/training/microsoft/crm/index.php?option=com_content&task=view&id=102&Itemid=79
Cheers,
Ravindra
MSBI COMMON URL(*****)
Hi,
Am very impressed on the following link.
Because,it is the only one link to show the way for all BI related blogs.
You can findout lot and lot of blogs on SSIS,SSAS,SSRS,PPS,MOSS,BIZTALK,.Net,MicrosoftOffice.
Am very impressed on the following site, It is a Excellent site:
http://prologika.com/CS/blogs/blog/archive/2007/09/12/mcts-self-paced-training-kit-exam-70-445-microsoft-sql-server-2005-business-intelligence-implementation-and-maintenance.aspx
Am very impressed on the following link.
Because,it is the only one link to show the way for all BI related blogs.
You can findout lot and lot of blogs on SSIS,SSAS,SSRS,PPS,MOSS,BIZTALK,.Net,MicrosoftOffice.
Am very impressed on the following site, It is a Excellent site:
http://prologika.com/CS/blogs/blog/archive/2007/09/12/mcts-self-paced-training-kit-exam-70-445-microsoft-sql-server-2005-business-intelligence-implementation-and-maintenance.aspx
MCTS(70-445) – Complete Syllabus
COMPLETE-SYLLABUS->From-> http://www.microsoft.com/learning/en/us/exams/70-445.mspx
Skills measured by Exam 70-445Managing SSASInstall SSAS.• Install multiple instances of SSAS.• Install a clustered instance of SSAS.• Migrate from SSAS 2000 to SSAS 2005 by using the Migration Wizard.
Configure SSAS.• Configure query logging.• Configure error logging.• Configure disk allocation.• Configure server and database roles and permissions.
Deploy an SSAS solution.• Deploy solutions by using the Deployment Wizard.• Deploy solutions by using XML for Analysis (XMLA) scripts.• Redeploy solutions by using the Synchronize Database Wizard.• Deploy solutions by using BIDS.
Monitor SSAS.• Monitor queries by using SQL Server Profiler.• Monitor SSAS by using the Performance snap-in (PerfMon).
Implement processing for SSAS objects.• Implement processing options. • Implement processing by using SQL Server Management Studio (SSMS), BIDS, and XMLA scripts.
Implement disaster recovery tasks.• Back up and restore the SSAS database files by using SSMS.• Re-create database objects by using XMLA scripts and reprocess.
Developing SSAS Solutions by Using BIDSImplement security on SSAS database objects.• Implement security on cubes and dimensions.• Implement security on cell data.• Use Multidimensional Expressions (MDX) to define the set.• Grant drillthrough permissions.
Create a cube.• Define translations.• Define perspectives.• Create a cube by using the Cube Wizard.• Define default storage settings for new measure groups.• Modify a cube by using Cube Designer.• Define dimension relationships.• Regular• Reference• Fact• Many-to-many
Create dimensions.• Create user-defined and parent-child hierarchies within a dimension.• Modify properties of user-defined hierarchies and attributes.• Create a role-playing dimension by using the Dimension Usage tab in Cube Designer.• Define storage modes.
Create and maintain measure groups and partitions.• Create measure groups.• Add measures to measure groups.• Modify measure properties.• Create partitions.• Define storage mode settings on partitions.• ROLAP, MOLAP, HOLAP• Proactive caching • Design aggregations on partitions.• Design aggregations by using the Aggregation Design Wizard.• Design aggregations by using custom settings.• Design aggregations by using the Usage-Based Optimization Wizard.
Create key performance indicators (KPIs).• Create value and goal expressions.• Create trend indicators and expressions.• Create status indicators and expressions.
Create actions.• Define the action target.• Define the action content.• Includes type of action and action expression.
Create a data source view (DSV).• Add tables from a data source.• Create relationships.• Add logical primary keys to a table in the DSV.• Create named queries.• Create named calculations.
Create queries and calculations by using MDX. • Query multidimensional data by using MDX.• Create calculations.• Create calculated members in the measures dimension.• Create calculated members in other dimensions.• Create named sets.
Implementing Data Mining by Using BIDSCreate a data mining solution.• Create a data mining structure by using the Data Mining Wizard.• Modify a data mining structure by using Data Mining Designer.• Add multiple models to compare algorithms.• Specify which tables and columns are key, input, and predictable.• Specify a column's content.• Specify case and nested tables.
Select a data mining definition method. • Create a definition from an existing relational database. • Create a definition from an existing cube.• Configure algorithm parameters.
Process a data mining object. • Process a data mining structure by using one of the following options: process full, process structure, or process default.• Process a data mining model by using one of the following options: process full or process default.
Query the data mining model by using Data Mining Extensions (DMX).• Create a data mining report in SSRS to access query results.• Create a DMX query by using the Prediction Query Builder.• Create a DMX query by using SSMS.
Test the data mining model by using mining accuracy charts. • Review the classification matrix.• Review the lift chart.
Implement data mining security.• Grant access to the data mining structure.• Grant access to the data mining model.
Managing SSRSManage SSRS reports by using Report Manager.• Create execution properties. • Edit or create report properties.• Examples are history and time-out default. • Create linked reports.• Manage data sources.• Create dynamic credentials by using data source expressions.
Install an SSRS infrastructure.• Install multiple instances of SSRS.• Manage private keys for encryption.
Configure an SSRS infrastructure.• Configure SSRS for Internet deployment.• Enable My Reports to allow users to create linked reports.• Create and configure SSRS instances by using the Reporting Services Configuration tool (Rsconfig.exe).
Deploy SSRS reporting solutions.• Deploy report changes to production by using BIDS.• Deploy Report Builder models. • Upload files to a report server catalog from Report Manager.
Implement SSRS schedules and subscriptions by using Report Manager.• Create shared schedules.• Create report-specific schedules.• Create custom delivery options by using data-driven subscriptions.• Choose a delivery extension and rendering format for subscriptions.
Implement security on SSRS solutions by using Report Manager.• Manage roles.• Assign item-level permissions to a role.• Create and modify system role assignments to manage site-level permissions.• Implement security for My Reports.
Developing Reporting Solutions by Using SSRSCreate reports by using Report Designer.• Set report-level properties.• Page margins, header and footer, and page size.• Create a report by using Report Wizard.• Create report items by using Report Designer.
Create a Report Model for Report Builder.• Import a DSV into a report model project.• Create a DSV from a relational database source by using Model Designer.• Create a model from a multidimensional data source by using Report Manager.
Configure report navigation options.• Define report item, toggle visibility, and enable drilldown on another report item.• Implement drillthrough by creating hyperlink actions, including Jump to URL, Jump to Report, and Jump to Bookmark.• Change report behavior of a report by using different parameters within a URL.
Display datasets in data regions.• Apply filters to data.• Group and sort data.• Nest data regions.• Define aggregates.
Control data by applying parameters.• Assign parameter defaults and data types.• Bind datasets to parameters.• Expose parameters to the user interface.
Apply formatting and style.• Apply dynamic formatting by using expressions.• Apply static formatting.
Extend a report by using expressions.• Invoke an embedded function by using expressions.• Integrate an assembly by using expressions.
Create datasets.• Choose the CommandType property for relational data.• Create a dataset by using multidimensional data.• Implement parameters within a query string.
Developing Business Intelligence Solutions by Using SSISCreate a package.• Use BIDS.• Use the SQL Server Import and Export Wizard.• Use the Package Migration Wizard.
Create data flow by using the data flow designer.• Configure sources and destinations.• Configure transformations.
Create control flow by using the control flow designer.• Sequence tasks by using precedence constraints.• Organize tasks by using containers.• Configure transaction handling for packages, containers, and tasks.• Set checkpoints to define restart points.
Create event handlers.Implement error handling.• Handle errors by configuring data flow paths.• Handle errors by configuring control flow paths.
Debug packages.• Debug progress reporting.• View intermediate results by using debug windows.• Examine the package state by setting breakpoints.
Administering SSIS PackagesImplement security on SSIS packages.• Enforce authenticity by using digital signatures.• Implement the security of package contents by setting the protection level of a package.• Implement the security of package contents by setting the package password.• Configure msdb database roles, such as db_dtsadmin, for controlling access to packages.
Run SSIS packages.• Execute a package by using the dtexec utility or the DTExecUI utility.• Schedule SSIS packages.• Execute a package by using the SSIS service.
Manage SSIS package execution.• Manage packages by using the DTUtil utility.• Define logging options.• Define execution properties.
Deploy SSIS packages.• Deploy packages to files.• Deploy packages to databases.• Create a package deployment utility.
Manage SSIS package configurations.• Configure and view package configurations by using the Package Configurations Organizer dialog box.• Configure a package by using the Package Configuration Wizard.• Connect to different data source types by adding connection managers.• Edit package configuration variables by using the DTExecUI utility.
MODULES->From-> http://www.sandline-training.co.uk/Products/MCTS-70-445-SQLServer2005-Business-Intelligence.php
Module 1 - Course 2791 Course Overview Introduction to SQL Server 2005 Analysis Services Overview of Data Analysis Solutions Overview of SQL Server 2005 Analysis Services Installing SQL Server 2005 Analysis Services Summary Review Quiz Creating Multidimensional Analysis Solutions Developing Analysis Services Solutions Creating a Data Source and a Data Source View Creating a Cube Summary Create a Data Source Create and Modify a Data Source View Create and Modify a Cube Review Quiz Working with Dimensions Configuring Dimensions Defining Hierarchies Sorting and Grouping Attributes Summary Configure Dimensions Define Relationships and Hierarchies Sort and Group Dimension Attributes Review Quiz Working with Measures and Measure Groups Working with Measures Working with Measure Groups Summary Configure Measures Define Dimension Usage and Relationships Configure Measure Group Storage Review Quiz Querying Multidimensional Analysis Solutions MDX Fundamentals Adding Calculations to a Cube Summary Query a Cube by Using MDX Create a Calculated Member Define a Named Set Review Quiz Customizing Cube Functionality Implementing Key Performance Indicators Implementing Actions Implementing Perspectives Implementing Translations Summary Implement a KPI Implement an Action Implement a Perspective Implement a Translation Review Quiz Deploying and Securing an Analysis Services Database Deploying an Analysis Services Database Securing an Analysis Services Database Summary Deploy an Analysis Services Database Secure an Analysis Services Database Review Quiz Maintaining a Multidimensional Solution Configuring Processing Settings Logging, Monitoring, and Optimizing an Analysis Services Solution Backing Up and Restoring an Analysis Services Database Summary Implement Logging and Monitoring Back Up and Restore a Database Review Quiz Introduction to Data Mining Overview of Data Mining Creating a Data Mining Solution Validating Data Mining Models Course Summary Create a Data Mining Structure Add a Data Mining Model Explore Data Mining Models Review Quiz Module 3 - Course 2793 subjects title Course Overview Introduction to SQL Server Reporting Services Overview of SQL Server Reporting Services Installing Reporting Services Reporting Services Tools Summary Explore Report Designer Explore Report Manager Review Quiz Authoring Basic Reports Creating a Basic Table Report Formatting Report Pages Calculating Values Summary Create a Basic Table Report Format Report Pages Add Calculated Values Review Quiz Enhancing Basic Reports Interactive Navigation Displaying Data Summary Use Dynamic Visibility Use Document Maps Initiate Actions Use a List Data Region Review Quiz Manipulating Data Sets Defining Report Data Using Parameters and Filters Using Parameter Lists Summary Restrict Query Results Filter Report Data Review Quiz Using Report Models Creating Report Models Using Report Builder Summary Create a Report Model Use Report Builder Review Quiz Publishing and Executing Reports Publishing Reports Executing Reports Creating Cached Instances Creating Snapshots and Report History Summary Publish Reports Execute a Report On Demand Configure and View a Cached Report Configure and View Snapshot Report Review Quiz Using Subscriptions to Distribute Reports Introduction to Report Subscriptions Creating Report Subscriptions Managing Report Subscriptions Summary Create a Standard Subscription Create a Data-Driven Subscription Review Quiz Administering Reporting Services Server Administration Performance and Reliability Monitoring Administering Report Server Databases Security Administration Summary Use Configuration Manager Secure a Report Services Site Secure Items Review Quiz Programming Reporting Services Querying for Server Information Using a Web Service Automating Report Management Rendering Reports Creating Custom Code Course Summary Use URL Access to Display a Report Build a Web Service Client Use the Report Viewer Control Review Quiz
Module 2 - Course 2792 Course Overview Introduction to SQL Server 2005 Integration Services Overview of Integration Services Solutions Integration Service Tools Summary Use the Import and Export Wizard Run an Integration Services Package Review Quiz Developing Integration Services Solutions Creating an Integration Services Solution Using Variables Building and Running a Solution Summary Create an Integration Services Project Implement a Package Build and Run an Integration Services Project Review Quiz Implementing Control Flow Control Flow Tasks Control Flow Precedence Constraints COntrol Flow Containers Summary Create a Simple Control Flow Configure Precedence Constraints Use Containers Review Quiz Implementing Data Flow Data Flow Sources and Destinations Data Flow Transformations Data Flow Paths Summary Transfer Data Implement Transformations Use Data Viewers Configure Error Output Review Quiz Implementing Logging Overview of Integration Services Logging Implementing Logging Summary Configure Logging Implement Custom Logging Review Quiz Debugging and Error Handling Debugging a Package Implementing Error Handling Summary Debug a Package Review Quiz Implementing Checkpoints and Transactions Implementing Checkpoints Implementing Transactions Summary Implement Checkpoints in a Package Implement Transactions in a Package Implement a Native Transaction Review Quiz Deploying Packages Package Configurations Deploying Packages Summary Create a Package Configuration Prepare a Package for Deployment Deploy a Package Review Quiz Managing and Securing Packages Managing Packages Securing Package Course Summary Import a Package Configure and Execute a Package Schedule a Package Secure a Package Review Quiz
From->The SSRS (SQL Server 2005 Reporting Services) related questions were gathered around the following topics, as I remember from the day I took the examCall customized code (functions) within Layout design. For example Code.FunctionName(...)Show total sums on Group Footer using Sum(Fields!InvoiceAmount.Value)Dataset Command Types ( StoredProcedure, TableDirect, Text)OverwriteDataSources optionCreating Linked ReportReport Server parameters (:rs) and Rendering Device Info parameters (:rc) http://msdn2.microsoft.com/en-us/library/aa256629(SQL.80).aspxRS Utility Tool (rs.exe)URL Addressability and Report Server syntaxNew RS installation by moving existing databases to an other serverGroup visibility toggled by report itemsData-driven subscription And the SSIS (SQL Server 2005 Integration Services) related questions were related withData Flow controls (the only SSIS control asked about was data flow controls)Parameter Setting within Data Flow controlBreakpointsDeploymentBLOGGERS-From ->http://peterkol.spaces.live.com/blog/cns%2168755AEAC31F9A6C%21196.entry70-445 TS: Microsoft SQL Server 2005 Business Intelligence - Implementation and Maintenance General • A lot of arranging tasks in the right order to achieve some goal (like adding a user group with specific rights to SSRS) • Other than that, standard multiple choice questions and some "select all that apply" variants • No cases, only specific questions Topics Remembered (I have goldfish memory) Reporting Services • Formatting report items • Handling reporting services security • Subscriptions (especially data driven ones) • Linked Reports • Drilldown reports both for matrix and table items • Deployment (deploy to a remote server, deployment manifest etc.) • Groupings and subtotals (ie. sum()) • Filters • Setting up parameters in various ways • Moving a RS installation (including encryption keys. ouch) • Report Models for Report Builder (I think I only got one question on Report Builder and that was it) • External code (i got a question on the syntax of calling a function in an assembly)SSIS • DTUtil.exe and its various parameters • Transactions in SSIS (surprisingly many questions on this) • Precedence constraints (nothing fancy, just AND-OR stuff) • Generally handle the flow in the dataflow to achieve some objective • Setting up logging (what events etc.) • Package configurations • Running packages through SQL Server agent (how to set values in configurations etc) • How to configure package checkpoints (a couple of questions on this, pretty detailed ie. what properties to set where etc.) • Debugging (Where / how to set up various viewers)SSAS • Some simple MDX • Some (probably) simple DMX • Fact / Dimension relationships (Many-to-Many, Reference etc.) • Attribute relationships (Defining them, troubleshooting errenous relationships) • MDX definitions of the various components of KPIs (ie set up trend indicator by using PrevPeriod etc) • Various properties of dimension attributes such as HIdeMemberIf etc. • Datasource Views, named quieries and computed columns • Data Security (Cell and dimension) • Security (Setting up various roles, integrating with AD etc.) • Processing options for the UDM • Processing options for Data Mining • Installation issues (clustering, instances) • Parent-Child hierarchies • Logging (Where, what, how) • Generating SSAS metadata for backup and recovery • Storage modes (MOLAP, HOLAP, ROLAP) in various scenarios • Aggregation design • Actions (I got a question on selecting where to define the action) • Creating calculated members (oddly, I only got one question on this) • All kinds of data mining stuff (I need to read up on this, I was pretty blank)Database Engine topics • Mostly locking issues and the avoidance thereof • Partitioning (how to set up)PRO: Designing a Business Intelligence Infrastructure by Using Microsoft SQL Server 2005 Unfortunatly I am quite a bit more scetchy on this one, it was the first one I took today. General • All questions are related to cases (like in the other SQL Server 2005 PRO exams). I feel the cases were a bit more complicated than in the other exams for SQL Server 2005. • No simulations, only multiple choice and "choose all that apply"Topics Remembered General datawarehouse / mart design • Designing various permutations of star-schemas (ie. add a dimension table and relate it to the fact table etc.) • When to use surrogate keys (Apparantly Microsofts answer is to only use surrogate keys in SCD2 scenarios) • Partitioning of load tables • Staging areas and how they can be useful • SCD design in dimension tablesSSAS design • A lot of choose between various ways to retrieve data to the DSV type questions (views, named queries etc.) • Unknown dmiension member handling both when processing and in dimension design • Setting up roles to restrict data access to users in various ways • Lazy Aggregations property • Choose between various storage modes / proactive caching alternatives to achieve some goal • All kinds of datamining topics that i more or less guessed onSSRS design • Various ways to create RDL (BIDS, Report Builder, Custom application) • Choose betwwen various ways to handle drilldown (subreports, hiding groups etc.) • Report snapshots • Choose where to put code (in-line or external assembly) • Choose between various maintenance strategiesSSIS design • Quite a few SCD2 scenarios • Functionality of Merge, Merge Join, Left Join components • Data flow error handling (set up a lookup transformation not fail on no match errors etc.)
QUESTIONS-From-> http://www.killtest.net/MCTS/70-445.asp
1.You are designing a Microsoft SQL Server 2005 Integration Services (SSIS) package. The package has the following features: Two Data Flow tasks and two Control Flow tasks A success constraint between each Data Flow task and the subsequent Control Flow task The package uses one transaction for all the tasks. You need to configure the package so that the two Data Flow tasks use their own transactions. You also need to ensure that each Control Flow task is enlisted in the same transaction as its preceding Data Flow task. Which three actions should you perform? (Each correct answer presents part of the solution. Choose three.)A.Change the TransactionOption property to Supported for the package.B.Change the TransactionOption property to Required for the package.C.Change the TransactionOption property to Supported for each Data Flow task.D.Change the TransactionOption property to Required for each Data Flow task.E.Change the TransactionOption property to Supported for each Control Flow task.F.Change the TransactionOption property to Required for each Control Flow task.Correct:A D E 2.You are designing a Microsoft SQL Server 2005 Integration Services (SSIS) package. The package contains 30 Data Flow tasks and 30 Control Flow tasks. The package runs slower than expected. You need to capture the start time, the finish time, and the elapsed time for the validation and execution of the package. What should you do?A.Enable the OnProgress event handler and use the Control Flow tasks to write the information to a log.B.Monitor the Progress tab during the execution of the package, and then monitor the Execution Results tab.C.Use the Performance Monitor tool to capture the counters from the SQL Server: SSIS Service object. Analyze the output for the required information.D.Use the Performance Monitor tool to capture the counters from the SQL Server: SSIS Pipeline object. Analyze the output for the required information.Correct:B 3.Microsoft SQL Server Management Studio (SSMS) is installed on your workstation. Microsoft Business Intelligence Development Studio (BIDS) is not installed on your workstation. You need to create a package that must meet the following requirements: It must be transactional. It must be optimized for 20 tables. It must be stored securely in the msdb database of a remote server. What should you do?A.Create the package by using DTS Designer.B.Create the package by using the Package Migration Wizard.C.Create the package by using the Microsoft SQL Server Import and Export Wizard.D.On the Microsoft SQL Server 2005 Integration Services (SSIS) menu, click the Create Package submenu.Correct:C 4.You are designing a Microsoft SQL Server 2005 Integration Services (SSIS) package. While testing, a Transact-SQL user-defined function causes duplicate key values that stop the transformation. To permit the transformation to continue, the package must perform the following tasks: 1. Ascertain which rows are affected. 2. Insert the rows that fail into a table. 3. Continue with the process. You need to change certain properties in the package to meet the requirements. Which three tasks should you perform? (Each correct answer presents part of the solution. Choose three.)A.Choose the Redirect Row option for the Error property on the key column.B.Choose the Ignore Failure option for the Error property on the key column.C.Choose the Fail Component option for the Error property on the key column.D.Add a DataReader source to use a new Data Flow destination when the Failure constraint is fired.E.Add a DataReader source to use a new Data Flow destination when the Completion constraint is fired.F.Edit the Error Output properties for each DataReader source and configure each data source for error handling.Correct:A D F 5.You are designing a Microsoft SQL Server 2005 Integration Services (SSIS) package. The package uses at least one sequence container in one transaction. Each transaction controls a Data Flow task and a Control Flow task. Each Data Flow task has a Success constraint. The Control Flow task follows the Success constraint. The package must include the following requirements: Each Data Flow task must use its own transaction. The Control Flow task and the Data Flow task that precedes it must succeed or fail as an atomic unit. A restart point must restart each Data Flow task and the Control Flow task that follows it as an atomic unit. You need to make changes in the control flow designer to meet the outlined requirements. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)A.Add all the Data Flow tasks and the Control Flow tasks to one sequence container.B.Add a sequence container for each Control Flow task and the Data Flow task that precedes it.C.Change the FailPackageonFailure property to true for each new sequence container that is added to the package.D.Change the FailPackageonFailure property to false for each new sequence container that is added to the package.Correct:B C 6.You are designing a Microsoft SQL Server 2005 Integration Services (SSIS) package. An Analysis Services Processing task in the package requires data from a file. The file is exported to a file share. If the file is not available on the file share, the Analysis Services Processing task must import data from a remote Microsoft SQL Server 2005. You need to add steps to the package to import the data. What should you do? (To answer, move the appropriate steps from the list of steps to the answer area and arrange them in the correct order.)Correct:Green choice1---->Yellow Choice1Green choice3---->Yellow Choice2Green choice6---->Yellow Choice37.You are designing a Microsoft SQL Server 2005 Integration Services (SSIS) package. The OLE DB data source for the package is a database that is updated frequently. You need to create a package that accomplishes the following tasks: Implement the IRowsetFastLoad interface. Support various data flow destinations. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)A.Use an OLE DB data flow destination.B.Use a Recordset data flow destination.C.Use a DataReader data flow destination.D.Create separate data flow destinations within the same Data Flow task.Correct:A D 8.Your company's business intelligence (BI) model uses Microsoft SQL Server 2005 Integration Services (SSIS). There are 20 packages that use the same configuration. The package configuration is stored in an XML file as a variable. You need to track the changes that affect a package that uses one variable. You also need to modify the package to provide automatic notification by e-mail whenever there are changes to the variable. What should you do? (To answer, move the appropriate steps from the list of steps to the answer area and arrange them in the correct order.)Correct:Green choice5---->Yellow Choice1Green choice3---->Yellow Choice2Green choice4---->Yellow Choice39.You are designing a Microsoft SQL Server 2005 Integration Services (SSIS) package. The package contains a single OLE DB data source. The package must be deployed on 10 servers. The package uses the XML configuration file to connect to a pre-assigned server. You need to ensure that the package uses the correct server at runtime. What should you do? (To answer, move the appropriate steps from the list of steps to the answer area and arrange them in the correct order.)Correct:Green choice4---->Yellow Choice1Green choice2---->Yellow Choice2Green choice6---->Yellow Choice310.You are designing a Microsoft SQL Server 2005 Integration Services (SSIS) package. The package is named UpdateTable.dtsx and contains a variable named runID. The package must run by using the dtexec utility. The runID variable must be set at runtime to a value of 5. You need to meet the outlined requirements. What should you do?A.Execute dtexec /F "c:\ssisPackages\UpdateTable.dtsx" /SET \packages.variables[runID].Value;5.B.Execute dtexec /F "c:\ssisPackages\UpdateTable.dtsx" /SET \packages.variables.runID=5.C.Create a text file that specifies the variable name and value in the format package.variables[runID].Value = 5. Execute dtexec /F "c:\ssisPackages\UpdateTable.dtsx" /COM where is the name of the file you just created.D.Create a text file that specifies the variable name and value in the format runID=5. Execute dtexec /F "c:\ssisPackages\UpdateTable.dtsx" /COM where is the name of the text file you just created.Correct:A 11.You create 25 Microsoft SQL Server 2005 Integration Services (SSIS) packages on the development server. You decide to deploy the SSIS packages on the test server. You need to create a repeatable process that will allow you to deploy the packages to the package store on the test server if the packages change. You also need to ensure that this is achieved with minimum effort. What should you do?A.Use Microsoft SQL Server Business Intelligence Development Studio (BIDS) to save each package to the remote server.B.Use the dtutil utility in a batch file to deploy each of the package .dtsx files to the remote server with the /Fi option.C.Copy the Microsoft SQL Server Business Intelligence Development Studio (BIDS) files to the remote server and build the SSIS project on the remote server.D.Create a package deployment utility in Microsoft SQL Server Business Intelligence Development Studio (BIDS) and use the Deployment Wizard to deploy all the packages.Correct:D 12.You are designing a Microsoft SQL Server 2005 Integration Services (SSIS) package. You need to ensure that the package came from a trusted source. What should you do?A.Change the CheckSignatureOnLoad property of the package to false.B.Change the CheckSignatureOnLoad property of the package to true.C.Create a certificate. Change the CheckSignatureOnLoad property of the package to false.D.Create a certificate. Change the CheckSignatureOnLoad property of the package to true.Correct:D 13.You are designing a Microsoft SQL Server 2005 Integration Services (SSIS) package. You create a table and populate the table with the required data. You receive an error message "destination table not found" when you run the package without the package destination objects. You need to set a package property that runs the package without giving the "destination table not found" error message when the configuration setting of the Connection Manager fails. Which package property should you set?A.DelayValidation = trueB.MaximumErrorCount = 2C.DisableEventHandler = trueD.FailPackageOnFailure = falseCorrect:A 14.You are designing a Microsoft SQL Server 2005 Integration Services (SSIS) package. The package is connected to a database on your development server by using a connection manager. You decide to use the DTExecUI utility to run the package. You need to change the connection of the connection manager to a different server at run time without using a package configuration file. What should you do?A.Specify an appropriate command file by using the Command Files dialog box.B.Specify an appropriate Connection String variable in the Set Values dialog box.C.Specify an appropriate package configuration by using the Configurations dialog box.D.Specify a different connection string for the connection manager by using the Connection Manager dialog box.Correct:D 15.You are designing a Microsoft SQL Server 2005 Reporting Services (SSRS) accounting report. Every other line of output in the report table must have a green background. You need to use the appropriate expression for the BackgroundColor property of the table. Which expression should you use?A.=Iif((RowNumber("Odd")),"Green","White")B.=Iif((RowNumber(Nothing)),"Green","White")C.=Iif((RowNumber(Nothing) MOD 2),"Green","White")D.=Iif((RowNumber(InScope("Odd"))),"Green","White")Correct:C 16.You are designing a Microsoft SQL Server 2005 Reporting Services (SSRS) report. The report must use a custom assembly to perform real-time lookup and currency conversion. The assembly has a static class named CurrencyConversion that exists in the namespace FinancialCalc. The class has a method named ToEUR that requires two arguments, Currency and CurrencyCode. You need to reference the ToEUR method in an expression to convert USD to EURO when the report runs. Which expression should you use?A.=Code.FinancialCalc.CurrencyConversion.ToEUR (Fields!Currency.Value,"USD")B.=Code!FinancialCalc.CurrencyConversion.ToEUR (Fields!Currency.Value,"USD")C.=FinancialCalc.CurrencyConversion.ToEUR (Fields!Currency.Value,"USD")D.=FinancialCalc!CurrencyConversion.ToEUR (Fields!Currency.Value,"USD")Correct:C 17.You are designing a Microsoft SQL Server 2005 Reporting Services (SSRS) report model. Users must develop their own SSRS reports by using your report model in the Report Builder tool. The data source for the report model is a Microsoft SQL Server 2000 database that contains 900 tables and 700 views. You need to build a report model that allows users access to only the 10 tables that they require for reporting. What should you do?A.Select the Create roles rule when you generate the report model.B.Create a data source view and select the Restrict to Schema(s) option.C.Create a data source view and select only the required tables and views.D.Clear the Create entities for all tables rule when you generate the report model.Correct:C 18.You are designing a Microsoft SQL Server 2005 Reporting Services (SSRS) report. You notice that while printing a report, blank pages are printed after every page that contains the data. But the report appears correctly when you view it on screen. You need to ensure that the blank pages are not printed. Which report property should you use?A.Page SizeB.Grid SpacingC.Interactive SizeD.DataTransformCorrect:A 19.You are designing a Microsoft SQL Server 2005 Reporting Services (SSRS) report. You create a report parameter named DisplayCol that contains the following properties: Data Type: Integer Prompt: Display Column Available Values: Non-queried with the following Label/Value pairs: oYes/1 oNo/0 Default Values: Null The report parameter is used to control the visibility of the column. You need to use the appropriate expression in the Hidden property for the column. Which expression should you use?A."=Parameters!DisplayCol.Value"B."=Parameters!rc:DisplayCol.Value"C."=Iif(Parameters!DisplayCol.Value=1,False,True)"D."=Iif(Parameters!rc:DisplayCol.Value=1,False,True)"Correct:C 20.You are designing a Microsoft SQL Server 2005 Reporting Services (SSRS) report. The report is based on the multidimensional data from a Microsoft SQL Server 2005 Analysis Services (SSAS) cube. You need to use the Report Wizard to create a Matrix report by using the [Change Count] measure in the Details field. Which Multidimensional Expressions (MDX) query should you use?A.SELECT NON EMPTY { [Measures].[Change Count] } ON ROWS, DIMENSION PROPERTIES MEMBER_CAPTION { [Measures]. [Change Count] } ON COLUMNSB.SELECT NON EMPTY { [Measures].[Change Count] } ON COLUMNS, DIMENSION PROPERTIES MEMBER_CAPTION { [Measures]. [Change Count] } ON ROWSC.SELECT DIMENSION PROPERTIES MEMBER_CAPTION { [Measures].[Change Count] } ON ROWSD.SELECT DIMENSION PROPERTIES MEMBER_CAPTION { [Measures].[Change Count] } ON COLUMNSCorrect:B 21.You are designing a Microsoft SQL Server 2005 Reporting Services (SSRS) report that supports drilldown. You need to modify the report so that the detailed data is hidden when the report is initially rendered. What should you do?A.Add the detailed data to a Group. Set the Hidden property of the Group to true.B.Add the detailed data to a Matrix. Set the IsToggleChild property of the Matrix to true.C.Add the detailed data to a Subreport. Set the Hidden property of the Subreport to true.D.Add the detailed data to a Subreport. Set the IsToggleChild property of the Subreport to true.Correct:A 22.You are designing a Microsoft SQL Server 2005 Reporting Services (SSRS) report. The report uses a dataset that is constructed from an SQL table named Employees. The table has an employeeID field and a managerID field. The managerID field in each row represents the employeeID field. You execute the following Transact-SQL query to get the dataset from the report: SELECT CONVERT(varchar(255), c.FirstName + ' ' + c.LastName) as Name, e.EmployeeID, e.ManagerID FROM HumanResources.Employee AS e JOIN Person.Contact AS c ON e.ContactID = c.ContactID You need to display the dataset as a hierarchical, organizational chart, as shown in the Exhibit. (Click the Exhibit button.) What should you do?A.Use the Level function in the Left property of the Padding property of the cell.B.Group the report by the employeeID field and set the managerID field as the group parent.C.Group the report by the managerID field and set the employeeID field as the group parent. Use the Level function in the Left property of the Padding property of the cell.D.Group the report by the employeeID field and set the managerID field as the group parent. Use the Level function in the Left property of the Padding property of the cell.Correct:D 23.You are designing a Microsoft SQL Server 2005 Reporting Services (SSRS) report. The report has three columns in a table. The columns are named MachineName, MachineMfr, and MachineModel. The report navigation options must allow users to click on a row from the MachineName column and drill through to another report, and then pass the contents of the MachineName column as a parameter named MachineName. You need to configure the report navigation options to satisfy the user requirements. Which three tasks should you perform? (Each correct answer presents part of the solution. Choose three.)A.On the General tab, select the data region.B.On the Visibility tab, select the expression for the report item.C.On the Navigation tab, select the report for the Hyperlink action.D.On the Navigation tab, select the bookmark for the Hyperlink action.E.On the Navigation tab, add an expression to the Hyperlink action parameters.F.On the Navigation tab, add the name and the value to the Hyperlink action parameters.Correct:D E F 24.You are designing a Microsoft SQL Server 2005 Reporting Services (SSRS) report. The report contains financial data. An input parameter named TargetMfr is present in the report. When a value in the Machine_Mfr field matches the selected value in the input parameter, the color of the output in a column is red. Otherwise, the color of the output is green. You need to set the TextBox that contains the color property of the Machine_Mfr field to the appropriate expression. Which expression should you use?A.=Iif(Fields!Machine_Mfr = Parameters!rc:TargetMfr.Value,"Red","Green")B.=Iif(Fields!Machine_Mfr.Value = Parameters!TargetMfr.Value,"Red","Green")C.=Iif(InScope(Machine_Mfr),"Red","Green")D.=Iif(InScope(TargetMfr),"Red","Green")Correct:B 25.You are designing a Microsoft SQL Server 2005 Reporting Services (SSRS) report. The query that builds the dataset for the report uses a WHERE clause to filter the rows. The report parameter must perform the following actions: Use the new parameterized query. Run without any user input. Allow users to change the value that is passed to the query by selecting a value from a drop-down list. You need to modify the settings of the Report Properties dialog box to meet the outlined requirements. Which four tasks should you perform? (Each correct answer presents part of the solution. Choose four.)A.Ensure that the Internal check box is selected.B.Ensure that the Default values property value is set to Null.C.Ensure that the Prompt property is set to an appropriate value.D.Ensure that the Data type property is set to an appropriate value.E.Ensure that the Default values property value is set to Non-queried by using an appropriate default value.F.Ensure that the Available values property value is set to Non-queried by using the appropriate labels and values.Correct:C D E F 26.You are developing a Microsoft SQL Server 2005 Analysis Services (SSAS) project. The project contains a cube named Finance. The Finance cube contains the following objects: A measure group named SalesMeasures. A measure named SalesAmount. A time dimension named DimTime. A time dimension hierarchy named Calendar that contains Year on the first level, Quarter on the second level, and Month on the third level. You need to create a trend expression that displays the increase and decrease in the SalesAmount measure for the current DimTime member, irrespective of the level of the Calendar hierarchy. Which expression should you use?A.Case When [SalesMeasures].[SalesAmount]> [SalesMeasures].[SalesAmount],[DimTime].[Calendar].PrevMember Then1 When [SalesMeasures].[SalesAmount]< [SalesMeasures].[SalesAmount],[DimTime].[Calendar].PrevMember Then -1 EndB.Case When [SalesMeasures].[SalesAmount],[DimTime].[Calendar]< [SalesMeasures].[SalesAmount],[DimTime].[Calendar].PrevMember Then1 When [SalesMeasures].[SalesAmount],[DimTime].[Calendar]> [SalesMeasures].[SalesAmount],[DimTime].[Calendar].PrevMember Then -1 EndC.Case When [SalesMeasures].[SalesAmount]> [SalesMeasures].[SalesAmount],[DimTime].[Calendar].NextMember Then1 When [SalesMeasures].[SalesAmount]< [SalesMeasures].[SalesAmount],[DimTime].[Calendar].NextMember Then -1 EndD.Case When [SalesMeasures].[SalesAmount]< [SalesMeasures].[SalesAmount],[DimTime].[Calendar].NextMember Then1 When [SalesMeasures].[SalesAmount]> [SalesMeasures].[SalesAmount],[DimTime].[Calendar].NextMember Then -1 EndCorrect:A 27.You are developing a Microsoft SQL Server 2005 Analysis Services (SSAS) project. You create a cube that contains the following objects: A time dimension named Time that has an attribute named TimeKey. A fact table named FactOrders. The Time dimension contains different hierarchies. Two of the columns of the fact table link to the attribute. You need to view the measures for different hierarchies for both the columns in the fact table. What should you do?A.Create a single dimension usage relationship between the Time dimension and the FactOrders fact table by defining a regular relationship.B.Create a single dimension usage relationship between the Time dimension and the FactOrders fact table by defining a many-to-many relationship.C.Create two dimension usage relationships between the Time dimension and the FactOrders fact table by defining a regular relationship for both the dimension usages.D.Create two dimension usage relationships between the Time dimension and the FactOrders fact table by defining a many-to-many relationship for both the dimension usages.Correct:C 28.You are developing a Microsoft SQL Server 2005 Analysis Services (SSAS) project. The data source for a cube has tables as shown in the following exhibit. (Click the Exhibit button.) The cube contains the following objects: A dimension named DimAccounts that uses a table named Accounts. A dimension named DimCustomers that uses a table named Customers. A measure group named Transactions that uses a fact table named Transactions. A measure group named Customer Accounts that uses a fact table named CustomerAccounts. You need to create a cube dimension to browse through the measures in the Transactions fact table by using the DimCustomers dimension. What should you do?A.Create a referenced relationship and set the intermediate dimension as DimAccounts.B.Create a referenced relationship and set the intermediate dimension as DimCustomers.C.Create a many-to-many relationship and set the intermediate measure group to Transactions.D.Create a many-to-many relationship and set the intermediate measure group to Customer Accounts.Correct:D 29.You are developing a Microsoft SQL Server 2005 Analysis Services (SSAS) project. You create a cube named ExpenseData that uses a dimension named Expense. The Expense dimension is a parent-child dimension with: a key attribute named Expense. A parent attribute named Expenses of type Expense. The project contains a role named Users. You need to modify the Users role to achieve the following tasks: Restrict role members from viewing the cell values of Expenses under the expense Managerial Expenses. Ensure that all the labels for the Expense dimension are visible. What should you do?A.Change the access to Read/Write for dimension Expense for the ExpensesData cube.B.On the Cell Data tab, select the Enable read permissions check box. Enter the [Expense].[Expenses].Parent.Name<>"Managerial Expenses" expression.C.On the Cell Data tab, select the Enable read permissions check box. Enter the [Expense].[Expenses].Parent.Name="Managerial Expenses" expression.D.On the Dimension Data tab, select the dimension Expense in the ExpensesData cube in the Dimension drop-down box, and then cancel the selection of all the members under the member Managerial Expenses.Correct:B 30.You are developing a Microsoft SQL Server 2005 Analysis Services (SSAS) project. You create a dimension that contains a parent-child hierarchy. The dimension has a key attribute named Employee and a parent attribute named Employees. You need to ensure that the dimension identifies the topmost employee member of the hierarchy as the root member. What should you do?A.Set the IsAggregatable property of the Employee attribute to true.B.Set the IsAggregatable property of the Employee attribute to false.C.Set the IsAggregatable property of the Employees attribute to true.D.Set the IsAggregatable property of the Employees attribute to false.Correct:D
Skills measured by Exam 70-445Managing SSASInstall SSAS.• Install multiple instances of SSAS.• Install a clustered instance of SSAS.• Migrate from SSAS 2000 to SSAS 2005 by using the Migration Wizard.
Configure SSAS.• Configure query logging.• Configure error logging.• Configure disk allocation.• Configure server and database roles and permissions.
Deploy an SSAS solution.• Deploy solutions by using the Deployment Wizard.• Deploy solutions by using XML for Analysis (XMLA) scripts.• Redeploy solutions by using the Synchronize Database Wizard.• Deploy solutions by using BIDS.
Monitor SSAS.• Monitor queries by using SQL Server Profiler.• Monitor SSAS by using the Performance snap-in (PerfMon).
Implement processing for SSAS objects.• Implement processing options. • Implement processing by using SQL Server Management Studio (SSMS), BIDS, and XMLA scripts.
Implement disaster recovery tasks.• Back up and restore the SSAS database files by using SSMS.• Re-create database objects by using XMLA scripts and reprocess.
Developing SSAS Solutions by Using BIDSImplement security on SSAS database objects.• Implement security on cubes and dimensions.• Implement security on cell data.• Use Multidimensional Expressions (MDX) to define the set.• Grant drillthrough permissions.
Create a cube.• Define translations.• Define perspectives.• Create a cube by using the Cube Wizard.• Define default storage settings for new measure groups.• Modify a cube by using Cube Designer.• Define dimension relationships.• Regular• Reference• Fact• Many-to-many
Create dimensions.• Create user-defined and parent-child hierarchies within a dimension.• Modify properties of user-defined hierarchies and attributes.• Create a role-playing dimension by using the Dimension Usage tab in Cube Designer.• Define storage modes.
Create and maintain measure groups and partitions.• Create measure groups.• Add measures to measure groups.• Modify measure properties.• Create partitions.• Define storage mode settings on partitions.• ROLAP, MOLAP, HOLAP• Proactive caching • Design aggregations on partitions.• Design aggregations by using the Aggregation Design Wizard.• Design aggregations by using custom settings.• Design aggregations by using the Usage-Based Optimization Wizard.
Create key performance indicators (KPIs).• Create value and goal expressions.• Create trend indicators and expressions.• Create status indicators and expressions.
Create actions.• Define the action target.• Define the action content.• Includes type of action and action expression.
Create a data source view (DSV).• Add tables from a data source.• Create relationships.• Add logical primary keys to a table in the DSV.• Create named queries.• Create named calculations.
Create queries and calculations by using MDX. • Query multidimensional data by using MDX.• Create calculations.• Create calculated members in the measures dimension.• Create calculated members in other dimensions.• Create named sets.
Implementing Data Mining by Using BIDSCreate a data mining solution.• Create a data mining structure by using the Data Mining Wizard.• Modify a data mining structure by using Data Mining Designer.• Add multiple models to compare algorithms.• Specify which tables and columns are key, input, and predictable.• Specify a column's content.• Specify case and nested tables.
Select a data mining definition method. • Create a definition from an existing relational database. • Create a definition from an existing cube.• Configure algorithm parameters.
Process a data mining object. • Process a data mining structure by using one of the following options: process full, process structure, or process default.• Process a data mining model by using one of the following options: process full or process default.
Query the data mining model by using Data Mining Extensions (DMX).• Create a data mining report in SSRS to access query results.• Create a DMX query by using the Prediction Query Builder.• Create a DMX query by using SSMS.
Test the data mining model by using mining accuracy charts. • Review the classification matrix.• Review the lift chart.
Implement data mining security.• Grant access to the data mining structure.• Grant access to the data mining model.
Managing SSRSManage SSRS reports by using Report Manager.• Create execution properties. • Edit or create report properties.• Examples are history and time-out default. • Create linked reports.• Manage data sources.• Create dynamic credentials by using data source expressions.
Install an SSRS infrastructure.• Install multiple instances of SSRS.• Manage private keys for encryption.
Configure an SSRS infrastructure.• Configure SSRS for Internet deployment.• Enable My Reports to allow users to create linked reports.• Create and configure SSRS instances by using the Reporting Services Configuration tool (Rsconfig.exe).
Deploy SSRS reporting solutions.• Deploy report changes to production by using BIDS.• Deploy Report Builder models. • Upload files to a report server catalog from Report Manager.
Implement SSRS schedules and subscriptions by using Report Manager.• Create shared schedules.• Create report-specific schedules.• Create custom delivery options by using data-driven subscriptions.• Choose a delivery extension and rendering format for subscriptions.
Implement security on SSRS solutions by using Report Manager.• Manage roles.• Assign item-level permissions to a role.• Create and modify system role assignments to manage site-level permissions.• Implement security for My Reports.
Developing Reporting Solutions by Using SSRSCreate reports by using Report Designer.• Set report-level properties.• Page margins, header and footer, and page size.• Create a report by using Report Wizard.• Create report items by using Report Designer.
Create a Report Model for Report Builder.• Import a DSV into a report model project.• Create a DSV from a relational database source by using Model Designer.• Create a model from a multidimensional data source by using Report Manager.
Configure report navigation options.• Define report item, toggle visibility, and enable drilldown on another report item.• Implement drillthrough by creating hyperlink actions, including Jump to URL, Jump to Report, and Jump to Bookmark.• Change report behavior of a report by using different parameters within a URL.
Display datasets in data regions.• Apply filters to data.• Group and sort data.• Nest data regions.• Define aggregates.
Control data by applying parameters.• Assign parameter defaults and data types.• Bind datasets to parameters.• Expose parameters to the user interface.
Apply formatting and style.• Apply dynamic formatting by using expressions.• Apply static formatting.
Extend a report by using expressions.• Invoke an embedded function by using expressions.• Integrate an assembly by using expressions.
Create datasets.• Choose the CommandType property for relational data.• Create a dataset by using multidimensional data.• Implement parameters within a query string.
Developing Business Intelligence Solutions by Using SSISCreate a package.• Use BIDS.• Use the SQL Server Import and Export Wizard.• Use the Package Migration Wizard.
Create data flow by using the data flow designer.• Configure sources and destinations.• Configure transformations.
Create control flow by using the control flow designer.• Sequence tasks by using precedence constraints.• Organize tasks by using containers.• Configure transaction handling for packages, containers, and tasks.• Set checkpoints to define restart points.
Create event handlers.Implement error handling.• Handle errors by configuring data flow paths.• Handle errors by configuring control flow paths.
Debug packages.• Debug progress reporting.• View intermediate results by using debug windows.• Examine the package state by setting breakpoints.
Administering SSIS PackagesImplement security on SSIS packages.• Enforce authenticity by using digital signatures.• Implement the security of package contents by setting the protection level of a package.• Implement the security of package contents by setting the package password.• Configure msdb database roles, such as db_dtsadmin, for controlling access to packages.
Run SSIS packages.• Execute a package by using the dtexec utility or the DTExecUI utility.• Schedule SSIS packages.• Execute a package by using the SSIS service.
Manage SSIS package execution.• Manage packages by using the DTUtil utility.• Define logging options.• Define execution properties.
Deploy SSIS packages.• Deploy packages to files.• Deploy packages to databases.• Create a package deployment utility.
Manage SSIS package configurations.• Configure and view package configurations by using the Package Configurations Organizer dialog box.• Configure a package by using the Package Configuration Wizard.• Connect to different data source types by adding connection managers.• Edit package configuration variables by using the DTExecUI utility.
MODULES->From-> http://www.sandline-training.co.uk/Products/MCTS-70-445-SQLServer2005-Business-Intelligence.php
Module 1 - Course 2791 Course Overview Introduction to SQL Server 2005 Analysis Services Overview of Data Analysis Solutions Overview of SQL Server 2005 Analysis Services Installing SQL Server 2005 Analysis Services Summary Review Quiz Creating Multidimensional Analysis Solutions Developing Analysis Services Solutions Creating a Data Source and a Data Source View Creating a Cube Summary Create a Data Source Create and Modify a Data Source View Create and Modify a Cube Review Quiz Working with Dimensions Configuring Dimensions Defining Hierarchies Sorting and Grouping Attributes Summary Configure Dimensions Define Relationships and Hierarchies Sort and Group Dimension Attributes Review Quiz Working with Measures and Measure Groups Working with Measures Working with Measure Groups Summary Configure Measures Define Dimension Usage and Relationships Configure Measure Group Storage Review Quiz Querying Multidimensional Analysis Solutions MDX Fundamentals Adding Calculations to a Cube Summary Query a Cube by Using MDX Create a Calculated Member Define a Named Set Review Quiz Customizing Cube Functionality Implementing Key Performance Indicators Implementing Actions Implementing Perspectives Implementing Translations Summary Implement a KPI Implement an Action Implement a Perspective Implement a Translation Review Quiz Deploying and Securing an Analysis Services Database Deploying an Analysis Services Database Securing an Analysis Services Database Summary Deploy an Analysis Services Database Secure an Analysis Services Database Review Quiz Maintaining a Multidimensional Solution Configuring Processing Settings Logging, Monitoring, and Optimizing an Analysis Services Solution Backing Up and Restoring an Analysis Services Database Summary Implement Logging and Monitoring Back Up and Restore a Database Review Quiz Introduction to Data Mining Overview of Data Mining Creating a Data Mining Solution Validating Data Mining Models Course Summary Create a Data Mining Structure Add a Data Mining Model Explore Data Mining Models Review Quiz Module 3 - Course 2793 subjects title Course Overview Introduction to SQL Server Reporting Services Overview of SQL Server Reporting Services Installing Reporting Services Reporting Services Tools Summary Explore Report Designer Explore Report Manager Review Quiz Authoring Basic Reports Creating a Basic Table Report Formatting Report Pages Calculating Values Summary Create a Basic Table Report Format Report Pages Add Calculated Values Review Quiz Enhancing Basic Reports Interactive Navigation Displaying Data Summary Use Dynamic Visibility Use Document Maps Initiate Actions Use a List Data Region Review Quiz Manipulating Data Sets Defining Report Data Using Parameters and Filters Using Parameter Lists Summary Restrict Query Results Filter Report Data Review Quiz Using Report Models Creating Report Models Using Report Builder Summary Create a Report Model Use Report Builder Review Quiz Publishing and Executing Reports Publishing Reports Executing Reports Creating Cached Instances Creating Snapshots and Report History Summary Publish Reports Execute a Report On Demand Configure and View a Cached Report Configure and View Snapshot Report Review Quiz Using Subscriptions to Distribute Reports Introduction to Report Subscriptions Creating Report Subscriptions Managing Report Subscriptions Summary Create a Standard Subscription Create a Data-Driven Subscription Review Quiz Administering Reporting Services Server Administration Performance and Reliability Monitoring Administering Report Server Databases Security Administration Summary Use Configuration Manager Secure a Report Services Site Secure Items Review Quiz Programming Reporting Services Querying for Server Information Using a Web Service Automating Report Management Rendering Reports Creating Custom Code Course Summary Use URL Access to Display a Report Build a Web Service Client Use the Report Viewer Control Review Quiz
Module 2 - Course 2792 Course Overview Introduction to SQL Server 2005 Integration Services Overview of Integration Services Solutions Integration Service Tools Summary Use the Import and Export Wizard Run an Integration Services Package Review Quiz Developing Integration Services Solutions Creating an Integration Services Solution Using Variables Building and Running a Solution Summary Create an Integration Services Project Implement a Package Build and Run an Integration Services Project Review Quiz Implementing Control Flow Control Flow Tasks Control Flow Precedence Constraints COntrol Flow Containers Summary Create a Simple Control Flow Configure Precedence Constraints Use Containers Review Quiz Implementing Data Flow Data Flow Sources and Destinations Data Flow Transformations Data Flow Paths Summary Transfer Data Implement Transformations Use Data Viewers Configure Error Output Review Quiz Implementing Logging Overview of Integration Services Logging Implementing Logging Summary Configure Logging Implement Custom Logging Review Quiz Debugging and Error Handling Debugging a Package Implementing Error Handling Summary Debug a Package Review Quiz Implementing Checkpoints and Transactions Implementing Checkpoints Implementing Transactions Summary Implement Checkpoints in a Package Implement Transactions in a Package Implement a Native Transaction Review Quiz Deploying Packages Package Configurations Deploying Packages Summary Create a Package Configuration Prepare a Package for Deployment Deploy a Package Review Quiz Managing and Securing Packages Managing Packages Securing Package Course Summary Import a Package Configure and Execute a Package Schedule a Package Secure a Package Review Quiz
From->The SSRS (SQL Server 2005 Reporting Services) related questions were gathered around the following topics, as I remember from the day I took the examCall customized code (functions) within Layout design. For example Code.FunctionName(...)Show total sums on Group Footer using Sum(Fields!InvoiceAmount.Value)Dataset Command Types ( StoredProcedure, TableDirect, Text)OverwriteDataSources optionCreating Linked ReportReport Server parameters (:rs) and Rendering Device Info parameters (:rc) http://msdn2.microsoft.com/en-us/library/aa256629(SQL.80).aspxRS Utility Tool (rs.exe)URL Addressability and Report Server syntaxNew RS installation by moving existing databases to an other serverGroup visibility toggled by report itemsData-driven subscription And the SSIS (SQL Server 2005 Integration Services) related questions were related withData Flow controls (the only SSIS control asked about was data flow controls)Parameter Setting within Data Flow controlBreakpointsDeploymentBLOGGERS-From ->http://peterkol.spaces.live.com/blog/cns%2168755AEAC31F9A6C%21196.entry70-445 TS: Microsoft SQL Server 2005 Business Intelligence - Implementation and Maintenance General • A lot of arranging tasks in the right order to achieve some goal (like adding a user group with specific rights to SSRS) • Other than that, standard multiple choice questions and some "select all that apply" variants • No cases, only specific questions Topics Remembered (I have goldfish memory) Reporting Services • Formatting report items • Handling reporting services security • Subscriptions (especially data driven ones) • Linked Reports • Drilldown reports both for matrix and table items • Deployment (deploy to a remote server, deployment manifest etc.) • Groupings and subtotals (ie. sum()) • Filters • Setting up parameters in various ways • Moving a RS installation (including encryption keys. ouch) • Report Models for Report Builder (I think I only got one question on Report Builder and that was it) • External code (i got a question on the syntax of calling a function in an assembly)SSIS • DTUtil.exe and its various parameters • Transactions in SSIS (surprisingly many questions on this) • Precedence constraints (nothing fancy, just AND-OR stuff) • Generally handle the flow in the dataflow to achieve some objective • Setting up logging (what events etc.) • Package configurations • Running packages through SQL Server agent (how to set values in configurations etc) • How to configure package checkpoints (a couple of questions on this, pretty detailed ie. what properties to set where etc.) • Debugging (Where / how to set up various viewers)SSAS • Some simple MDX • Some (probably) simple DMX • Fact / Dimension relationships (Many-to-Many, Reference etc.) • Attribute relationships (Defining them, troubleshooting errenous relationships) • MDX definitions of the various components of KPIs (ie set up trend indicator by using PrevPeriod etc) • Various properties of dimension attributes such as HIdeMemberIf etc. • Datasource Views, named quieries and computed columns • Data Security (Cell and dimension) • Security (Setting up various roles, integrating with AD etc.) • Processing options for the UDM • Processing options for Data Mining • Installation issues (clustering, instances) • Parent-Child hierarchies • Logging (Where, what, how) • Generating SSAS metadata for backup and recovery • Storage modes (MOLAP, HOLAP, ROLAP) in various scenarios • Aggregation design • Actions (I got a question on selecting where to define the action) • Creating calculated members (oddly, I only got one question on this) • All kinds of data mining stuff (I need to read up on this, I was pretty blank)Database Engine topics • Mostly locking issues and the avoidance thereof • Partitioning (how to set up)PRO: Designing a Business Intelligence Infrastructure by Using Microsoft SQL Server 2005 Unfortunatly I am quite a bit more scetchy on this one, it was the first one I took today. General • All questions are related to cases (like in the other SQL Server 2005 PRO exams). I feel the cases were a bit more complicated than in the other exams for SQL Server 2005. • No simulations, only multiple choice and "choose all that apply"Topics Remembered General datawarehouse / mart design • Designing various permutations of star-schemas (ie. add a dimension table and relate it to the fact table etc.) • When to use surrogate keys (Apparantly Microsofts answer is to only use surrogate keys in SCD2 scenarios) • Partitioning of load tables • Staging areas and how they can be useful • SCD design in dimension tablesSSAS design • A lot of choose between various ways to retrieve data to the DSV type questions (views, named queries etc.) • Unknown dmiension member handling both when processing and in dimension design • Setting up roles to restrict data access to users in various ways • Lazy Aggregations property • Choose between various storage modes / proactive caching alternatives to achieve some goal • All kinds of datamining topics that i more or less guessed onSSRS design • Various ways to create RDL (BIDS, Report Builder, Custom application) • Choose betwwen various ways to handle drilldown (subreports, hiding groups etc.) • Report snapshots • Choose where to put code (in-line or external assembly) • Choose between various maintenance strategiesSSIS design • Quite a few SCD2 scenarios • Functionality of Merge, Merge Join, Left Join components • Data flow error handling (set up a lookup transformation not fail on no match errors etc.)
QUESTIONS-From-> http://www.killtest.net/MCTS/70-445.asp
1.You are designing a Microsoft SQL Server 2005 Integration Services (SSIS) package. The package has the following features: Two Data Flow tasks and two Control Flow tasks A success constraint between each Data Flow task and the subsequent Control Flow task The package uses one transaction for all the tasks. You need to configure the package so that the two Data Flow tasks use their own transactions. You also need to ensure that each Control Flow task is enlisted in the same transaction as its preceding Data Flow task. Which three actions should you perform? (Each correct answer presents part of the solution. Choose three.)A.Change the TransactionOption property to Supported for the package.B.Change the TransactionOption property to Required for the package.C.Change the TransactionOption property to Supported for each Data Flow task.D.Change the TransactionOption property to Required for each Data Flow task.E.Change the TransactionOption property to Supported for each Control Flow task.F.Change the TransactionOption property to Required for each Control Flow task.Correct:A D E 2.You are designing a Microsoft SQL Server 2005 Integration Services (SSIS) package. The package contains 30 Data Flow tasks and 30 Control Flow tasks. The package runs slower than expected. You need to capture the start time, the finish time, and the elapsed time for the validation and execution of the package. What should you do?A.Enable the OnProgress event handler and use the Control Flow tasks to write the information to a log.B.Monitor the Progress tab during the execution of the package, and then monitor the Execution Results tab.C.Use the Performance Monitor tool to capture the counters from the SQL Server: SSIS Service object. Analyze the output for the required information.D.Use the Performance Monitor tool to capture the counters from the SQL Server: SSIS Pipeline object. Analyze the output for the required information.Correct:B 3.Microsoft SQL Server Management Studio (SSMS) is installed on your workstation. Microsoft Business Intelligence Development Studio (BIDS) is not installed on your workstation. You need to create a package that must meet the following requirements: It must be transactional. It must be optimized for 20 tables. It must be stored securely in the msdb database of a remote server. What should you do?A.Create the package by using DTS Designer.B.Create the package by using the Package Migration Wizard.C.Create the package by using the Microsoft SQL Server Import and Export Wizard.D.On the Microsoft SQL Server 2005 Integration Services (SSIS) menu, click the Create Package submenu.Correct:C 4.You are designing a Microsoft SQL Server 2005 Integration Services (SSIS) package. While testing, a Transact-SQL user-defined function causes duplicate key values that stop the transformation. To permit the transformation to continue, the package must perform the following tasks: 1. Ascertain which rows are affected. 2. Insert the rows that fail into a table. 3. Continue with the process. You need to change certain properties in the package to meet the requirements. Which three tasks should you perform? (Each correct answer presents part of the solution. Choose three.)A.Choose the Redirect Row option for the Error property on the key column.B.Choose the Ignore Failure option for the Error property on the key column.C.Choose the Fail Component option for the Error property on the key column.D.Add a DataReader source to use a new Data Flow destination when the Failure constraint is fired.E.Add a DataReader source to use a new Data Flow destination when the Completion constraint is fired.F.Edit the Error Output properties for each DataReader source and configure each data source for error handling.Correct:A D F 5.You are designing a Microsoft SQL Server 2005 Integration Services (SSIS) package. The package uses at least one sequence container in one transaction. Each transaction controls a Data Flow task and a Control Flow task. Each Data Flow task has a Success constraint. The Control Flow task follows the Success constraint. The package must include the following requirements: Each Data Flow task must use its own transaction. The Control Flow task and the Data Flow task that precedes it must succeed or fail as an atomic unit. A restart point must restart each Data Flow task and the Control Flow task that follows it as an atomic unit. You need to make changes in the control flow designer to meet the outlined requirements. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)A.Add all the Data Flow tasks and the Control Flow tasks to one sequence container.B.Add a sequence container for each Control Flow task and the Data Flow task that precedes it.C.Change the FailPackageonFailure property to true for each new sequence container that is added to the package.D.Change the FailPackageonFailure property to false for each new sequence container that is added to the package.Correct:B C 6.You are designing a Microsoft SQL Server 2005 Integration Services (SSIS) package. An Analysis Services Processing task in the package requires data from a file. The file is exported to a file share. If the file is not available on the file share, the Analysis Services Processing task must import data from a remote Microsoft SQL Server 2005. You need to add steps to the package to import the data. What should you do? (To answer, move the appropriate steps from the list of steps to the answer area and arrange them in the correct order.)Correct:Green choice1---->Yellow Choice1Green choice3---->Yellow Choice2Green choice6---->Yellow Choice37.You are designing a Microsoft SQL Server 2005 Integration Services (SSIS) package. The OLE DB data source for the package is a database that is updated frequently. You need to create a package that accomplishes the following tasks: Implement the IRowsetFastLoad interface. Support various data flow destinations. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)A.Use an OLE DB data flow destination.B.Use a Recordset data flow destination.C.Use a DataReader data flow destination.D.Create separate data flow destinations within the same Data Flow task.Correct:A D 8.Your company's business intelligence (BI) model uses Microsoft SQL Server 2005 Integration Services (SSIS). There are 20 packages that use the same configuration. The package configuration is stored in an XML file as a variable. You need to track the changes that affect a package that uses one variable. You also need to modify the package to provide automatic notification by e-mail whenever there are changes to the variable. What should you do? (To answer, move the appropriate steps from the list of steps to the answer area and arrange them in the correct order.)Correct:Green choice5---->Yellow Choice1Green choice3---->Yellow Choice2Green choice4---->Yellow Choice39.You are designing a Microsoft SQL Server 2005 Integration Services (SSIS) package. The package contains a single OLE DB data source. The package must be deployed on 10 servers. The package uses the XML configuration file to connect to a pre-assigned server. You need to ensure that the package uses the correct server at runtime. What should you do? (To answer, move the appropriate steps from the list of steps to the answer area and arrange them in the correct order.)Correct:Green choice4---->Yellow Choice1Green choice2---->Yellow Choice2Green choice6---->Yellow Choice310.You are designing a Microsoft SQL Server 2005 Integration Services (SSIS) package. The package is named UpdateTable.dtsx and contains a variable named runID. The package must run by using the dtexec utility. The runID variable must be set at runtime to a value of 5. You need to meet the outlined requirements. What should you do?A.Execute dtexec /F "c:\ssisPackages\UpdateTable.dtsx" /SET \packages.variables[runID].Value;5.B.Execute dtexec /F "c:\ssisPackages\UpdateTable.dtsx" /SET \packages.variables.runID=5.C.Create a text file that specifies the variable name and value in the format package.variables[runID].Value = 5. Execute dtexec /F "c:\ssisPackages\UpdateTable.dtsx" /COM where is the name of the file you just created.D.Create a text file that specifies the variable name and value in the format runID=5. Execute dtexec /F "c:\ssisPackages\UpdateTable.dtsx" /COM where is the name of the text file you just created.Correct:A 11.You create 25 Microsoft SQL Server 2005 Integration Services (SSIS) packages on the development server. You decide to deploy the SSIS packages on the test server. You need to create a repeatable process that will allow you to deploy the packages to the package store on the test server if the packages change. You also need to ensure that this is achieved with minimum effort. What should you do?A.Use Microsoft SQL Server Business Intelligence Development Studio (BIDS) to save each package to the remote server.B.Use the dtutil utility in a batch file to deploy each of the package .dtsx files to the remote server with the /Fi option.C.Copy the Microsoft SQL Server Business Intelligence Development Studio (BIDS) files to the remote server and build the SSIS project on the remote server.D.Create a package deployment utility in Microsoft SQL Server Business Intelligence Development Studio (BIDS) and use the Deployment Wizard to deploy all the packages.Correct:D 12.You are designing a Microsoft SQL Server 2005 Integration Services (SSIS) package. You need to ensure that the package came from a trusted source. What should you do?A.Change the CheckSignatureOnLoad property of the package to false.B.Change the CheckSignatureOnLoad property of the package to true.C.Create a certificate. Change the CheckSignatureOnLoad property of the package to false.D.Create a certificate. Change the CheckSignatureOnLoad property of the package to true.Correct:D 13.You are designing a Microsoft SQL Server 2005 Integration Services (SSIS) package. You create a table and populate the table with the required data. You receive an error message "destination table not found" when you run the package without the package destination objects. You need to set a package property that runs the package without giving the "destination table not found" error message when the configuration setting of the Connection Manager fails. Which package property should you set?A.DelayValidation = trueB.MaximumErrorCount = 2C.DisableEventHandler = trueD.FailPackageOnFailure = falseCorrect:A 14.You are designing a Microsoft SQL Server 2005 Integration Services (SSIS) package. The package is connected to a database on your development server by using a connection manager. You decide to use the DTExecUI utility to run the package. You need to change the connection of the connection manager to a different server at run time without using a package configuration file. What should you do?A.Specify an appropriate command file by using the Command Files dialog box.B.Specify an appropriate Connection String variable in the Set Values dialog box.C.Specify an appropriate package configuration by using the Configurations dialog box.D.Specify a different connection string for the connection manager by using the Connection Manager dialog box.Correct:D 15.You are designing a Microsoft SQL Server 2005 Reporting Services (SSRS) accounting report. Every other line of output in the report table must have a green background. You need to use the appropriate expression for the BackgroundColor property of the table. Which expression should you use?A.=Iif((RowNumber("Odd")),"Green","White")B.=Iif((RowNumber(Nothing)),"Green","White")C.=Iif((RowNumber(Nothing) MOD 2),"Green","White")D.=Iif((RowNumber(InScope("Odd"))),"Green","White")Correct:C 16.You are designing a Microsoft SQL Server 2005 Reporting Services (SSRS) report. The report must use a custom assembly to perform real-time lookup and currency conversion. The assembly has a static class named CurrencyConversion that exists in the namespace FinancialCalc. The class has a method named ToEUR that requires two arguments, Currency and CurrencyCode. You need to reference the ToEUR method in an expression to convert USD to EURO when the report runs. Which expression should you use?A.=Code.FinancialCalc.CurrencyConversion.ToEUR (Fields!Currency.Value,"USD")B.=Code!FinancialCalc.CurrencyConversion.ToEUR (Fields!Currency.Value,"USD")C.=FinancialCalc.CurrencyConversion.ToEUR (Fields!Currency.Value,"USD")D.=FinancialCalc!CurrencyConversion.ToEUR (Fields!Currency.Value,"USD")Correct:C 17.You are designing a Microsoft SQL Server 2005 Reporting Services (SSRS) report model. Users must develop their own SSRS reports by using your report model in the Report Builder tool. The data source for the report model is a Microsoft SQL Server 2000 database that contains 900 tables and 700 views. You need to build a report model that allows users access to only the 10 tables that they require for reporting. What should you do?A.Select the Create roles rule when you generate the report model.B.Create a data source view and select the Restrict to Schema(s) option.C.Create a data source view and select only the required tables and views.D.Clear the Create entities for all tables rule when you generate the report model.Correct:C 18.You are designing a Microsoft SQL Server 2005 Reporting Services (SSRS) report. You notice that while printing a report, blank pages are printed after every page that contains the data. But the report appears correctly when you view it on screen. You need to ensure that the blank pages are not printed. Which report property should you use?A.Page SizeB.Grid SpacingC.Interactive SizeD.DataTransformCorrect:A 19.You are designing a Microsoft SQL Server 2005 Reporting Services (SSRS) report. You create a report parameter named DisplayCol that contains the following properties: Data Type: Integer Prompt: Display Column Available Values: Non-queried with the following Label/Value pairs: oYes/1 oNo/0 Default Values: Null The report parameter is used to control the visibility of the column. You need to use the appropriate expression in the Hidden property for the column. Which expression should you use?A."=Parameters!DisplayCol.Value"B."=Parameters!rc:DisplayCol.Value"C."=Iif(Parameters!DisplayCol.Value=1,False,True)"D."=Iif(Parameters!rc:DisplayCol.Value=1,False,True)"Correct:C 20.You are designing a Microsoft SQL Server 2005 Reporting Services (SSRS) report. The report is based on the multidimensional data from a Microsoft SQL Server 2005 Analysis Services (SSAS) cube. You need to use the Report Wizard to create a Matrix report by using the [Change Count] measure in the Details field. Which Multidimensional Expressions (MDX) query should you use?A.SELECT NON EMPTY { [Measures].[Change Count] } ON ROWS, DIMENSION PROPERTIES MEMBER_CAPTION { [Measures]. [Change Count] } ON COLUMNSB.SELECT NON EMPTY { [Measures].[Change Count] } ON COLUMNS, DIMENSION PROPERTIES MEMBER_CAPTION { [Measures]. [Change Count] } ON ROWSC.SELECT DIMENSION PROPERTIES MEMBER_CAPTION { [Measures].[Change Count] } ON ROWSD.SELECT DIMENSION PROPERTIES MEMBER_CAPTION { [Measures].[Change Count] } ON COLUMNSCorrect:B 21.You are designing a Microsoft SQL Server 2005 Reporting Services (SSRS) report that supports drilldown. You need to modify the report so that the detailed data is hidden when the report is initially rendered. What should you do?A.Add the detailed data to a Group. Set the Hidden property of the Group to true.B.Add the detailed data to a Matrix. Set the IsToggleChild property of the Matrix to true.C.Add the detailed data to a Subreport. Set the Hidden property of the Subreport to true.D.Add the detailed data to a Subreport. Set the IsToggleChild property of the Subreport to true.Correct:A 22.You are designing a Microsoft SQL Server 2005 Reporting Services (SSRS) report. The report uses a dataset that is constructed from an SQL table named Employees. The table has an employeeID field and a managerID field. The managerID field in each row represents the employeeID field. You execute the following Transact-SQL query to get the dataset from the report: SELECT CONVERT(varchar(255), c.FirstName + ' ' + c.LastName) as Name, e.EmployeeID, e.ManagerID FROM HumanResources.Employee AS e JOIN Person.Contact AS c ON e.ContactID = c.ContactID You need to display the dataset as a hierarchical, organizational chart, as shown in the Exhibit. (Click the Exhibit button.) What should you do?A.Use the Level function in the Left property of the Padding property of the cell.B.Group the report by the employeeID field and set the managerID field as the group parent.C.Group the report by the managerID field and set the employeeID field as the group parent. Use the Level function in the Left property of the Padding property of the cell.D.Group the report by the employeeID field and set the managerID field as the group parent. Use the Level function in the Left property of the Padding property of the cell.Correct:D 23.You are designing a Microsoft SQL Server 2005 Reporting Services (SSRS) report. The report has three columns in a table. The columns are named MachineName, MachineMfr, and MachineModel. The report navigation options must allow users to click on a row from the MachineName column and drill through to another report, and then pass the contents of the MachineName column as a parameter named MachineName. You need to configure the report navigation options to satisfy the user requirements. Which three tasks should you perform? (Each correct answer presents part of the solution. Choose three.)A.On the General tab, select the data region.B.On the Visibility tab, select the expression for the report item.C.On the Navigation tab, select the report for the Hyperlink action.D.On the Navigation tab, select the bookmark for the Hyperlink action.E.On the Navigation tab, add an expression to the Hyperlink action parameters.F.On the Navigation tab, add the name and the value to the Hyperlink action parameters.Correct:D E F 24.You are designing a Microsoft SQL Server 2005 Reporting Services (SSRS) report. The report contains financial data. An input parameter named TargetMfr is present in the report. When a value in the Machine_Mfr field matches the selected value in the input parameter, the color of the output in a column is red. Otherwise, the color of the output is green. You need to set the TextBox that contains the color property of the Machine_Mfr field to the appropriate expression. Which expression should you use?A.=Iif(Fields!Machine_Mfr = Parameters!rc:TargetMfr.Value,"Red","Green")B.=Iif(Fields!Machine_Mfr.Value = Parameters!TargetMfr.Value,"Red","Green")C.=Iif(InScope(Machine_Mfr),"Red","Green")D.=Iif(InScope(TargetMfr),"Red","Green")Correct:B 25.You are designing a Microsoft SQL Server 2005 Reporting Services (SSRS) report. The query that builds the dataset for the report uses a WHERE clause to filter the rows. The report parameter must perform the following actions: Use the new parameterized query. Run without any user input. Allow users to change the value that is passed to the query by selecting a value from a drop-down list. You need to modify the settings of the Report Properties dialog box to meet the outlined requirements. Which four tasks should you perform? (Each correct answer presents part of the solution. Choose four.)A.Ensure that the Internal check box is selected.B.Ensure that the Default values property value is set to Null.C.Ensure that the Prompt property is set to an appropriate value.D.Ensure that the Data type property is set to an appropriate value.E.Ensure that the Default values property value is set to Non-queried by using an appropriate default value.F.Ensure that the Available values property value is set to Non-queried by using the appropriate labels and values.Correct:C D E F 26.You are developing a Microsoft SQL Server 2005 Analysis Services (SSAS) project. The project contains a cube named Finance. The Finance cube contains the following objects: A measure group named SalesMeasures. A measure named SalesAmount. A time dimension named DimTime. A time dimension hierarchy named Calendar that contains Year on the first level, Quarter on the second level, and Month on the third level. You need to create a trend expression that displays the increase and decrease in the SalesAmount measure for the current DimTime member, irrespective of the level of the Calendar hierarchy. Which expression should you use?A.Case When [SalesMeasures].[SalesAmount]> [SalesMeasures].[SalesAmount],[DimTime].[Calendar].PrevMember Then1 When [SalesMeasures].[SalesAmount]< [SalesMeasures].[SalesAmount],[DimTime].[Calendar].PrevMember Then -1 EndB.Case When [SalesMeasures].[SalesAmount],[DimTime].[Calendar]< [SalesMeasures].[SalesAmount],[DimTime].[Calendar].PrevMember Then1 When [SalesMeasures].[SalesAmount],[DimTime].[Calendar]> [SalesMeasures].[SalesAmount],[DimTime].[Calendar].PrevMember Then -1 EndC.Case When [SalesMeasures].[SalesAmount]> [SalesMeasures].[SalesAmount],[DimTime].[Calendar].NextMember Then1 When [SalesMeasures].[SalesAmount]< [SalesMeasures].[SalesAmount],[DimTime].[Calendar].NextMember Then -1 EndD.Case When [SalesMeasures].[SalesAmount]< [SalesMeasures].[SalesAmount],[DimTime].[Calendar].NextMember Then1 When [SalesMeasures].[SalesAmount]> [SalesMeasures].[SalesAmount],[DimTime].[Calendar].NextMember Then -1 EndCorrect:A 27.You are developing a Microsoft SQL Server 2005 Analysis Services (SSAS) project. You create a cube that contains the following objects: A time dimension named Time that has an attribute named TimeKey. A fact table named FactOrders. The Time dimension contains different hierarchies. Two of the columns of the fact table link to the attribute. You need to view the measures for different hierarchies for both the columns in the fact table. What should you do?A.Create a single dimension usage relationship between the Time dimension and the FactOrders fact table by defining a regular relationship.B.Create a single dimension usage relationship between the Time dimension and the FactOrders fact table by defining a many-to-many relationship.C.Create two dimension usage relationships between the Time dimension and the FactOrders fact table by defining a regular relationship for both the dimension usages.D.Create two dimension usage relationships between the Time dimension and the FactOrders fact table by defining a many-to-many relationship for both the dimension usages.Correct:C 28.You are developing a Microsoft SQL Server 2005 Analysis Services (SSAS) project. The data source for a cube has tables as shown in the following exhibit. (Click the Exhibit button.) The cube contains the following objects: A dimension named DimAccounts that uses a table named Accounts. A dimension named DimCustomers that uses a table named Customers. A measure group named Transactions that uses a fact table named Transactions. A measure group named Customer Accounts that uses a fact table named CustomerAccounts. You need to create a cube dimension to browse through the measures in the Transactions fact table by using the DimCustomers dimension. What should you do?A.Create a referenced relationship and set the intermediate dimension as DimAccounts.B.Create a referenced relationship and set the intermediate dimension as DimCustomers.C.Create a many-to-many relationship and set the intermediate measure group to Transactions.D.Create a many-to-many relationship and set the intermediate measure group to Customer Accounts.Correct:D 29.You are developing a Microsoft SQL Server 2005 Analysis Services (SSAS) project. You create a cube named ExpenseData that uses a dimension named Expense. The Expense dimension is a parent-child dimension with: a key attribute named Expense. A parent attribute named Expenses of type Expense. The project contains a role named Users. You need to modify the Users role to achieve the following tasks: Restrict role members from viewing the cell values of Expenses under the expense Managerial Expenses. Ensure that all the labels for the Expense dimension are visible. What should you do?A.Change the access to Read/Write for dimension Expense for the ExpensesData cube.B.On the Cell Data tab, select the Enable read permissions check box. Enter the [Expense].[Expenses].Parent.Name<>"Managerial Expenses" expression.C.On the Cell Data tab, select the Enable read permissions check box. Enter the [Expense].[Expenses].Parent.Name="Managerial Expenses" expression.D.On the Dimension Data tab, select the dimension Expense in the ExpensesData cube in the Dimension drop-down box, and then cancel the selection of all the members under the member Managerial Expenses.Correct:B 30.You are developing a Microsoft SQL Server 2005 Analysis Services (SSAS) project. You create a dimension that contains a parent-child hierarchy. The dimension has a key attribute named Employee and a parent attribute named Employees. You need to ensure that the dimension identifies the topmost employee member of the hierarchy as the root member. What should you do?A.Set the IsAggregatable property of the Employee attribute to true.B.Set the IsAggregatable property of the Employee attribute to false.C.Set the IsAggregatable property of the Employees attribute to true.D.Set the IsAggregatable property of the Employees attribute to false.Correct:D
Wednesday, March 18, 2009
CRM-Microsoft Dynamics CRM 4.0
Hi,
Last week, I attended MS-CRM-DYNAMICS training. It’s really very nice one.Microsoft Providing best services in ERP and CRM Products also.Just I have added some links regarding to the brief description about CRM-Microsoft Dynamics CRM 4.0
Cheers,
Ravindra
Monday, March 9, 2009
SSIS – new Features in SSIS 2008
Hi,
It’s amaging to share about SSIS-2008 is powerful ETL tool in the market.
Am describing some points here.
1.SQL Server 2008- a record for loading data into a relational database using an Extract, Transform and Load (ETL) tool. Over 1 TB of TPC-H data was loaded in under 30 minutes.
2. 1.18TB of flat file data was loaded in 1794 seconds. This is equivalent to 1.00TB in 25 minutes 20 seconds or 2.36TB per hour.
3. Informatica has the fastest time previously reported, loading 1 TB in over 45 minutes. SSIS has now beaten that time by more than 15 minutes.
4.using sqlprofile task we can easily trace the data.
One of the great SSIS developer mattm posted excellent views on his blog’s.
Cheer’s..!
Part one
http://blogs.msdn.com/mattm/archive/2008/01/10/what-s-new-in-sql-server-2008-for-ssis-part-one.aspx
Part two
http://blogs.msdn.com/mattm/archive/2008/01/22/what-s-new-in-sql-server-2008-for-ssis-part-two.aspx
It’s amaging to share about SSIS-2008 is powerful ETL tool in the market.
Am describing some points here.
1.SQL Server 2008- a record for loading data into a relational database using an Extract, Transform and Load (ETL) tool. Over 1 TB of TPC-H data was loaded in under 30 minutes.
2. 1.18TB of flat file data was loaded in 1794 seconds. This is equivalent to 1.00TB in 25 minutes 20 seconds or 2.36TB per hour.
3. Informatica has the fastest time previously reported, loading 1 TB in over 45 minutes. SSIS has now beaten that time by more than 15 minutes.
4.using sqlprofile task we can easily trace the data.
One of the great SSIS developer mattm posted excellent views on his blog’s.
Cheer’s..!
Part one
http://blogs.msdn.com/mattm/archive/2008/01/10/what-s-new-in-sql-server-2008-for-ssis-part-one.aspx
Part two
http://blogs.msdn.com/mattm/archive/2008/01/22/what-s-new-in-sql-server-2008-for-ssis-part-two.aspx
What's New (Integration Services), (Reporting Services) && (Analysis Services)
Hi,
by using Sql Server 2008 we can provide excellent services to the organization by giving the Intellegence solutions for the business.
In terms of BI now Sql Server 2008 become a powerful tool in the market .
At each part of this showing a better features for the better solutions.
As per BI Suites(SSIS,SSAS,SSRS) we can generate the reports to the end - clients with accurate results.
Now am posting some links which shows you about the new features in the MSBI tool.
Trace it.Hunt it.Crack it.
http://msdn.microsoft.com/en-us/library/bb522534.aspx
http://technet.microsoft.com/en-us/library/ms170438.aspx
http://msdn.microsoft.com/en-us/library/ms171005.aspx
by using Sql Server 2008 we can provide excellent services to the organization by giving the Intellegence solutions for the business.
In terms of BI now Sql Server 2008 become a powerful tool in the market .
At each part of this showing a better features for the better solutions.
As per BI Suites(SSIS,SSAS,SSRS) we can generate the reports to the end - clients with accurate results.
Now am posting some links which shows you about the new features in the MSBI tool.
Trace it.Hunt it.Crack it.
http://msdn.microsoft.com/en-us/library/bb522534.aspx
http://technet.microsoft.com/en-us/library/ms170438.aspx
http://msdn.microsoft.com/en-us/library/ms171005.aspx
SSRS: Conditional Formatting
Hi,
In reports we have to set different conditions on reports various on the requirement.
And also we can generate reports with colors to give a bright look by the customers/clients.
Expressions are something I have found to be very handy when using SSRS. A report I needed to setup had to list when a user had last visited a site. Since they needed to make a visit every three years the report listed the last visit date and the date three years out (this was all done is the stored procedure). Sometimes folks may get behind and I wanted to highlight the sites that were past due.
The report needed to be able to be sorted by a date, town or country so I could not just list the sites by date and have the overdue visits all at the top. I decided to simply have the overdue sites listed in bold. To accomplish this I added the following to the TableRow’s font weight property:
=iif (FORMAT(Fields!ThreeYearsOut.Value, "MM/dd/yyyy") < Today(), "Bold", "Normal")
This "How to" guide provides ideas & code samples on using color to improve your users understanding of the data in your reports. It includes using gradients of color in your tables & charts. It also covers using color to represent a second dimension &/or multiple levels of data in a hierarchy.
http://blogs.msdn.com/davidlean/archive/2009/02/17/sql-reporting-how-to-conditional-color-1-4-the-basics-report-expressions-custom-code.aspx
In reports we have to set different conditions on reports various on the requirement.
And also we can generate reports with colors to give a bright look by the customers/clients.
Expressions are something I have found to be very handy when using SSRS. A report I needed to setup had to list when a user had last visited a site. Since they needed to make a visit every three years the report listed the last visit date and the date three years out (this was all done is the stored procedure). Sometimes folks may get behind and I wanted to highlight the sites that were past due.
The report needed to be able to be sorted by a date, town or country so I could not just list the sites by date and have the overdue visits all at the top. I decided to simply have the overdue sites listed in bold. To accomplish this I added the following to the TableRow’s font weight property:
=iif (FORMAT(Fields!ThreeYearsOut.Value, "MM/dd/yyyy") < Today(), "Bold", "Normal")
This "How to" guide provides ideas & code samples on using color to improve your users understanding of the data in your reports. It includes using gradients of color in your tables & charts. It also covers using color to represent a second dimension &/or multiple levels of data in a hierarchy.
http://blogs.msdn.com/davidlean/archive/2009/02/17/sql-reporting-how-to-conditional-color-1-4-the-basics-report-expressions-custom-code.aspx
Subscribe to:
Posts (Atom)