Building a On-Site Data Warehouse Using Microsoft Stack

Most IT specialists are aware of individual components within SQL Server and may use one or two of them regularly for data analytics or general operations. Outside of IT, others may be familiar with one of several analytics-oriented SQL Server add-ons such as those for Excel that are targeted at data analysis or data mining in general.

It is likely that far fewer staff, if any, have combined SQL Server components into a complete data analytics stack that enables the organization to build a high-performance data analytics solution using on-premise and external data sources that deliver meaningful analytics across the enterprise.

Fortunately, Microsoft provides all the tools and services necessary for a traditional, on-site data analytics system. This article outlines these components and their general usage. Of necessity, it cannot cover all the intricacies that may arise out of business-specific requirements.

Normalized vs. De-Normalized Databases

Relational databases use a normalized data organization optimized for rapid insert and update operations while minimizing data reads, which makes them ideal for OLTP processing. These types of databases are not suitable for data warehouses, which use an OLAP design that de-normalizes the data. This organization is optimized for the high-volume read performance required for analytical processing.

Other Reasons to Use a Separate Data Analytics Database

In addition to optimizing read performance, creating a separate OLAP database versus utilizing the OLTP corporate database has several advantages:

  • Analytical data queries requiring summarization of large data quantities will not bog down corporate database servers that collect related data items from disparate resources
  • Historical data can be cached rather than archived, which is typical on OLTP databases
  • You are free to consolidate data from several sources including off-premise databases, social media sites or a variety of document formats such as XML or Excel.
  • Cleansing and optimizing your data for analysis will be automated using your business-specific rules, queries and schedule.

The Microsoft Data Analytics Stack

SQL Server 2016 contains the entire suite of components necessary to build your first data warehouse. The three critical components are Integration Services, Analysis Services and Reporting Services.

SQL Server Integration Services (SSIS)

SSIS is SQL Server’s ETL component that connects to your data sources, migrates data, restructures it according to business-specific rules and populates the database. It manages acquiring data from multiple sources, cleaning this data plus performing type conversions and any other transformations required to streamline analysis. SQL Server Agent permits automatic scheduling of these processes so the database always contains the most relevant and timely data.

Use the particular business problems you are solving to guide ETL preparation and specification. A user-centric approach pares down and organizes the data being collected for the most efficient extraction and presentation.

SQL Server Analysis Services  (SSAS)

SSAS is critical to creating the most useful and highest-performing database queries. This component transforms normalized or relational data into multidimensional database “cubes” that are tuned to deliver rapid results to ad hoc queries. It does so through data indexing, data aggregation and enabling complex data calculations to be embedded at the cube level where processing is most efficient.

SQL Server Reporting Services (SSRS)

SSRS is used to make queries on an SQL Server database as well. It offers everything required to build detailed, complex reports with visualizations and facilities to manage and distribute reports securely. Users may view reports via the SSRS Web Portal, Power BI or a SharePoint server. End users are able to import reports directly to Excel or PowerPoint.

SQL Server Data Tools

This package works within Visual Studio (VS) and provides development tools for all the integration, analysis and reporting steps outlined previously. It is free and downloads a VS shell if you do not already have VS installed. SSDT lets you design and build your OLAP database and SQL Server content in much the same manner as you would for a VS application. It replaces the previous Business Intelligence Development Studio.


Microsoft’s SQL Server plus SSDT support development of a complete data analytics analysis system that includes an efficient OLAP database, optimized data schema and a full pipeline of data acquisition, pre-processing, analysis, delivery and visualization. Many organizations without a current data analytics solution may not have expertise in all the SQL Server components required, and those that do have typically only applied their skills toward OLTP type solutions.

Thus, many businesses employ an experienced partner for architecting a data analytics system that is scalable, extendable and highly secure. The most capable consultants are able to train and guide in-house staff through the entire development and maintenance lifecycle such that the enterprise becomes self-sufficient at further expanding and optimizing the system.

If you need help or advice on your data analytics system, let us know. Our team of data experts have worked with businesses large and small across all industries and have the right specialized technical skills to guide you towards a solution.




Automated Testing for SSRS Reports

Motivations for SSRS Report Testing

Both data warehouse developers and end users of data analytics reports have a keen interest in the accuracy and appearance of data content. As SSRS reports are being developed, they are typically tested piecemeal during construction and as a whole when completed for both these aspects.

