Knight's Microsoft Business Intelligence 24-Hour Trainer

Leveraging Microsoft SQL Server Integration, Analysis, and Reporting Services with Excel and Sharepoint
3.8 (33 ratings) Instead of using a simple lifetime average, Udemy calculates a
course's star rating by considering a number of different factors
such as the number of ratings, the age of ratings, and the
likelihood of fraudulent ratings.
1,113 students enrolled
$50
Take This Course
  • Lectures 46
  • Contents Video: 8.5 hours
    Other: 30.5 hours
  • Skill Level All Levels
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 10/2013 English

Course Description

Microsoft Office 2010 and SQL Server 2008 R2 bring the power of Microsoft's enterprise Business Intelligence (BI) tools to the vast Microsoft Office market. If you are just starting to get a handle on Microsoft BI tools, this course provides you with the just the right amount of information to perform basic business analysis and reporting. You'll explore the components and related tools that comprise the Microsoft BI toolset as well as the new BI features of Office 2010. After a basic primer on BI and data modeling, the expert team of authors provides you with step-by-step lessons in the videos and ebook on how to use SQL Server Integration Services, SQL Server Analysis Services, SQL Server Reporting Services, Excel BI (including PowerPivot), and SharePoint. When you’re done, you’ll be able to create dashboards, visualizations, and reports to enhance your business’s understanding of your data. With more than 8 hours of video training arranged into more than 35 bite-sized lessons, this is the perfect way to get up to speed on Microsoft’s complete suite of BI tools.

As a bonus, we’re including the complete ebook for a second ebook Professional Microsoft SQL Server 2012 Integration Services. With this crucial resource, you will explore how this newest release serves as a powerful tool for performing extraction, transformation, and load operations (ETL). A team of SQL Server experts deciphers this complex topic and provides detailed coverage of the new features of the 2012 product release. The authors present you with a new set of SISS best practices, based on years of real-world experience and it includes case studies and tutorial examples to illustrate advanced concepts and techniques. You’ll also want to have the free AdventureWorks and AdventureWorksDW databases installed. You can find the versions of these databases we used for Professional Microsoft SQL Server 2012 Integration Service on the Wrox website at http://www.wrox.com/go/SQLServer2012DataSets. Please note that we have removed the version number from the database names. If you already have a versioned database like the AdventureWorks2008R2 database though, this will work for the purposes of the demo.

Visit www.wrox.com and www.wiley.com

What are the requirements?

  • All Internet browsers, including Internet Explorer, Safari, Chrome, and Firefox

What am I going to get from this course?

  • Over 35 lessons and 8 hours of video!
  • Learn important BI concepts with thorough discusses.
  • Walk through the Try It sections in each lesson to learn on your own.
  • Learn the various features of SSIS and SSAS.
  • Discover how to build various reports (matrix, parameterized, etc.) to include a dashboard and visualization.
  • Integrate your environment with Excel, PowerPivot, data mining, SharePoint.

What is the target audience?

  • IT professionals, DBAs, and tech-savy business analysts who are new to Microsoft's BI tools
  • This is a beginner business intelligence course but it does assume that you know SQL Server 2005 or 2008 to run queries against the database engine. It is structured with tutorials for a beginner; it focuses on only the essential components and teaches you what need to complete your project at work or school. As such, this is not a reference filled with a description of every property in a given task.

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: Data Warehousing and Business Intelligence
Introduction to the Course
Preview
Article
8 pages

Please Note: You will need to view this lecture in the split screen mode in order to view the menu that will allow for downloading the materials.

This chapter introduces you to how Business Intelligence can improve your organization’s view into its operations and uncover hidden areas of profitability and analysis. You'll look at the challenges your organization faces with data and business intelligence and how you'll solve these challenges. Unlike all of the other lectures in this course, there's no video component to lesson 1. Consider it a pre-reading assignment. However, the supplemental materials link for lesson 1 is the code you'll need to work with in many of the other lectures.

16:38

Get up to speed! Learn about choosing the dimensions and designs dimension tables that work for you. The Try It demonstrates creating a dimension table in SQL Management Studio to handles change, data from multiple source systems, and history tracking.

13:15

Just the facts! This lesson discusses fact tables, what they do, and how to choose and design one. The Try It illustrates how to create a fact table in SQL Management Studio and choose additive values appropriately.

Section 2: SQL Server Integration
17:27

What is SSIS? This lesson serves as your introduction to SSIS and includes instructions for creating SSIS packages and working with BIDS. The Try It creates a solution and project using BIDS, creates an SSIS package to extracts data from a database, adds a column to the data set and exports data to a text file.

11:47

