Course 20467d designing business intelligence solutions with microsoft sql server 2014 free

Looking for:

MOC D: Designing Business Intelligence Solutions with MS SQL Server | ProTech 

Click here to DOWNLOAD

   

 

Download Companion Content for Microsoft Official Courseware | Microsoft

 

If no text qualifier had been selected in the Import and Export Data Wizard, these commas would have created additional columns in these rows, making the data difficult to examine as a table. Clear the filter for the CountryRegionCode column.

Note that this formula shows the earliest year that a store in this sample data was opened. Close Excel without saving any changes.

Ensure you have completed the previous demonstration in this module. Then add a new ADO. Alternatively, you can drag the task icon to the Control Flow surface. In the Request Properties pane, set the following property values. Do not click OK when finished: o ConnectionManager: localhost.

ResellerSales o TableOrView: [dbo]. On the Debug menu, click Start Debugging. View a Data Profiling Report 1. Maximize the Data Profile Viewer window and under the [dbo]. Then review the minimum and maximum values for the OrderDate column.

Under the [dbo]. Click the bar chart for any of the column lengths, and then click the Drill Down button at the right-edge of the title area for the middle pane to view the source data that matches the selected column length.

On the Debug menu, click Stop Debugging, and then close Visual Studio, saving your changes if you are prompted. When the app starts, maximize it. Select the AddressLine2 column, and then click the Drill Down button to view the source data. Select the inclusion violation for the payment type value of 0, and then click the Drill Down button to view the source data.

Note: The PaymentTypes table includes two payment types, using the value 1 for invoice-based payments and 2 for credit account payments. The Data Profiling task has revealed that for some sales, the value 0 is used, which may indicate an invalid data entry or may be used to indicate some other kind of payment that does not exist in the PaymentTypes table.

Close the Data Profile Viewer window. Which transformation type should you use? Ensure you have completed the previous demonstrations in this module. Then right-click each of the following tables and click Select Top Rows and view the data they contain.

Product o dbo. ProductCategory o dbo. ProductSubcategory 4. Then right-click dbo. DimProduct and click Select Top Rows to verify that this table is empty. Then change the package name to ExtractProducts. Right-click Data Flow Task and click Rename.

Then change its name to Extract Products. Double-click Extract Products to switch to the Data Flow tab. Alternatively, you can drag the Source Assistant icon to the Data Flow surface.

In the list of connection managers, click localhost. Products, and then click OK. Click Build Query to open the Query Builder dialog box.

In the OLE DB Source Editor dialog box, click the Columns tab, view the list of external columns that the query has returned and the output columns generated by the data source, and then click OK. Use a Derived Column Transformation 1. Alternatively, you can drag the Derived Transformation icon to the Data Flow surface. Rename the Derived Column transformation to Calculate Profit.

Select the Products source, and then drag the blue output arrow to the Derived Column transformation. Double-click the Derived Column transformation to edit its settings, and then in the Derived Column Name box, type Profit. Expand the Column folder, and then drag the ListPrice column to the Expression box. Alternatively, you can drag the Lookup icon to the Data Flow surface. Rename the Lookup transformation to Lookup Category. Select the Calculate Profit transformation, and then drag the blue output arrow to the Lookup Category transformation.

Double-click the Lookup Category transformation to edit its settings. In the Lookup Transformation Editor dialog box, on the General tab, in the Specify how to handle rows with no matching entries list, select Redirect rows to no match output. In the Lookup Transformation Editor dialog box, on the Connection tab, ensure that the localhost.

Products connection manager is selected, and then click Use results of an SQL query. Click Preview to view the product category data, note that it includes a ProductSubcategoryKey column, and then click Close to close the preview.

Configure a Destination 1. Alternatively, you can drag the Destination Assistant icon to the Data Flow surface. DemoDW, and then click OK. Select the Lookup Category transformation, and then drag the blue output arrow to the DemoDW destination. Double-click the DemoDW destination to edit its settings, and then in the Name of the table or the view list, click [dbo].

In the OLE DB Destination Editor dialog box, on the Mappings tab, note that input columns are automatically mapped to destination columns with the same name. Rename the flat file destination Uncategorized Products. Select the Lookup Category transformation, and then drag the blue output arrow to the Uncategorized Products destination. The Lookup No Match Output output is automatically selected.

Double-click the Uncategorized Products destination to edit its settings, and then click New. Then select Delimited Values and click OK. Then click OK. In the Flat File Destination Editor dialog box, click the Mappings tab and note that the input columns are mapped to destination columns with the same names, and then click OK.