However, it is always possible to overlook certain report items or their representation after report deployment. Furthermore, issues with changes in data sources or the ETL flow may introduce anomalies or errors that affect data values or their presentation at a later date.

SSRS Report Testing Automation Issues

To increase report testing efficiency and accuracy, automated testing is key during both development and maintenance of SSRS reports, especially for organizations utilizing dozens or hundreds of reports on a regular basis. Automation can decrease development time and play an important role in correcting discrepancies in report data post-deployment, which could otherwise negatively impact confidence in the data warehouse.

The complexity of interactions within SSRS itself and the many other components of SQL Server, however, make the creation of fully automated testing a tricky business. Furthermore, the high-degree of customization possible for SSRS reports implies that customized testing approaches are probably required in addition to standardization of report content and layout wherever feasible.

SSRS Testing Areas for Automation

Unit Testing

There are a large number of characteristics that can be tested during development via unit tests. Unit tests can migrate to test suites for troubleshooting post-deployment bugs quickly. These are a few example coverage areas for such automated tests:

  • If data is not reaching a report, validate the data source, check that the correct mappings to the dataset are being used and that each column’s Visibility property is set correctly.
  • If data is not present and the report uses stored procedures, validate the SP parameters, their data types, conversions and formatting requirements.
  • The failure of data to group as expected can be tested by examining the grouping expressions and that the Aggregate function is applied to Numeric columns.
  • Create a query test framework that can take as input individual queries or query scripts. Such a framework would contain validated comparison datasets including metadata to run against actual queries/datasets with each report’s specific data source, parameters or configuration.
  • Test report rendering into formats used by the organization. Initial tests require manual inspection of results, but subsequent tests could be automated using input reports under version control. The most useful of these tests is to output the report in XML format, which is likely to be complete, free of data conversions and most amenable to automated comparison tests.

A possible starting point for building a permanent RS unit testing framework can be found here:

Layout Testing

Automating report layout tests typically presents the greatest difficulties. If your organization uses the SSRS web portal, however, you can take advantage of a number of web UI automation tools that facilitate such testing.

Selenium Web-Driver is a free tool for testing web page layout and functionality, which works with Firefox, IE, Chrome and Safari. Automation test scripts are written in either Java, C#, Ruby, Python or JavaScript. Scripts utilize the Web-Driver API which invokes a live browser or runs headless.

Other UI-based testing tools are also available such as the open-source Sikuli, freeware AutoIt or TestComplete, which is a proprietary tool by SmartBear.


Test automation has its limits, of course. This is especially so with regard to SSRS reports testing. For instance, usability testing is clearly out of scope for automation. Automation is further complicated in SSRS, since data is often manipulated beneath the covers beyond control of the report itself. For example, data may be manipulated outside of queries in embedded VB.Net code or in external class libraries.

Even so, automating report testing wherever feasible always pays off. Choose the low-hanging fruit first before expanding into other areas, such as troubleshooting suites and GUI-based layout testing. As you progress, you are likely to find that developing a mindset aimed at automation frequently instills a virtuous cycle of discovering new opportunities for further test automation.

In SSRS testing areas that present the most difficult obstacles, employ third-party tools or employ an experienced automation consultant who can demonstrate automation methods most appropriate for your SSRS development and usage scenarios.

Power BI and SSRS: How Do They Pair?

SQL Server Reporting Services is a powerful reporting tool in its own right with strong programmability, extensibility and scalability features that have led to its widespread adoption. As a report authoring tool, however, SSRS has lacked the powerful dashboard creation features of its competitors. With the release of SQL Server 2016, it now has the capability to produce stunning, information-packed visualizations when paired with Microsoft’s Power BI suite of business analytics tools.

Pairing Up SSRS and Power BI

The only prerequisites to get SSRS and Power BI to play together is the installation of SQL Server 2016 CTP 3, a Power BI account of any level, registration of SSRS with Power BI and SQL Server Agent running. Register SSRS with Power BI via Reporting Services Configuration Manager by entering the SQL server name and RS instance and connecting. Then under Power BI Integration, register a work or school Power BI account. The account must have permissions for registering apps with Azure Active Directory.

Basic SSRS/Power BI Interactions

Once registration is complete, you can upload any Power BI file to the Reporting Services web portal. After uploading, you can open a Power BI file, interact with the report, update its model, refresh the data and save it back to RS web portal when operations are complete. All other consumers of the report will see the changes instantly.

Power BI Mobile App