Besides showing how easy it is to construct, configure, and add constraints to tasks in a container, this lesson’s Try It shows how to create an SSIS package that loops through contents of a table and updates a column in each loop iteration.

16:51

With data flow it’s easy to extract, clean and load data to your destination. This lesson introduces the Data Flow Designer and shows how to create sources and destinations, transformations and data flow paths. The Try It demonstrates how to build an SSIS package that extracts employee pay rate increases from a database and updates them.

14:08

Are certain of your tasks commonplace? You can utilize some common SSIS packages to extract data, loop over text files and rename and archive files. The Try It section covers creating an SSIS package that loops over a list of text files, imports data from them, and then renames and moves files to an archive.

15:33

Need to load dimension tables? SSIS’s Slowly Changing Dimension (SCD) Wizard walks you through the process. You learn SCDs basics, including the various types and inferred members. You then create an SSIS package using the Wizard.

10:30

In this lesson you learn all about loading fact tables as well as the importance of natural keys. The Try it demonstrates creating an SSIS package to load a fact table.

07:23

Deployment time! This lesson shows how to deploy a single package using either SQL Server Management Studio (SSMS) or Business Intelligence Development Studio (BIDS). You also see how to deploy multiple packages via a deployment utility.

Section 3: SQL Server Analysis Services
04:32

Your data warehouse is loaded and large! SQL Server Analysis Services (SSAS) to the rescue! In this lesson you learn about cube technology for generating ad hoc reports, MDX for retrieves data from the cube, and BIDS for SSAS.

20:22

To build a cube, you need a data source. This lesson shows how to create one. You also see how to create a data source view (DSV) for filtering data, and a named calculation—a small expression stored in the DSV. You also learn a bit about data profiling.

12:00

Ready to build a cube? The Cube Wizard automates the process. Besides using the Cube Wizard, you see how to identify measured groups and dimension, which are required for your walk through the Wizard.

12:38

With your cube deployed, it’s time to add dimensions. In this lesson, you learn how to use the Dimension Editor to choose your dimensions, build hierarchies, establish attribute relationships, and define attribute keys. The Try It has you add a hierarchy for a sales territory, establish the necessary attribute relationships, and then hide the sales territory key.

10:59

With dimensions complete, it’s time to tour the various tabs of the Cube Editor to format the measures and define aggregation types. The Try It formats each measure to its proper type, places specific measures into their own folder and browses the cube after deployment.

13:52

As your project changes over time, so does its requirements. You prepare for this eventuality by in this lesson by adding a new measure, measure group, and dimension. The Try It illustrates adding a fact table to a cube, formatting the measures and then adding a new employee dimension.

21:15

This lesson gives a condensed overview of MultiDimension eXpression language (MDX), which helps you perform calculations, build KPIs, and generate reports. You learn about tuples and sets, filtering data, and navigation functions.

12:37

Now that you know MDX basics, you can create custom calculations to solve problems on the fl. You can also create named sets to filter or order data. The Try It has you creating a calculation to determine the average sale per customer visit, and defining things so sales show up in red when they fall below a specific value.

22:54

Want to make predictions or recognize patterns data? Data Mining can help you explore your data. Here, you discover how to create, validate and query a mining model.

16:21

How do you secure and optimize your user’s experience? Using SSAS in this lesson, you learn how to secure your cube, partition and aggregation your data, implement usage based optimization, and then process your cube. The Try It section covers partitioning data into two partitions and creating an aggregation for performance.

Section 4: SQL Server Reporting Services
01:35

Need to show your client or boss a report? No problem, with SQL Server Reporting Services (SSRS). This quick overview explains SSRS basics.

05:09

The Report Wizard is great for a quick report where appearance doesn’t matter. This lesson illustrates how to use the Wizard to create a data source, a data set and a simple report. In the Try It, you create a report that contains a table, and then format the table with the Text box property.

11:04

What if you need to group your report in row groups and column groups? A matrix report is the answer. This lesson demonstrates how to display measures on a matrix, and how to group both columns and rows.

11:30

Wouldn’t it be great if your end-users could change an existing report themselves? In this lesson, you learn all about parameterizing reports with parameter properties and multi values. The Try It shows how to create a report with parameters, how to create a query for the parameter, and how to set up the available values.

18:48

Although the MDX language is complex, building reports on your cube using MDX doesn’t have to be. This lesson shows how to query a cube, gives background on measure groups, and demonstrates how to define cube parameters. The Try It shows how to create a report cube, place parameters on the query, and then generate a matrix report off the cube.

11:30