On the Debug menu, click Start Debugging, and observe the data flow as it runs, noting the number of files transferred along each path. When the data flow has completed, on the Debug menu, click Stop Debugging.

Close Visual Studio, saving your changes if you are prompted. Start Excel, and open the Unmatched Products. Note that there were no unmatched products.

Alternatively, you could extract the data from the table to Excel and use formulas to determine the minimum and maximum values. Lastly, you could retrieve the Column Statistics profile request in the Data Profiling task.

When you use the package, what must you change to avoid naming conflicts? The Name property of the package. The ID property of the package. The Name and ID properties of the package.

The ID properties of the package and the individual tasks. The ID properties of the connection manager and the individual tasks. Answer: The Name property of the package. In Solution Explorer, double-click Control Flow. Use the default values for all other settings o From on the Mail tab : demo adventureworks. Select the Delete Files task and drag its green arrow to the Delete Folder task.

Connect each of the file system tasks to the Send Failure Notification task. Click the connection between the Delete Files task and the Send Failure Notification task to select it. Then hold the Ctrl key and click each connection between the remaining file system tasks and the Send Failure Notification task while holding the Ctrl key to select them all. Press F4 and in the Properties pane, set the Value property to Failure. Click anywhere on the control flow surface to clear the current selection, and then double-click any of the red constraints connected to the Send Failure Notification task.

One constraint must evaluate to True, and click OK. Note that all connections to the Send Failure Notification task are now dotted to indicate that a logical OR operation is applied.

Right-click the control flow surface next to the Send Failure Notification task and click Add Annotation. Then type Send an email message if a task fails. Drag the group to rearrange the control flow so you can see that the Delete Folder task is still connected to the Create Folder task.

On the Debug menu, click Start Debugging to run the package, and note that the Delete Files and Delete Folder tasks failed because the specified folder did not previously exist. This caused the Send Failure Notification task to be executed.

Double-click it to open it with Outlook. This time all the file system tasks should succeed because the folder was created during the previous execution. Consequently, the Send Failure Notification task is not executed. Stop debugging and close Visual Studio. Save the solution files if prompted. With which character s should you prefix variable names?

On the View menu, click Other Windows, and click Variables. In the Variables pane. In Solution Explorer, double-click Project. In the Project. Use a Variable and a Parameter in an Expression 1.

On the Control Flow. In the Properties pane, in the Expressions property box, click the ellipsis … button. Then in the Property Expressions Editor dialog box, in the Property box, select ConnectionString and in the Expression box, click the ellipsis … button.

Run the project, and when it has completed, stop debugging and close Visual Studio. Which type of container should you use? A task container. A sequence container. A for loop container. A foreach loop container. Answer: A task container. Start Visual Studio and open the SequenceContainer. Click and drag around the Delete Files and Delete Folder tasks to select them both, and then drag into the sequence container. Drag a precedence constraint from the sequence container into Create Folder.

Then right-click the precedence constraint and click Completion. Drag a precedence constraint from the sequence container to Send Failure Notification. Then right- click the precedence constraint and click Failure. Run the package and view the results, then stop debugging. Click the sequence container and press F4. Then in the Properties pane, set the Disable property to True.

Run the package again and note that neither of the tasks in the sequence container is executed. Then stop debugging and close Visual Studio. Then add a variable with the following properties: o Name: counter o Scope: Control Flow o Data type: int32 o Value: 0 5. Double-click the For Loop container and set the following properties.

Drag a precedence constraint from the For Loop Container to the Sequence Container and rearrange the control flow if necessary. Run the package, and note that the For Loop starts Notepad three times, opening the text file with the counter variable value in its name Demo1.

Close Notepad each time it opens, and when the execution is complete, stop debugging. Close Visual Studio, saving the solution files if prompted. Then double-click the Foreach loop container to view the Foreach Loop Editor dialog box. On the Collection tab, in the Enumerator list, select Foreach File Enumerator, and in the Expressions box, click the ellipsis … button. Then in the Property Expressions Editor dialog box, in the Property list, select Directory and in the Expression box click the ellipsis … button.

In the Foreach Loop Editor dialog box, on the Collection tab, in the Retrieve file name section, select Name and extension to return the file name and extension for each file the loop finds in the folder. In the Foreach loop Editor dialog box, on the Variable Mappings tab, in the Variable list, select User::fName and in the Index column select 0 to assign the file name of each file found in the folder to the fName variable.

