Gartner Says BI is the Focus of Companies: Some Strategy Tips

Gartner research predicts that business intelligence and analytics need to scale up to support explosive data sources.

Many companies are developing strategies to take complete advantages of their business intelligence initiatives and data trying to extract the maximum value at the minimum risk.

The main values that these companies are trying to extract are as follows:

  • Improved decision making throughout the organizations
  • Quick decision making
  • Scalable solutions
  • Low operational cost
  • Less development time of the solution
  • Easy to maintain
  • Easy to use

In order to have a successful business intelligence strategy, organizations should have to encourage processes, people and technology to work in an integrated mode. The factors that need to be addressed in BI strategy and planning include the following:

We have helped our customers with business intelligence initiatives. We would be happy to share our experience and answer any questions you may have. Please feel free to contact us.

The post Gartner Says BI is the Focus of Companies: Some Strategy Tips appeared first on OptimusBI.

5 Steps to Better Self-Serve BI Reporting

Companies seeking true value from their data, focus on the users of the reports. This is even more important these days as companies move to self-serve BI. I’ve spoken with a number of organizations that think they could be getting more from their self-serve BI.

If you are embarking on a self-serve BI project, or you are concerned that you could be getting more from self-serve BI, then these are important user-centered points to keep in mind.

  1. Understand the needs of self-serve BI end-users.
  2. Ensure the data is correct.
  3. Train end-users to use the tools.
  4. Prioritise your reports.
  5. Give detailed requirements.
  6. Validate your reports against usage scenarios.

A brief summary on each of the above points is as follows:

  1. Understand the needs of self-serve BI end-users

    Before starting any self-serve BI initiative, you should understand the needs of end-users. Senior management, business unit heads and any other stakeholders should be involved in determining the needs.

    Early involvement will help eliminate redundant reporting. This also provides an opportunity to map out the business units who can be satisfied with single report like Employee Performance Reports that can be shared with the Department Managers and HR.

  2. Ensure the data is correct

    Big companies accumulate data sources and data can very quickly get out of control.

    Data sources often include internal applications, external feeds and various software as a service applications. Having a single version of correct data is a must.

    This can be achieved by developing a data warehouse or a central database. Having correct data in one place is a key first step to giving your organization the tools to make good decisions.

  3. Train end-users to use the tools

    Training should be provided to the end users on the self-serve BI tool. This will help them understand the capabilities of the reports. Training is critical as end users may end up creating ineffective reports.

  4. Prioritise your reports

    Management should sit down together and decide which reports need to be made and order the reports by priority. The reports will provide maximum value as the highest priority reports get developed and deployed first.

    This will help prevent end users from getting overwhelmed by a large number of new reports all at once giving them time adapt to the tool.

  5. Give detailed requirements

    Detailed requirements are necessary in the report development process. This step requires effort from both the business analyst and the end user. Having detailed white boarding session(s) will reduce significant amount of time in the iterations after the report is developed.

  6. Validate your reports against usage scenarios

    Test cases from the end users help significantly in the successful development of the report. End users should provide test cases comprising of all possible scenarios. This also helps in combining business logic with the application logic and result in correct query writing.

We have worked on many self-serve BI reporting projects. Contact us and we’ll be happy to share our experience with you and address any questions you may have.

The post 5 Steps to Better Self-Serve BI Reporting appeared first on OptimusBI.

Report Parameter Selection: The Importance of Talking to End Users

I spend a lot of time speaking with the various end users of reports on Microsoft Dynamics AX and other platforms when gathering requirements ahead of report development. Selecting which report paramaters to use is an important part of any report development project, but it is also important to check whether a particular  parameter is a need or want when speaking with the end users.

In the report development process, requirements gathering is an important stage that typically includes the following.

  • Brainstorming sessions with the end users.
  • Designing report mock-ups.
  • Capturing important features of report such as fonts, sizes, colour and images.
  • Capturing report logic.
  • Defining input parameters.

Defining Input Parameters

When defining input parameters, it is important to check with end users to see which input parameters are actually needed in the report.