There is also a Power BI mobile application that connects to Reporting Services as long as network access to the RS server is obtainable from your location. Once connected, you can interact with reports or already defined KPIs in the web portal just as you would from your desktop.

BI Visualization Pinning

Where the partnership between SSRS and Power BI really shines is the ability for users to pin any report visualization, such as charts, matrices, maps and so on to a Power BI dashboard. These pins can have additional annotations. To take advantage of this new feature, you need to configure RS with a BI account that has application add rights in Azure Active Directory.

Once you are signed into RS with Power BI credentials, you can begin pinning. The most common errors during this interaction occur because SQL Agent is not running, since the RS subscription subsystem is utilized for this feature. When the pairing is set up correctly, a Power BI icon appears in the RS toolbar.

You access “pinnable” items via an additional tab, which darkens everything in the display that you cannot pin. Selecting an item brings up a dialog popup from which you select the working group which contains the dashboard to which you will add the pin. You also specify an update frequency so the pin is kept up to date with regular data refreshes.

Note that pins may “expire,” which means they fail to show up at a later date. This situation occurs if you have not refreshed the authentication token during that period. This refresh is accomplished simply by signing into Power BI every 90 days or less at which time all your pins are updated.

The Future of SSRS and Power BI

One of the clear drawbacks to Power BI is that, for now, it is strictly a cloud-based solution. Users are clamoring for the capabilities of Power BI for their on-premise and hybrid systems. Microsoft is responding by rewriting SSRS to extend Power BI-like features to on-premise installations. This also requires that legacy SSRS users be accommodated to provide them with advanced features and an upgrade path.

The new SSRS, which has been making an impact since SQL Server 2016 RC1, supports all modern web browsers using HTML5. A single SSRS mobile app runs on all three major platforms. The RS interface and reports can be custom-branded for a more professional look.


As SSRS has become the de facto modern standard for BI analytics systems, its weakness in data visualizations has come to the fore, which Microsoft is softening by creating ways for SSRS to pair up with Power BI for great visualizations. This pairing, for now, is loosely coupled with the ability to interact with Power BI reports, a mobile Power BI app that connects to an RS server and the ability to attach SSRS visuals as a tile inside of a Power BI dashboard.

They are now adding more capabilities within SSRS itself for big customers with on-premise BI operations, which will include another mobile app and better visuals. It seems logical that at some point Microsoft will opt to unify UIs, report formats and internal subsystems within both SSRS and Power BI in order to reach their eventual goal of one billion Power BI users.


Check out our latest interactive Power BI demos. Play with real data to see how it translates into interactive visualizations:


Migrating Crystal Reports to SSRS

Most legacy ERP systems still utilize Crystal Reports for creating detailed, flexible and dynamic business reports. However, multiple acquisitions of the software over the years has resulted in it falling behind software trends in Data Analytics systems. Even its latest owner, SAP, is moving to Microsoft’s SQL Server Reporting Service as its go-to solution.

The SSRS add-on to SQL Server has evolved to a web-based report server featuring essential Data Analytics functions such as data-driven subscriptions and a multi-format rendering engine. Additionally, its scalability potential far exceeds that of Crystal Reports. Since many companies already own an SQL Server license and SSRS comes bundled for free, many are anxious to convert legacy Crystal Reports resources to SSRS.

Migration Process Overview

If your organization is fortunate enough to have only a handful of relatively simple reports to convert, you might employ one of several third-party automatic CR/RPT to SSRS/RDL tools. If you are using SQL Server 2016, you will need to further upgrade the RDL output via SQL Server Data Tools as Microsoft has recently made changes to the internal report object model.

Most companies, however, will have dozens or hundreds of reports of varying complexity for conversion and many of these will not lend themselves well to automatic conversion.

Step One: Report Analysis

In order to reduce the number of reports to convert and prioritize the remainder, follow these guidelines:

  • Determine if the report is still used directly or indirectly via another report
  • Record how the report is accessed and how often
  • Discover which individuals or group users are using the report
  • Prioritize which report items are most important to their business decision value

The results of this analysis will reveal the shortest path to converting the highest value reports and report data. This step often suggests ways in which reports can be consolidated or standardized by sharing common information across reports. This presents opportunities to share SQL stored procedures across reports as well.

In preparation for migration, also document various technical aspects such as the specific data sources being used, which may be databases offline and online or software products such as OLAP Intelligence or MicroStrategy Intelligence Server.