You can enhance any report with a map that displays data by location—especially helpful for businesses with national or international stores. This lesson discusses the map tools in SQL Server, illustrates how perform state mapping, and discusses spatial data. In the Try It, you create a query with spatial data and a map report, and then add a Bing map layer.

18:22

You can set up a dashboard to serve as an overview of company information. Besides learning how to create one, you see how to create drilldowns and use filters. You also learn how to generate a drilldown, and a chart on a dashboard in the Try It section.

15:22

You’re done with your reports and need to deploy! This lesson shows just that, as well as how to set up subscriptions and security on your reports. During the Try It, you deploy a report from BIDS, work with the report manager, and set up a subscription.

12:15

Displaying your data in a more appealing and graphical manner is easy with new SSRS components. Sparklines and data bars, provide in-row visualization of data, while indicators represent a single data value quickly, such as ratings or conditions. For the Try It, you create an SSRS report with all these components.

12:52

Whether you want to build a report from scratch or use a Wizard, Report Builder 3.0 helps you design, author and publish a report for sharing. Besides building reports you see how to incorporate shared datasets and report parts, the subject of the Try It.

Section 5: Containers
09:25

Want to generate nice looking reports from your cube using Excel? In this lesson, you see how to connect with Excel to build such a report and then learn best practices for reporting in Excel. The Try it covers creating an Excel cube report, choosing data for analysis and then using that data for production performance.

10:18

What is PowerPivot? An add-in that expands Excel by combining it with Analysis Services! This lesson and it’s Try It covers PowerPivot essentials including: connecting to different data sources; loading selecting, and filtering data; and best practices for working with data sources.

11:35

Now that you’ve loaded data into PowerPivot, you learn about its report components, how to load and build reports, and how to add visualization to it. In the Try It, you use a table and chart to report on sales trends by product categories and subcategories.

10:12

You can combine the power of data mining with Excel! This lesson shows how to access data mining in Excel and what types of analysis you can perform. The Try It covers building a report that categorizes buyers and then uses the report to see how categories influence purchasing.

Section 6: Sharepoint
08:56

Want your client to say “Wow!”? You learn about the power of SharePoint for Business Intelligence through discussion of its four components: PerformancePoint, PowerPivot, Excel Services and Reporting Services. The Try It challenges you to think about how to utilize SharePoint for your company or client.

15:40

To harness Reporting Services, you must install all the proper integration tools, configure it on both the reporting and SharePoint side, and then deploy it. In the Try It, you create a new SharePoint site as well as a new document library for storing reports. You then deploy a report to your new SharePoint site.

21:36

How is your business performing? This lesson shows you how to tap into PerformancePoint by building a dashboard, how to work with the Decomposition Tree tool and how to build in nice drill-up and drilldown features. The Try It shows how to deploy a completed cube, create a scorecard using the Internet Revenue KPI, and how to create an analytic chart.

17:49

You can share your Excel documents in a collaborative environment with Excel Services. Here, you learn about the different services it offers, how to configure it and how to share your Excel documents with colleagues. In the Try It, you deploy a cube, create a Pivot Chart and PivotTable, and then send the results to SharePoint.

19:06

You know how to use PowerPivot in Excel, but did you know you can also use it in SharePoint? This lesson shows how to install and configure PowerPivot, share a PowerPivot document, and place documents into a gallery. The Try It shows how to create a Pivot Chart and Table generated from a database, and then send the results to SharePoint.

08:01

This lesson showcases some of the management tools available to monitor your Business Intelligence solutions. In the Try It, you further investigate the PowerPivot Management Dashboard.

Section 7: Personal Library
434 pages

A book-and-video introduction to Microsoft's Business Intelligence tools

If you are just starting to get a handle on Microsoft Business Intelligence (BI) tools, this book and accompanying video provides you with the just the right amount of information to perform basic business analysis and reporting. You'll explore the components and related tools that comprise the Microsoft BI toolset as well as the new BI features of Office 2010. After a basic primer on BI and data modeling, the expert team of authors provides you with step-by-step lessons in the book and videos on the accompanying DVD on how to use SQL Server Integration Services, SQL Server Analysis Services, SQL Server Reporting Services, Excel BI (including PowerPivot), and SharePoint.

  • Integrates instructional videos with each of the lessons found in the book to enhance your learning experience
  • Explores the Microsoft Business Intelligence (BI) toolset as well as the new BI features of Office 2010
  • Encourages you to practice what you've learned in "Try It Out" sections
  • Contains video demonstrations that walk you through how to tackle each lesson featured in the book

With Knight's Microsoft Business Intelligence 24-Hour Trainer, veteran authors present you with an ideal introductory book-and-video package so that you can get started working with the BI toolset immediately!/p