Here are some questions that will help in identifying the parameter requirements:

  • What is the expected performance of the report?
  • Do you need data re-entry minimized?
  • Can the reports use record limits filtering out the data that is not required?
  • Do you need browser view?
  • Do you need direct print?
  • How many clicks are required before the report query is submitted to fetch the output?
  • How many parameters are cascaded to limit the selection values in the other parameters?


If just one parameter, like Start Date,  is used, the query will fetch only records from a single table. The query should run quite fast and report performance will be good.

By using SSRS and other similar technologies for reporting, printing reports can be handled by the report generation software making any printing-related parameters unnecessary which gives you another opportunity for optimization.

Cascading parameters can make reports more usable. Take for example a Customer Account parameter. Even a small business will have hundreds or thousands of  Customer Accounts. Scrolling through a list of accounts isn’t practical and can be quite frustrating.

Cascade the Customer Account parameter under Division or Region or some other similar parameter and suddenly the list becomes much more manageable.

If you want to talk about more specific ways of validating parameters for your own particular situation, please feel to contact us.

The post Report Parameter Selection: The Importance of Talking to End Users 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.

Data Mart and OLAP Data Cube 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.

One of the solutions we proposed was a data mart and data cube to represent order, price and sales customer care data.

Data marts store subsets of data for access. OLAP data cubes are representations of multidimensional datasets. They combination provides a robust and extensible view of data that is well suited for self-serve BI.

The users simply connect to the cube using MS Excel and utilize its powerful, yet easy to use drag and drop user interface to analyze the data. The MS Excel user interface for data analysis is very intuitive and responsive.

The features of combining a data mart and OLAP data cube are

  1. The solution can replace a fair number of the reports related to orders, prices and revenues and customer care with simple Excel spreadsheets. It should be able to cover dozens of reports that would otherwise have to be re-developed one-by one.
  2. The solution provides a solid base for extending the data mart to subdomains other than orders, prices and sales customer care.
  3. The solution gives end users access to high-performance ad-hoc analysis even over fairly large datasets.

High Level Architecture Diagram

This particular client only needed ad-hoc reporting using data out of the ERP system, so the data mart would provide access to the report data for the OLAP data cube which is then queried by SSRS for static reports and Excel for ad-hoc reports.

high-level-architecture-diagram Data Mart and OLAP Data Cube Sample

High Level Data Mart Schema

A sample high-level data mart schema for retail includes Customer, Date, Order Status, Item and Supplier dimensions that relate to Revenue and Inventory metrics.

high-level-data-mart-schema Data Mart and OLAP Data Cube Sample

This particular data mart is fairly simple consisting of 10-12 dimensions with two to three facts each which can contain many attributes or measures.

Some things to consider for your data mart that can increase the complexity beyond just representing more data include the following.

  1. Security: Role-based and other advanced security measures.
  2. Number of records: More records can slow down queries excessively and overload the SQL server when refreshing the data.
  3. Downtime: It’s a lot easier and cheaper to refresh the data overnight with some downtime.
  4. Logging: logging report queries and other data requests adds to the storage overhead.
  5. Data source: ERP systems are usually well documented, but legacy systems could involve a lot of work.
  6. Data complexity: More complex data relationships are more computationally expensive.

Output Samples of Self Serve BI

self-serve-bi-output Data Mart and OLAP Data Cube Sample

more-self-serve-bi-output Data Mart and OLAP Data Cube Sample

The post Data Mart and OLAP Data Cube Sample appeared first on OptimusBI.

How to Migrate from MySQL to Microsoft SQL Server

How to migrate from MySQL to Microsoft SQL Server 2005 and later using the MySQL ODBC Connector 5.1. After following these steps you should have a MySQL database fully migrated in to your Microsoft SQL Server environment.

ODBC Connection Setup to MySQL Database

This article will assume that user have already downloaded and installed the MySQL ODBC Connector.

These instructions use MySQL ODBC Connector 5.1.