Step Two: Migration Implementation

You should now be prepared to recreate report characteristics as new SSRS reports. Proceed in this order:

  1. Create new SQL queries and then fetch and verify the data sets for accuracy
  2. Set up the SSRS report layouts noting that Crystal Reports is a banded report design, whereas SSRS uses a Table object
  3. Incorporate row-level data while building grouping relationships and subtotals
  4. Devise formulas and procedures to derive condensed results from primary data
  5. Create visual reporting artifacts such as charts, graphs and matrices
  6. Finally, set up any required sub-reports and linked reports

Unfortunately, completing these steps accurately may require serious sleuthing skills if the original reports are not well documented, which is often the case.

Difficulties of Automation

Although some conversion steps above may appear amenable to automation, such tools often run into serious obstacles even with reports of relatively low complexity. These problems often derive from the distinct software architectures used by each product. Furthermore, no Crystal Reports conversion tool performs the initial analysis step that’s so vital to ensuring your upgraded reporting system is cost-effective and efficient.

There are numerous examples of automated conversion issues that are difficult to debug and correct:

  • The use of shared data sources often generates exceptions
  • Differences in parameter names may interfere with data source connections
  • Parameter renaming often breaks dependencies in the same or other reports
  • Required parameters in SSRS are sometimes missing in Crystal Reports.
  • Layout fields and blocks often intersect, and overlapped objects render poorly
  • The Keep Together feature in Crystal Reports frequently translates incorrectly
  • Expressions and VB code in Crystal Reports often break or produce incorrect results in SSRS
  • Multicolumn reports and text boxes frequently require manual adjustment in SSRS

Even in cases where auto-conversion may appear to work at a 95% level, it is highly recommended that the conversion be overseen by a trained specialist who can uncover hidden flaws and correct them before they reach end users who depend on a clean, fully functional reporting system.

Employ a Phased Approach

If your Crystal Reports to SSRS conversion appears evenly moderately complex, approach the migration in a phased approach including upfront detailed analysis to uncover unforeseen, time-consuming obstacles.

After initial testing, deliver reports to a select group of knowledgeable users for beta testing before widespread deployment. Reduce inevitable end-user issues by documenting thoroughly changes in appearance or usage.


Migrating from Crystal Reports to SSRS provides an ideal opportunity to evaluate and streamline your entire reporting solution to ensure BI decision makers are getting the information they require in the most effective manner. SSRS will continue to offer more advanced features and grow with your business needs due to its superior scalability.

You will likely find that automated migration presents as many problems as it solves and that using an experienced conversion partner to implement a detailed manual conversion process pays the highest dividends.

SSRS Best Practices

Installing, configuring and running SQL Server Reporting Services is a non-trivial set of tasks. To successfully perform these, a company needs people with skills related to databases, system administration, report creation and data analysis. Most initial deployments and their usage can take advantage of several best practices in order to improve performance, maintenance and the production of quality reports.

Deployment Tips

Safeguard the Encryption Key

A number of items such as passwords and connection strings are encrypted within SSRS and stored within the Report Server database or configuration files. Many an installer has forgotten to back up the encryption key and later regretted it. Keep a copy of the key in a password-protected file and place copies in both the SSRS server and another safe spot outside the server. When migrating SSRS to another server, restoring this key will avoid having to re-create encrypted items such as your data sources.

Simplify Security Control

Sysadmins should be in the habit of granting access privileges by Active Directory groups rather than to individuals. Furthermore, reports themselves should be categorized into folders, and access privileges should be assigned per folder. Hierarchical access can be created via subfolder privilege cascading.

Use SQL Authentication in Lieu of Windows Authentication

Windows Authentication for connecting to report data sources is only reliable if the SQL Server database and the SSRS server are on the same machine, which often presents an inconvenience to users trying to access remote machines. To enable access from another machine, use SQL Authentication instead or supply account credentials that work on the remote machine.

Performance Improvement Tips

Optimize SELECT Statements

A bit of fine-tuning of SELECT statements goes a long way to improving SSRS performance by reducing the amount of data fetched to only that which is necessary. Get in the habit of specifying only the column names relevant to your query instead of using SELECT * statements.

Profile Slow Queries

Take advantage of the GUI-based SQL Server Profiler to analyze long-running queries and stored procedures to see if you can wring out even better performance. Note that the profiler will be deprecated in later releases of SQL Server except for Analysis Services workloads.