Remove the precedence constraints that are connected to and from the Copy File task, and then drag the Copy File task into the Foreach Loop Container. Run the package, closing each instance of Notepad as it opens. When the package execution has completed, stop debugging and close Visual Studio, saving the solution files if prompted. Right-click dbo. StagingTable and click Select Top Rows to verify that it contains product data. ProductionTable and click Select Top Rows to verify that it is empty.

Start Visual Studio and open the Transactions. In Solution Explorer, double-click Move Products. Note that the control flow consists of a data flow task named Copy Products that moves products from a staging table to a production table, and an SQL Command task named Update Prices that sets the product price. On the Debug menu, click Start Debugging to run the package and note that the Update Prices task fails.

Then on the Debug menu click Stop Debugging. ProductionTable tables, noting that it now contains product data but the prices are all set to 0. You want to avoid having products with invalid prices in the production table, so you need to modify the SSIS package to ensure that, when the price update task fails, the production table remains empty. This deletes all rows in the dbo. ProductionTable; In Visual Studio, click anywhere on the control flow surface and press F4.

Then in the Properties pane, set the TransactionOption property to Required. Repeat the previous step for the Update Prices task. Run the package and note that the Update Prices task fails again. Then stop debugging. ProductionTable table, noting that it is empty, even though the Copy Products task succeeded.

The transaction has rolled back the changes to the production table because the Update Prices task failed. Run the package and note that all tasks succeed. ProductionTable table, noting that it now contains products with valid prices. Start Excel and open Products.

Note that it contains details for three more products. Then close Excel. Start Visual Studio and open the Checkpoints. In Solution Explorer, double-click Load Data. Note that the control flow consists of a file system task to create a folder, a second file system task to copy the products file to the new folder, and a data flow task that loads the data in the products file into the staging table. Click anywhere on the control flow surface to select the package, and press F4.

Then change the expression for the NewPrice column to and click OK. View the Control Flow tab, and then run the package. Note that the Create Folder and Copy File tasks, which succeeded previously, are not re-executed. Only the Load to Staging Table task is executed. Stop debugging, and verify that the Checkpoint.

StagingTable, and note that it now contains data about six products. How can you accomplish this? Then configure the precedence constraints to use a Logical OR to determine whether or not to run Task 3. Question: Which container should you use to perform the same task once for each file in a folder? Answer: A Foreach Loop container.

Question: Your package includes an FTP task that downloads a large file from an FTP folder, and a data flow task that inserts data from the file into a database. The data flow task may fail because the database is unavailable, in which case you plan to run the package again after bringing the database online. How can you avoid downloading the file again when the package is re-executed?

Answer: Configure the package to use a checkpoint. Start Visual Studio and open the Debugging. In Solution Explorer, double-click Debugging Demo. This package includes a control flow that performs the following tasks: o Copies a text file using a variable named User::sourceFile to determine the source path and a variable named User::copiedFile to determine the destination path.

On the Debug menu, click Start Debugging, note that the first task fails, and on the Debug menu, click Stop Debugging. Note that you can use this dialog box to control the events and conditions for breakpoints in your package. When you toggle a breakpoint, by default it is enabled for the onPreExecute event with a Hit Count Type value of Always. View Variables while Debugging 1. With execution stopped at the breakpoint, on the Debug menu, click Windows, and click Locals.

In the Locals pane, expand Variables and find the user:copiedFile variable, then right-click it and click Add Watch. The Watch 1 pane is then shown with the user::copiedFile variable displayed. Click the Locals pane and find the user:sourceFile variable, then right-click it and click Add Watch.

The Watch 1 pane is then shown with the user::copiedFile and user:sourceFile variables displayed. Start debugging and observe the variable values in the Watch 1 pane. Note that the sourceFile variable now refers to the correct file. On the Debug menu, click Continue and note that the Load Data task fails. Enable a Data Viewer 1.

Double-click the Load Data task to view the data flow design surface. Note that you can use this tab to enable the data viewer and specify which columns should be included, and that by default, all columns are included. Click the Control Flow tab and verify that a breakpoint is still enabled on the Copy Source File task. Then on the Debug menu, click Start Debugging.

When execution stops at the breakpoint, on the Debug menu, click Continue. In the data viewer window, click Copy Data. Then click the green continue button in the data viewer window. When execution stops because the data flow task has failed, on the Debug menu, click Stop Debugging. Start Excel, and create a new blank workbook.

With cell A1 selected, on the Home tab of the ribbon, click Paste. Then view the data you have pasted from the data viewer. Close Excel without saving the workbook, and close Visual Studio. Which of the following event types should you log? Start Visual Studio and open the Loggin.