The setup of the MySQL ODBC Connector is quite simple:

  1. Go to Control Panel -> Administrative Tools-> Data Sources (ODBC). Under the tab labeled as System DSN, press the Add button.odbc-data-source1 How to Migrate from MySQL to Microsoft SQL Server
  2. From the dialog box named Create New Data Source, select MySQL ODBC 5.1 Driver and then click on the Finish How to Migrate from MySQL to Microsoft SQL Server
  3. In the MySQL connector/ODBC Data Source Configuration dialog box, add your connection parameters for the MySQL database account that you are migrating including a user name which has full access to your databases in MySQL preferably the “root” account. Do not change the port to anything other than 3306, unless during your MySQL server installation, you have defined something else. This is default port number for MySQL database. In the server option you can also type remote server host name in case your MySQL server is remotely located.mysql-connector1 How to Migrate from MySQL to Microsoft SQL Server
  4. You can check that you have the right connection settings by pressing the Test button and then the OK button.

Query to link Microsoft SQL Server to MySQL Database (Linked Server)

After completing above steps open a query window in MS SQL and run the following SQL statement:

EXEC master.dbo.sp_addlinkedserver
@server = N’MYSQL’,
@provstr=N’DRIVER= {MySQL ODBC 5.1 Driver}; SERVER=mysql-server-name; DATABASE=db-name; USER=username; PASSWORD=password; OPTION=3′

Be sure to replace mysql-server-name, db-name, username and password in the above query.

This script will produce a link to your MySQL database through the ODBC connection. You can check this linking in the Microsoft SQL Server Management Studio Object Explorer under Server Objects -> Linked Servers -> Providers.

sql-server-management How to Migrate from MySQL to Microsoft SQL Server


Data Migration between the Databases

Create a new database in Microsoft SQL Server, for instance “testMySQL”. Now run the following SQL statement to migrate/import table “users” from the MySQL database db-name, into the newly created
database in Microsoft SQL “testMySQL”.

SELECT * INTO testMySQL.dbo.users
FROM openquery(MYSQL, ‘SELECT * FROM db-name.users’)

As with the previous query, be sure to replace db-name and testMySQL as appropriate.

That’s it. Your data is now in your Microsoft SQL Server.

Please post any queries or questions in the comments section below.

The post How to Migrate from MySQL to Microsoft SQL Server 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.

Retail BI: What to Measure

Retail_BI-Post-300x140 Retail BI: What to Measure

Following from my earlier blogpost on retail industry BI dashboards, I wanted to share some thoughts on what are typical reports or dashboards that are quite common in the Retail Industry.

“Customer is the king” in the retail industry. The retail industry is looking for customer data and how they can quickly react to the changing customer demands. Business Intelligence on the customer data can help retail industry in:

  • Improving customer retention
  • Exceeding customer expectations
  • Removing wasteful spending by target marketing
  • Reducing efforts to predict customer demands
  • In order to be successful in a retail chain, companies look for 3 main data sources:
  • Customers
  • Financials
  • Stores

Understanding the Customer

This key segment of the retail industry helps in strategic campaigns and relationship building. Certain data types like average sales per transaction, total number of customer trips per month, visit to buy ratio etc. are helpful in understanding the customer requirements and reacting to the fluctuating market conditions. Therefore, following Business Intelligence reports will be helpful for a retail client to learn more about the customers

  • Average order size per transaction
  • Average number of items per transaction
  • Average profit per transaction

Tracking the Financials

The strength of the business is measured by the finances. The retail industry combines the customer data during the analysis of the financial data. For example, understanding the products demand is analysed by the number of customers buying the product in a given month and the profit that is generated by selling of the product. This will help in identifying the opportunities. The following key reports or dashboard segments are used by the Retail industry:

  • Product and Profit Report over time
  • Top 5 products of the month in revenue

Success of Stores

This is another segment of monitoring and measuring the day to day operations of stores. This helps in identifying the management efficiency and deficiency in the marketing. Additionally, this also reflects on which stores are providing better results with the relative marketing efforts. Some of the important reports in this section are:

  • Daily/Monthly Cost breakdown (Fixed and Variable)
  • Products Report (Shelf Life, % of damaged products, % of returned products)

We have experience in writing various type of reports and designing dashboards for retail clients. Feel free to contact us, we are always happy to help with your queries.

The post Retail BI: What to Measure appeared first on OptimusBI.

Indicators for Your Dashboards: Surfacing Data to be Noticed

Business Intelligence dashboards are important tools for understanding the organization’s growth. Insightful dashboards share certain attributes:

dashboard-300x246 Indicators for Your Dashboards: Surfacing Data to be Noticed
  • It should have the right intensity of relevant information
  • It should have right colours
  • It should have appropriate and approved metrics to be visualized
These attributes have fall in to two basic categories:
  1. Getting the right data on your dashboard
  2. Making sure that the right data gets noticed.
It is the second point here that we are concerned with and good dashboard indicators are critical to getting the right data noticed.

Intensity of Relevant Information

The volume of information displayed in the dashboards has great importance.

Some end users chose to have dashboards filled up with as much information as one can (in one screen). On the other hand, others choose to have informative dashboard with data as minimal as possible.

However, the information should be organized in such a way that it support exploration and analysis. Smart tools should be selected that can help interact with the information and convey meaningful information in a quick glance..

Right Colours

The right colour will make particular items stand out from the rest, while linking related contents.

It is important to limit the use of colours to only when required, even for highlighting essential areas of the dashboards. An example would be: use of red colour to indicate both negative balances and over budget figures.

Always ensure that colour selections contribute to attractive and readable data presented in text or in chart format.

Appropriate and Approved Metrics

Metrics should be consulted with the specific user groups and should be approved.

Dashboards are no longer exclusively for executive management. All users need access to information so they can make sound decisions. Important steps to capture such requirements are covered in my blog post on capturing dashboard requirements.

The dashboards should summarize all major segments of measurements.

If you want to learn more about dashboards or OptimusBI,


The post Indicators for Your Dashboards: Surfacing Data to be Noticed appeared first on OptimusBI.

How to create subscription for SSRS reports

report-dropdown How to create subscription for SSRS reportsReport subscriptions in SQL Reporting Services enable you to configure the automatic delivery of specified reports by e-mail or to a file share at scheduled intervals. You can also configure other types of report subscriptions, such as data-driven subscriptions from the Web interface included with Microsoft SQL Server Reporting Services.

NOTE: Data-driven subscription functionality is not available in all editions (i.e Standard or Express edition). There are also limitations on the kinds of data sources that you can use to retrieve subscription data at run time.


Here we are going to subscribe a report which has already been deployed on the Report Server. Report server is present on our SharePoint 2010 site.


  • SQL Server 2008 R2 (Enterprise Edition)
  • Reporting Server
  • Share Point 2010
  • SSRS Report (developed using Report builder 3.0, for which we have to create a subscription)


You can subscribe to an SSRS Report using email or Windows File Share, we are going to look the subscription of the SSRS report using email.


  1. On the report server, navigate to your SharePoint site URL eg: http://sharepoint-ser/Reports.aspx where you will see a list of reports.
  2. In the options list that that appears when you mouse over on the desired report, select Manage Subscription.
    report-dropdown-manage-subscriptions How to create subscription for SSRS reports
  3. On the new page , select Add Subscription.
    add-subscription-1024x172 How to create subscription for SSRS reports
  4. On the New Subscription page in the Delivery Extension dropdown, select E-mail.
    delivery-extension-dropdown How to create subscription for SSRS reports

    • Windows File Share: expoerts the report to a network drive
    • Email: sends to your email address in Delivery Options
    • SharePoint Library:  exports the report to a location on your SharePoint Portal.
  5. In the Delivery Options area, fill out the desired settings.
    delivery-options How to create subscription for SSRS reports

    • To: destination email address of the report
    • Subject: title of the email
    • Comment: free text field you can use to customize the email message.
    • Include a link to the report:  check this to include a direct link to the report.
  6. In the Report Content area, select the desired settings.
    report-content How to create subscription for SSRS reports

    • Use the check box to show report inside message
    • Use the dropdown to add the report as an attachment and select a format.
  7. In the Delivery Events area, click Configureto set the desired report subscription scheduling settings.
  8. In the Schedule Reports dialog, set the desired settings and click OK.
    schedule-reports How to create subscription for SSRS reports
  9. In the Parameters dialog, enter the desired settings and click OK.
    report-subscription-parameters How to create subscription for SSRS reports

You have successfully created the subscription for the SSRS report.

Your Subscription will already trigger at the specified schedule.

If you want to learn more about how OptimusBI can help you with SSRS or any other BI project,

The post How to create subscription for SSRS reports appeared first on OptimusBI.