Use WHERE to Filter at the Database

Learn to use the WHERE clause in your queries for data filtering whenever possible. It is far more efficient for data filtering to take place at the database level and not at the report level. Filtering at the report level should only be used when multiple report items, such as matrices, lists and charts, are sharing a single dataset and each requires different methods of filtering.

Maintenance Tips

Shared Data Sources

Expert programmers know the old maxim that any problem can be solved by introducing another level of indirection. This is important in SSRS reports as well. For example, if at least several of your reports use a common data source connection, do not refer to this connection directly within each report. Instead, set up a project-level Shared Data Source and refer to that within reports. If the source of report data changes at a later time, you only have to modify it in one place for all reports to pick up the new data source.

Maintaining SSRS Report Server Catalog

Within the SSRS software architecture is an SSRS report server catalog, which caches data used to serve up previous reports without re-creating them afresh from the SQL Server database. The catalog tables should be regularly re-indexed and database statistics updated to improve query performance.

Report Design Tips

Improving Readability

There are several ways in which you can make SSRS reports more accessible to end users, such as maintaining uniformity of the font type and its size throughout the report, avoiding too many colors, saving space by proper textbox orientation and using alternating row colors. If the report is exported into multiple formats, always perform each export and check the output for readability.

Improving Content Quality

For reports that require the most up-to-date information, prominently display the time and date of the last refresh. This practice helps end users evaluate the report’s relevance. Also, display the data filter parameters, which assists readers’ report comprehension. When presenting visuals, such as pie charts and bar graphs, avoid using too many data categories as the smaller ones will clump together. If necessary, break out the smaller categories into a secondary visual.


SQL Server Reporting Services is a key component for modern enterprise Business Intelligence solutions. It provides a huge array of features essential to extracting and presenting the most relevant data to business decision makers. As such, its complexity requires careful deployment and optimization to achieve the best performance possible and high quality, informative reports for its end users. The tips presented here will help your organization utilize SSRS to best advantage.

Report Development in SSRS vs. X++ for Dynamics AX 2012

Last week’s discussion with one of our Business Intelligence clients, prompted me to document the following questions and responses for the benefit of many organizations who fall in to the following classes:

  • Companies planning to implement Microsoft Dynamics AX 2012.
  • Companies planning to develop, modify or migrate reports into Microsoft Dynamics AX 2012.
  • Companies wanting to leverage SharePoint infrastructure that they have in place.

Why is developing reports in SSRS better than developing/modifying reports in the current format (using X++ in AOT)?

The main advantages of developing reports in SSRS are:

  • Report performance is better in SSRS than in X++.
  • Future change requests and report development is less expensive on SSRS than X++. So it is better to transition to SSRS now.
  • SSRS reports can be published to SharePoint that can be used by users without AX 2012 licenses saving on license costs.

Will these SSRS reports be available inside AX?

The SSRS reports will be available inside specific AX modules along with other out-of-box reports just like with the old X++ reports.

Is it possible to add report links to other ERP systems into AX? Can users access these reports without navigating out from AX.

The answer depends on the ERP system, but generally, yes.

Reconciliation reports, which take data from more than one application, can be embedded into the AX Module and can be accessed by the users in the same manner as other SSRS reports. This is possible as the report connects with the databases of the various applications. If the data in the other application database is correct then the report will show the correct information.

Is it possible to publish the same report in SharePoint so that users who do not have access to AX still run the report?

A report copy can be published to SharePoint, but there will be 2 copies of the .rdl report file. The same file cannot be configured to run in AX and SharePoint as they both have different report servers.

Is it possible to provide specific access of the SSRS reports published in AX to end users?

Yes, users can get specific access to SSRS reports. Depending on the access to the specific module in AX, users can view and run the report(s) published in that module.

We would be happy to sit with you to demonstrate our capabilities and identify where you can save significant costs in the reports development projects. Feel free to contact us.

The post Report Development in SSRS vs. X++ for Dynamics AX 2012 appeared first on OptimusBI.

PowerPivot for Excel Sample

One of our clients needed a single report that served the needs of several different users each with slightly different requirements. In addition to satisfying different needs, the report also needed to select and filter on different columns. Finally, the report had to be fast.

We proposed two solutions, a robust and extensible data mart and data cube, and a quick and dirty solution using PowerPivot for Excel which we will cover here.