Note: As part of the e-book version of this title, video lessons can be accessed at wrox.com using a link provided in the interior of the e-book.

964 pages

An in-depth look at the radical changes to the newest release of SISS

Microsoft SQL Server 2012 Integration Services (SISS) builds on the revolutionary database product suite first introduced in 2005. With this crucial resource, you will explore how this newest release serves as a powerful tool for performing extraction, transformation, and load operations (ETL). A team of SQL Server experts deciphers this complex topic and provides detailed coverage of the new features of the 2012 product release. In addition to technical updates and additions, the authors present you with a new set of SISS best practices, based on years of real-world experience that have transpired since the previous edition was published.

  • Details the newest features of the 2012 SISS product release, which is the most significant release since 2005
  • Addresses the keys to a successful ETL solution, such as using the right enterprise ETL tool and employing the right ETL architecture in order to meet the system requirements
  • Includes additional case studies and tutorial examples to illustrate advanced concepts and techniques

Professional Microsoft SQL Server 2012 Integration Services is a valuable resource that meets the demands and high expectations of experienced SSIS professionals.

217 pages

Note - this is the same eBook as posted to Lecture 42, it has been split in 2 parts do to it's size. This Lecture, 44, is Part 1 of Kinghts Microsoft Business INtelligence 24- Hour Trainer, Part 2 is located in Lecture 45. Some students were having trouble loading the complete file within their browser. We recommend using Google Chroome to view our courses.

An in-depth look at the radical changes to the newest release of SISS

Microsoft SQL Server 2012 Integration Services (SISS) builds on the revolutionary database product suite first introduced in 2005. With this crucial resource, you will explore how this newest release serves as a powerful tool for performing extraction, transformation, and load operations (ETL). A team of SQL Server experts deciphers this complex topic and provides detailed coverage of the new features of the 2012 product release. In addition to technical updates and additions, the authors present you with a new set of SISS best practices, based on years of real-world experience that have transpired since the previous edition was published.

  • Details the newest features of the 2012 SISS product release, which is the most significant release since 2005
  • Addresses the keys to a successful ETL solution, such as using the right enterprise ETL tool and employing the right ETL architecture in order to meet the system requirements
  • Includes additional case studies and tutorial examples to illustrate advanced concepts and techniques

Professional Microsoft SQL Server 2012 Integration Services is a valuable resource that meets the demands and high expectations of experienced SSIS professionals.

217 pages

Note - this is the same eBook as posted to Lecture 42, it has been split in 2 parts do to it's size. This Lecture, 45, is Part 2 of Kinghts Microsoft Business INtelligence 24- Hour Trainer, Part 1 is located in Lecture 44. Some students were having trouble loading the complete file within their browser. We recommend using Google Chroome to view our courses.

An in-depth look at the radical changes to the newest release of SISS

Microsoft SQL Server 2012 Integration Services (SISS) builds on the revolutionary database product suite first introduced in 2005. With this crucial resource, you will explore how this newest release serves as a powerful tool for performing extraction, transformation, and load operations (ETL). A team of SQL Server experts deciphers this complex topic and provides detailed coverage of the new features of the 2012 product release. In addition to technical updates and additions, the authors present you with a new set of SISS best practices, based on years of real-world experience that have transpired since the previous edition was published.

  • Details the newest features of the 2012 SISS product release, which is the most significant release since 2005
  • Addresses the keys to a successful ETL solution, such as using the right enterprise ETL tool and employing the right ETL architecture in order to meet the system requirements
  • Includes additional case studies and tutorial examples to illustrate advanced concepts and techniques

Professional Microsoft SQL Server 2012 Integration Services is a valuable resource that meets the demands and high expectations of experienced SSIS professionals.

Conclusion to the Course
Preview
Article

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Founded in 1807, John Wiley & Sons, Inc. has been a valued source of information and understanding for more than 200 years, helping people around the world meet their needs and fulfill their aspirations. Wiley and its acquired companies have published the works of more than 450 Nobel laureates in all categories: Literature, Economics, Physiology or Medicine, Physics, Chemistry, and Peace.

Wiley is a global provider of content and content-enabled workflow solutions in areas of scientific, technical, medical, and scholarly research; professional development; and education. Our core businesses produce scientific, technical, medical, and scholarly journals, reference works, books, database services, and advertising; professional books, subscription products, certification and training services and online applications; and education content and services including integrated online teaching and learning resources for undergraduate and graduate students and lifelong learners. Wiley's global headquarters are located in Hoboken, New Jersey, with operations in the U.S., Europe, Asia, Canada, and Australia. The Company is listed on the New York Stock Exchange under the symbols JWa and JWb.

Ready to start learning?
Take This Course