Have foundational conceptual understanding of data warehousing, data marts, and Business Intelligence. Students must be well-versed on the subjects of data warehousing, data marts, and BI, and preferably have read at least one book by Ralph Kimball or Bill Inmon. Have conceptual understanding of the components of SQL Server For example, constructing cubes, developing packages, and writing reports. Have foundational understanding of Microsoft Windows security. For example, how groups, delegation of credentials, and impersonation function in a security context.
Have foundational understanding of Web-based architecture. Capture the business and technical requirements for a Business Intelligence solution architecture. Describe the major stages and requirements of a Business Intelligence project lifecycle. Determine Business Intelligence development requirements and implement a Business Intelligence development project.
Assess and design a Business Intelligence infrastructure. Describe and plan Business Intelligence operations and their management.
Introduction to Business Intelligence Architecture. Overview of Business Intelligence. Overview of Business Intelligence Architecture. Overview of the Business Intelligence Project Lifecycle. Planning a Business Intelligence Project. Determining Business Intelligence Requirements.
Publications – Data Inspirations
Revising and Updating a Business Intelligence Project. Lab 2: Planning a Business Intelligence Project. Introduction to Business Intelligence Development. Overview of Business Intelligence Development. Managing Business Intelligence Development. Many have noted that the learning curve in building SSAS projects is particularly difficult, with a large number of intricate options that require a measureable amount of research and understanding.
For this reason, SSAS has a reputation of being difficult to learn. Although I love SSAS, I understand the observations about the learning curve, especially after having taught it for years. Second, there is SSAS Tabular, which is essentially targeted toward newer developers with the promise of an improved Visual Studio interface and easier learning curve.
Additionally, implementing advanced functionality in SSAS Tabular also results in learning-curve challenges as well. Still, SSAS Tabular does offer some opportunities to build analytic solutions in a slightly easier fashion. Unfortunately, MDX is arguably more difficult. Listing 3 shows an example of moderately complicated query — retrieving the top three products by sales, and for each of the three products, the top three cities by sales.
Ad-hoc Transact-SQL queries
The code illustrates the following concepts in MDX:. Here are two additional MDX code snippets. The first demonstrates how to express a dollar amount figure in terms from one year ago, using the MDX ParallelPeriod function and a Ship Date hierarchy. Although it can take months or even years to become strong in MDX, these code snippets demonstrate one of the hallmarks of MDX—expressing aggregated data in terms of related dimension member values. Additionally, each tool that allows developers to incorporate MDX code have their own nuances.
DAX is only used for expressions. Like most things, "it depends. The following is a snippet of a DAX formula to rank cities by the sum of reseller sales amount. One of the challenges of SSRS is knowing exactly what type of output can be created. Sometimes developers prematurely assume that they need to purchase a third-party charting tool to generate specific output that SSRS can generate albeit sometimes with considerable effort. Figures 6 through 9 show some moderately involved report output with SSRS:.
Figure 6 shows an example of drilldown capabilities, revealing or suppressing lower-level details. The report also demonstrates the ability in SSRS to dynamically format certain expressions based on a condition. In this example, the report highlights the highest shipper based on dollars , along with the highest vendor inside each shipper. The expressions to set dynamic formatting are sometimes complicated and must adhere to any scoping rules regarding groups in the report.
Figure 7 displays an SSRS matrix report. You can use the matrix when you have business values to spread across the columns axis such as salesman, product groups, years, etc. Figure 7 also demonstrates a powerful matrix feature that Microsoft added to the matrix in SQL Server the ability to have multiple expressions per spreading element i. Figure 8 shows one of the most visually impressive features that Microsoft added to SSRS performance gauges. These can serve as a visual Key Performance Indicator KPI with the additional ability to visually show through a condensed progress bar how close a measure is to a certain goal.
Figure 8 shows employee sales as a percent of sales goals, with rules for "good," "not good," and "somewhere in between. The sparkline is much like a regular chart, with the legend, tick marks, and all annotations removed. The sole intention is to show a quick visual of trends. Figure 9 shows an advanced feature in SSRS charting: the ability to have multiple sets of series groups within a report category or subcategory.
This is an instance where preparing the result set to be able to provide series and category groups is half the battle! The report designer itself, while certainly very important, represents just a portion of the total development effort required to build reporting applications. The report designer itself represents just a portion of the total development effort.
Additional R eading: In Table 1 , Item 13 references a four-part video on creating the entire performance gauge report in Figure 8. Item 14 references a detailed slide deck on creating data-driven subscriptions in SSRS. Once again, this is a topic that could span an entire article. The page contains a KPI scorecard to visually depict sales performance, along with "hot-links" so that a user can click on a portion of the scorecard and display a more detailed chart or grid on the right side of the page.
What Is SSRS?
If you know something about SharePoint development, you might suspect correctly that PerformancePoint Services provides a layer on top of SharePoint Web parts, allowing developers to focus on the general output for each segment of the page without dealing with the specifics of Web parts. There are two key points here.
The second is that developers can create visual KPIs using several different output tools. This goes back to a point I made at the beginning of this article; although the output tools are generally not terribly difficult to learn, the issue can be which tool is the best for a given situation. Just like with many questions, the answer is usually "it depends. NET reporting tools for certain situations. NET classes as well.
NET code to perform special tasks. Some of these tasks might include:. These are just four of many scenarios. In some cases the scenario is where one application needs to communicate with another one. Listing 4 shows an example of this. Stay tuned! Developers need to learn how to build solutions. This is particularly true of SSRS. Developers also need to be historians in the sense that a specific task might have been difficult in SQL Server but is easier in SQL Server due to new functionality.
These features are only available in the Enterprise Edition. Two facts of life. Which leads to Fact 2: Even with new functionality, developers often still need to apply elbow grease to meet requirements. Sure, we all complain about the long nights of research that we think should have been easier. Developers should be aware that Self-Service BI tools have limitations and require more coding and configuration and even proper architecting than some of the marketing would indicate. My Subscriber Account Advertise Write.
Chapter 1: Introduction to SQL Server Reporting Services (SSRS)
Training Home Classes Mentoring State of. Staffing Home Looking for Staff? Looking for Work? Contact Us.
Dark Kimbie. This article was published in:. CDC cannot run without Agent running. Enable a database for CDC by running the system stored procedure sys.
Data Warehouse and Business Intelligence Toolkit Books
Enable CDC on any table where you want to log changes by running the system stored procedure sys. After you run sys. The change-tracking table holds each version of every insertion, update a row for the old version of the row and a row for the new version of the row , and every deletion. An in-memory, highly-compressed, vector-based structure that leverages duplicated integer values. Imagine a fact table of orders with hundreds of millions of rows, where a single vendor foreign key represents thousands of rows.
A new optimized cache and buffer pool engine that can read batches of rows in parallel instead of one row at a time IO Statistics for queries that use the columnstore index will have a dramatically reduced number of reads Smart IO Caching that uses an aggressive read-ahead strategy Queries that effectively use the columnstore index can run as much as 10 times faster. Yes, you read that correctly. Figure 4: This figure shows the execution Plan for the Columnstore index, versus other indexes. Figure 5: SQL Server optimizes certain execution operators to use the new Batch mode, which processes packets of rows in parallel.
BIN2 collation is required to join character data in In-Memory optimized tables. The latter durability mode is not quite as fast, but SQL Server preserves the data. Define indexes either as hash indexes for single value lookups or range indexes for lookups across a range of values, such as dates. Because OLAP databases in essence provide the "special glue" to associate business measures with business dimensions, developers who write MDX queries need to realize that the SSAS engine will always deal with the context of the current query.
MDX Syntax rules for curly braces, parenthesis, and colons can be difficult to learn. The code illustrates the following concepts in MDX: The use of named sets to build a collection of member values cities and products The use of topcount to retrieve the top N members, based on some measure sales The use of the RANK function. PrevMember , [Measures]. The Matrix control permits multiple adjacent i.
SSRS can run in SharePoint and later as a standard SharePoint service application, providing better integration and slightly improved performance. Figures 6 through 9 show some moderately involved report output with SSRS: Figure 6 shows an example of drilldown capabilities, revealing or suppressing lower-level details.
Some of these tasks might include: Writing. Create an instance of the Reporting Services viewer from the Reporting. Webforms namespace. Why would you create an instance of an SSRS viewer?