The end-users analyze the data using Microsoft PowerPivot extension of Microsoft Excel. The extension allows for drag and drop pivot table analysis very similar to the data mart and OLAP data cube.

However, the variety of the attributes that can be used for analysis is strictly limited to the fields present in the relevant Crystal Report.

This solution entails creating a report on Microsoft Reporting Services that reuses the stored procedure that the Crystal Report is using. The report will refresh regularly and provide a data source for PowerPivot.

The processing and analysis of the data happens on end-user workstations within the PowerPivot extension of Excel.

The features of this solution are:

  1. The solution would, unfortunately, not work for large datasets as there will be performance issues associated with reading and processing large amounts of data on end-user workstations.
  2. Excel 2010 is required with a free PowerPivot extension installed. This is in contrast to the data mart solution that works with Excel 2007 and higher.
  3. The solution can’t be easily extended or reused to cover slicing and dicing along new dimensions, attributes or measures in the subdomain.
  4. Extending the solution to a wider variety of dimensions or measure may result in more effort than is required for the data mart and data cube solution
  5. The solution is prone to introducing inconsistencies. Even when extreme care is taken in extending the solution, it is difficult to maintain complete consistency between many report data sources.

PowerPivot Architecture

SSRS reuses queries to gather data in to Excel where it can be moulded in to a usable report.

powerpivot-architecture PowerPivot for Excel Sample

PowerPivot Schema

The PowerPivot data reuses existing report queries limiting the ways that you can pivot the data but still providing useful options.

powerpivot-schema PowerPivot for Excel Sample

Output Samples of PowerPivot for Excel

sample-outputs PowerPivot for Excel Sample

sample-reports PowerPivot for Excel Sample

The post PowerPivot for Excel Sample appeared first on OptimusBI.

Evaluating SSRS Report Developers for Continuous Improvement

SSRS-reporting Evaluating SSRS Report Developers for Continuous Improvement

We have a standard set of criteria that we use after any SSRS project to help our SSRS report developers evaluate each other and improve on their work.

The evaluation criteria are:

  • Always keep your audience in mind.
  • Thoroughly capture report requirements before writing queries.
  • Make upfront effort to reuse design.
  • Use and reuse datasets.

Keep your Audience in Mind

Always keep the audience or end user of the report in mind. If the end user is an accounting analyst, then the report will be multi-page and have a very detailed view of the transactions, such as line items of each transaction. If the end user is a CFO or senior management, then the report will be most likely single page.

If the audience is not kept in mind, then the report will be ignored by the end users. Including the right the level of detail in your report is a key.

Thoroughly Capture Report Requirements before Writing Queries

The challenge faced by many report development teams is they do not get the requirements in enough detail at the start of the project.

Report developers who dive right in to developing reports after receiving the initial requirements will often discover that the requirements didn’t fully capture what the end user really wanted. The result is that the developer puts in a full round of development before properly capturing the requirements and has to revisit development.

The key to solving this problem is to spend more time capturing report design requirements. Prepare mock-ups using tools like Excel before implementing the design. Get the template approved and then spend time on the query writing required to power the reports. Putting in extra effort at the start will save a lot of time and cost of report development over the long run.

Upfront Effort to Reuse the Design

Developers should always keep reusability in mind when designing reports.

Large reporting projects always present opportunities to reuse elements like templates and functions. The key is to think about reusability from the beginning.

Use and Reuse Datasets

SSRS provides ability to create and reuse datasets.

The advantage of reusing datasets is that they save time and also let you update the query in one place regardless of how often it appears in different reports across the organization. The plug and play functionality of SSRS not only saves time and cost up front, but also reduces the cost of future updates.


By keeping these principles in mind, we find that we are able to continually improve on our ability to deliver useful SSRS reports on time and under budget.

We have many such checklists and best practices, a few of them are:

The post Evaluating SSRS Report Developers for Continuous Improvement appeared first on OptimusBI.

Integrated and Native Mode SSRS Performance Comparison

In my previous blog post I shared integration benefits of SSRS in SQL Server 2012 and SharePoint. In response to the post, a reader asked about the performance of reporting services in SharePoint Integrated mode on SQL Server 2008 R2 compared with Native mode.

SharePoint Integration Mode with SSRS in SQL Server 2008 R2

Before looking at SSRS performance in Native and Integration modes, we should look at the checkered performance history of SSRS-SharePoint integrations.