In Solution Explorer, double-click Logging Demo. If an error message is displayed, click OK. DemoDW connection manager. Note that the Windows Event Log provider requires no configuration. Click the Providers and Logs tab, and in the Containers tree, clear the checkbox for Load Data, and then click the checkbox again to check it.

This enables you to override the inherited logging settings for the Load Data task. With Load Data selected, on the Details tab, select the OnError and OnInformation events, and then click Advanced and clear the Operator column for the two selected events. This shows the events that have been logged during the debugging session if the log is empty, re-run the package and then view the Log Events window again.

On the Start screen, type Event and run the Event Viewer app. Then expand Windows Logs, and click Application. Pricing is subject to change without notice. Pricing does not include applicable taxes.

Please confirm exact pricing with the exam provider before registering to take an exam. After the retirement date, please refer to the related certification for exam requirements.

Skip to main content. This browser is no longer supported. Download Microsoft Edge More info. Table of contents Exit focus mode. Table of contents. Instant Knowledge. Other students can be merged. You will be the only student in the class.

Official Courseware 4 Hours. Week Days. Start Time : At any time 12 AM. Select Time Zone. Training Schedule:. Enquire Now! Subscribe me to your Newsletter. Trending Technologies Microsoft Azure. Cloud Computing. Microsoft Cloud. Microsoft Dynamics Microsoft Role-based Certifications. Microsoft Dynamics.

Azure Infrastructure. Azure Development. Microsoft Security Engineer. Add Name and Email Address of participant If different from you. Request a call back? Privacy Policy.

Submit Processing. No, the published fee includes all applicable taxes. What does the fee include? The Fee includes: Official courseware Remote Labs. What is the difference between Group Training and 1-on-1? Schedule for Group Training is decided by Koenig. Schedule for 1-on-1 is decided by you. What is the advantage of 1-on-1 if there can be other students? In 1-on-1 you can select your own schedule, other students can be merged but you select the schedule. Denunciar este documento.

Marcar por contenido inapropiado. Descargar ahora. Buscar dentro del documento. Ask your Microsoft Learning Partner how you can prepare for certification. Citrix XenServer 5. Hyperion Foundation Services.

Hyper V. Business Intelligence an Introduction. SAP BO4. Management in Daraz. Chaitanya Pudi - Copy.

 

SQL Server BI Solutions | Firebrand Training.

 

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support. This exam retired on January 31, For currently available options, please refer to the Browse Certifications and Exams page.

This exam is intended for business intelligence BI architects, who are responsible for the overall design of a BI infrastructure and how it relates to other data systems in use. Please note that this exam does not include questions on features or capabilities that are present only in the SQL Server product.

For more information, please download and review this document. Related exams: none. Important: See details. Price based on the country or region in which the exam is proctored.

All objectives of the exam are covered in depth so you'll be ready for any question on the exam. Download exam skills outline. Learning paths or modules are not yet available course 20467d designing business intelligence solutions with microsoft sql server 2014 free this exam.

No current courses available course 20467d designing business intelligence solutions with microsoft sql server 2014 free this exam. Demonstrate your broad skill sets in SQL administration, building enterprise-scale data solutions, and leveraging business intelligence data - both on-premises and in cloud environments.

See two great offers to help boost your odds of success. Review and manage your scheduled appointments, продолжить чтение, and transcripts. Learn more about requesting an accommodation for your exam. Pricing is subject adobe premiere pro cc free change without notice. Pricing does not include applicable taxes. Please confirm exact pricing with the exam provider before registering to take an exam.

After the retirement date, please refer to the related certification for exam requirements. Skip to main content. This browser is no longer supported. Download Microsoft Edge More info. Table of contents Exit focus mode. Table of contents. Two ways to prepare Online - Free. Instructor-led - Paid. Items in this collection. Hide completed. Instructor-led courses to gain the skills needed to become certified.

Related certifications There may be certifications and prerequisites related to "Exam Designing Business Intelligence Solutions with Microsoft SQL Server" MCSE: Data Management по этой ссылке Analytics Demonstrate your broad skill sets in SQL administration, building enterprise-scale data solutions, and leveraging business intelligence data - both on-premises and in cloud environments.

Exam resources Exam Replay See two great offers to help boost your odds of success. Certification dashboard Review and manage your scheduled appointments, certificates, and ссылка на подробности. Request accommodations Learn more about requesting an accommodation for your exam.

Exam policies and FAQs Review and manage your scheduled appointments, certificates, and transcripts.