The integration of the SharePoint environment with SQL Server Reporting Services (SSRS) provides many benefits such as shared security, shared storage, well-defined workflows and one-stop shop for all documents in an organizations. However, the price of this integration is paid in additional performance overhead.

Since the initial release of the integration, about seven to eight years ago, Microsoft has made significant performance improvements. The SQL Server 2008 R2 and later version with SharePoint 2010 have much better performance than older versions with better individual report performance as well as improved overall operations per seconds (throughput).

The performance hit on older versions can often be seen when running a complex SSRS report, drill-down or drill-through reports or any report with .NET coding, in parallel on SharePoint 2010 or older.

SQL Server 2008 R2 has a significant performance advantages over older versions of SQL Server. Newer versions of SQL Server are pretty close performance-wise the main difference being features like those highlighted in my last blog post.

SSRS Performance in Native and Integration Mode

To begin with, let me briefly touch on process in both.

SSRS in Native Mode:


SSRS-native-mode Integrated and Native Mode SSRS Performance Comparison


  1. End user requests a report to a Reporting Service Engine.
  2. Reporting Service Engine retrieves report definition from the Report Server Catalogue.
  3. Reporting Service Engine sends query requests to the database(s).
  4. Reporting Service Engine sends the extracted report to the end user.
  5. End user sends request to Reporting Service Engine to format output (Excel, PDF etc..).
  6. Reporting Service Engine returns the report in the end user’s requested format.

SSRS in Integration Mode:


SSRS-integration-mode Integrated and Native Mode SSRS Performance Comparison


  1. End user requests a report to SharePoint Web Services.
  2. SharePoint Web Services retrieves the report definition from the SharePoint Catalogue.
  3. SharePoint Web Services requests a report from the Reporting Service Engine.
  4. Reporting Service Engine queries Report Server Catalogue to compare and authenticate report definition with the report definition received from the SharePoint Catalogue.
  5. Reporting Service Engine sends query requests to the database(s).
  6. Reporting Service Engine sends the extracted report to the SharePoint Web Services.
  7. SharePoint Web Services sends the extracted report to the end user.
  8. End user sends request to SharePoint Web Services to format output (Excel, PDF etc…).
  9. SharePoint Web Services requests Reporting Service Engine to send report in the end user’s requested format.
  10. Reporting Service Engine returns the report in the end user’s requested format to SharePoint Web Services.
  11. SharePoint Web Services returns the report in the end user’s requested format to the end user.

We see that communication in SSRS in Integration Mode takes more steps than in SSRS in Native Mode and this is reflected in performance. However, the difference is microseconds.

The choice between Native and Integration mode really depends on the organization as performance isn’t much of an issue. If they prefer the SSRS in Integration Mode with improved performance, they can add more computing resources to the infrastructure.

The post Integrated and Native Mode SSRS Performance Comparison appeared first on OptimusBI.

New SSRS-SharePoint Integration Features

ssrs-orbit-sharepoint New SSRS-SharePoint Integration Features

An interesting question was asked by a prospect in the recent meeting, “What does SQL Server Reporting Services 2012 bring?” To bring some context, my prospect wants to develop some SSRS reports for their financial ERP and would like to use SharePoint as a collaboration portal.

There are many changes to SQL Server Reporting Services 2012 SharePoint integrations. The prime difference between SQL Server 2008 R2 and 2012 is that the latter is now a Windows Service Application which helps take advantage of a number of SharePoint features while providing more flexibility in deployment.

Leveraging SharePoint’s load balancing feature: SSRS now can leverage SharePoint’s load balancing features without much overhead and configuration. Fault tolerance and load balancing infrastructure are the part of SharePoint feature set for Service Applications.

Shared Services: The new architecture helps in scalability of reporting services. Reporting services can switch between running on a single server and on multiple servers depending on the load. The sharing of resources across servers become seamless with Service Applications helping with scaling.

Flexible Deployment: As a Service Application, the new version of SSRS allows for more flexible  deployment. Depending on the resources and scenario, SSRS can run either on a frontend web server or in a separate application server.

Administration Made Easy: The administration of Service Applications is integrated directly with the SharePoint architecture streamlining administration.

For more information on integrating SSRS and SharePoint,

  • watch Microsoft’s video on Configuring and Securing Complex BI Applications in a SharePoint 2010 Environment with Microsoft SQL Server 2012
  • or contact OptimusBI to see how we can help you with your setup.



The post New SSRS-SharePoint Integration Features appeared first on OptimusBI.