Perficient Business Intelligence Solutions Blog

Blog Categories

Subscribe via Email

Subscribe to RSS feed

Archive for the ‘Microsoft’ Category

“Accelerate your Insights” – Indeed!

I have to say, I was very excited today as I listened to Satya Nadella describe the capabilities of the new SQL 2014 Data Platform during the Accelerate your Insights event. My excitement wasn’t tweaked by the mechanical wizardry of working with a new DB platform, nor was it driven by a need to be the first to add another version label to my resume. Considering that I manage a national Business Intelligence practice, my excitement was fueled by seeing Microsoft’s dedication to providing a truly ubiquitous analytic platform that addresses the rapidly changing needs of the clients I interact with on a daily basis.

If you’ve followed the BI/DW space for any length of time you’re surely familiar with the explosion of data, the need for self-service analytics and perhaps even the power of in-memory computing models. You probably also know that the Microsoft BI platform has several new tools (e.g. PowerPivot, Power View, etc.) that run inside of Excel while leveraging the latest in in-memory technology.

PeopleDataAnalytics But… to be able to expand your analysis into the Internet of Things (IoT) with a new Azure Intelligent Systems Service and apply new advanced algorithms all while empowering your ‘data culture’ through new hybrid architectures…, that was news to me!

OK, to be fair, part of that last paragraph wasn’t announced during the key note, it came from meetings I attended earlier this week and that I’m not at liberty to discuss, but suffice it to say, I see the vision!

What is the vision? The vision is that every company should consider what their Data Dividend is.

Diagram: Microsoft Data Dividend Formula

Why am I so happy to see this vision stated the way it is? Because for years I’ve evangelized to my clients to think of their data as a ‘strategic asset’. And like any asset, if given the proper care and feeding, you should expect a return on it! Holy cow and hallelujah, someone is singing my song!! :-)

What does this vision mean for our clients? From a technical standpoint it means the traditional DW, although still useful, is an antiquated model. It means hybrid architectures are our future. It means the modern DW may not be recognizable to those slow to adopt.

From a business standpoint it means that we are one step closer to being constrained only by our imaginations on what we can analyze and how we’ll do it. It means we are one step closer to incorporating ambient intelligence into our analytical platforms.

So, in future posts and an upcoming webinar on the modern DW, let’s imagine…

Even Row Distribution on SSRS report

SSRS report tends to hold maximum number of rows it can fit on a page.  The number of rows varies according to the page size, row width, location of the table, etc.  My report contains 99 records and these records are unevenly distributed throughout the report.  The report I created holds 36 rows on page 1, 38 on page 2 and remaining on page 3.  Today, we will learn to evenly distribute rows across several pages of the report.

I have created a simple table for illustration.  This table contains 3 fields:

1.)   ID

2.)   Product_type

3.)   Product_detail

I am querying all the records from my table and displaying the result in my SSRS table report.  For those interested, here is the SQL query:





My requirement is that I want to display 10 rows per page.  This means I will have 10 rows on page 1 to 9 and the remaining 9 rows on page 10.

To do that, I will have to make groups of 10 rows and display these groups on different pages.  It seems complicated but it really isn’t.

The first thing to do is right click on Details and click on Add Group and select Parent Group.











You will see a pop up window.  Instead of choosing the options from drop down, click on fx.  fx is a button to enter mathematical formula.  This button is also found in Microsoft Excel.

Read the rest of this post »

Sales Report with Date Picker

Sales Report with Date Picker.

Last week we created a generic sales report in SSRS.  This week we will customize that report and display the numbers only for specific dates.  Such reports are called Parameterized reports and are user friendly because the user can choose a date range for his/her reports.

So, let us begin!

Step 1: Let us modify the SQL query we wrote last week.  Because we want to allow the user to choose a date range we will include a where clause.  Pay special attention to Where clause.






Where [candle_soap_shop$].Sold between @StartDate AND @EndDate

group by [candle_soap_shop$].Product_Type


order by count(*) desc


Let us run the report and see what we have.











You see two text boxes – one called the Start Date and the other End Date.  Both text boxes are very descriptive.  However, the user will have to enter the date every time he/she wants data.  Another thing to remember is that different countries use different date formats.  For example: United States uses mm/dd/yyyy format and India uses dd/mm/yyyy.  Wouldn’t it be just more convenient if we gave the user a date picker to choose a start and an end date.

Let us go back to the home page by clicking on Design button on Top Left.

Step 2: Click on + sign to the left of Parameters to expand.

Step 3: Right click on StartDate and select Parameter Properties.











Step 4: Because we know that StartDate is a date, we will select Date/Time from the drop down and click OK.












After you have finished reading this article and creating this report, I would advise you to try out different types of parameters and see how the report looks.

Step 5: Follow steps 3 and 4 for EndDate.

Finally, run the report and verify whether date picker is now available.











Stay tuned for more articles!!









Sales Report in SSRS

I hope everyone has found my articles useful and have used my articles in creating some great looking SSRS reports.  This week we will take a look at creating a product breakdown report or a detailed sales report.

Let us refresh our memory.  Our sales table has the following fields:

Fields Description Data Type
ID Identifier (Primary Key) Number
Product _Type Candles, hand sanitizers, perfumes, etc Varchar2
Product Detail Fragrance of the product such as Strawberry, Vanilla, Eucalyptus, etc Varchar2
In Store Timestamp of the product arrival in store Date
Sold Timestamp of product sold Date


Imagine a scenario in which a manager wants a count of every product sold by its product type.  We generate such a report by creating tabular reports and using SQL’s group by clause.

If you missed my article on creating tabular reports, don’t fret! This report will take you through the all the steps required to create a great looking sales report using tables in SSRS.  This article will be a great refresher course.

Read the rest of this post »

Pie Charts – Your First Visual SSRS Report

In my previous articles, we looked at all the basic concepts of creating a report in SSRS.  We built a simple tabular report and made it presentable.  However, simple tabular or matrix graphs are textual reports.   Today, we will build a visual report – a pie chart.  There are several visual reports one can build using report builder.

1.)    Column graphspie chart

2.)    Line graphs

3.)    Shape graphs

4.)    Bar graphs

5.)    Area graphs

6.)    Range graphs

7.)    Scatter graphs

8.)    Polar graphs

We will focus on building a Pie Chart report today.  Pie chart looks like a Pizza i.e a circle divided into smaller areas.  Every area represents a set of data belonging to a group.  This allows business managers to view the data as a proportion of the whole set of data.  Pie charts also allow business users and managers to compare one set of data with another set of data in one chart.

Let us dive into creating our first visual report – Pie Chart.

Before creating the Pie Chart, let us see how we can divide our data set into different categories.  We have primarily three types of products:

1.)    Candles

2.)    Hand sanitizer

3.)    Shower Gels.

Great – The next step is to create a datasource and a dataset.

Read the rest of this post »

SSRS – Making Reports Presentable

Now that we have created out first SSRS report, it is time to make our reports presentable.

SSRS is really user friendly and customizing a report is very simple.

This is how our reports currently look:


Let us first add a title to the report so that the users/managers know what information to look for in the report.

Add a Title:

1.)    Click in the area that says “Click to Add Title”

Read the rest of this post »

Creating First SSRS Report – Part 2

In this post, we are going to create our first report in SSRS.  Excited? Let’s get started.

In order to create a report, we need 2 things:

1.)    Data source: Here, you make connection to your database in Microsoft SQL server, Oracle, Teradata, etc.

2.)    Dataset: Here, you choose your table you want to create reports for.

Open Report Builder on your machine.  If it is already open, you should see something like this:

Adding DataSource:

1.)    Click on New and Select Data source:

2.)    Enter name for your datasource. (Optional)

Read the rest of this post »

Creating your First Report in SSRS – Part 1

I am splitting this article in 2 parts.  In the first part, I want to talk about all the tools you need to create your first report.  Before reading the 2nd part of this post, make sure you have:

1.)    Microsoft’s Business Intelligence Development Studio

2.)    Report Builder 3.0 (I am going to use Report Builder 2.0)

3.)    SQL Server or Oracle.

4.)    Revised SQL queries.

All the 4 items listed above are extremely important in creating your reports.  SSRS is a pretty user friendly tool and if an analyst can write SQL queries, he/she can start creating attractive SSRS reports in no time.

I am going to use the table given below in all my posts on SSRS.  We will be querying this table and creating different types of reports.

Consider this table to be a sales table for a store such as Bath and Body works.  This table has the following information:

Fields Description Data Type
ID Identifier (Primary Key) Number
Product _Type Candles, hand sanitizers, perfumes, etc Varchar2
Product Detail Fragrance of the product   such as Strawberry, Vanilla, Eucalyptus, etc Varchar2
In Store Timestamp of the product arrival in store Date
Sold Timestamp of product   sold Date


Please dedicate sometime in installing all the software listed above and creating SQL table on your machine before we dive into the second part of this article

Business Intelligence Tools and Agenda

Hello Readers and fellow Perficient bloggers,

This is my first official blog post on Business Intelligence forum.  Before I tell you what my blog is about and the agenda for upcoming blog posts, I would like to give you a quick introduction.

JuventusI am Sujay Nadkarni and I joined Perficient in March 2012 as a Business Consultant after completing my Master’s Degree in Information Systems from University of Texas, Dallas.  I am a Sun Certified Java Developer, Certified Scrum Master and have a certification in SAS.  Besides technology, I love soccer.  I am probably the biggest Juventus FC fan on this planet and my dream is to watch at least one Juventus home game.  I also love traveling and that is primarily the reason I joined Perficient (kidding).  Italy, Machu Pichu and Bora Bora are on the list of places I would love to visit before I die.  As a kid, I also acted in several Bollywood movies and Television series.

I am confident that everyone knows the importance of data in today’s world and the importance of presenting meaningful data to our clients.

Petabytes and Terabytes of data are collected every day from millions of users.  Check out this interesting link

It has become important to analyze this data and try to make sense out of it.  I intend to write several short posts on several business intelligence tools and I hope these posts will prove to be immensely helpful to consultants.

Read the rest of this post »

Why are BI Projects so difficult to implement? part 3

You can find the previous posts to this topic here: Part 1, Part 2, Part 2.5

So how do we reduce the amount of anxiety related to a BI project (thus making them less painful)? To start, the project team needs to be keenly aware of the following:

They must consistently over communicate that some process change will happen and what it is.

Why must they over communicate? It takes time for people to assimilate new ideas, and this is only after they actually start listening. A standard metric in television marketing states that a consumer must make 3 ‘mental connections’ to a TV ad before deciding if the product is relevant to them. Is your process change more interesting than that TV ad?

Ensure that everyone feels they are in this learning process together. In other words, don’t let some users put themselves on an island.

We’ve all been in a class at some point where someone started to fall behind and was too embarrassed to raise their hand, again. Don’t let your users fall behind.

Realize that the mechanical capabilities of moving through a new report or dashboard are not inherent in all users and may demand ‘more obvious’ training.

I just finished reading an interesting article about technology anxiety in an older workforce and the study cited interface design as a leading cause of anxiety; specifically the practice of designing an interface with a ‘layered menu’ system in which the user must remember that there are ‘invisible options’ and the sequence of actions to find them. Dashboards typically employ this functionality through the ‘right-click’ context menu.

I immediately thought of the introduction of the Microsoft ‘ribbon’ and if the ‘invisible option’ problem wasn’t a leading factor in that design change.

Finally, be prepared for the data anomaly effect.

I’ve written about this before but it remains to be relevant. Users of a new analytical platform need to be prepared for the fact that they will be faced with data anomalies. Some of these anomalies will turn out to be actual bugs, but some will not. Those that are not obvious bugs will require research. Research means delayed responses back to the business and delayed responses mean a frustrated business user, if the project team has not embraced the recommendation of over-communicating.

Over time, the number of bugs will decrease, but the number of research requests is likely to increase (as shown in the example below) especially as new users are rolled on.

Additionally, there are a series of events that can cause these numbers to fluctuate. Version releases are an obvious source for bugs, but what about a key team member leaving?

In the example below, during October of 2012, we see a spike in research requests but no spike in bugs and no additional users added. The only major event that occurred was a new member joining the team. We can infer that either the previous support person had direct lines of communication open to the users (very likely that hallway discussions answered some users questions) or that the new team member is recording ‘discussions’ differently. Regardless, this event may be a source of frustration to the business that has no direct tie to the functionality or stability of the system.

Production Support Analysis (Example)

In conclusion, there are a number of reasons why a BI project can be difficult to implement, but not all of them are related to ‘BI technology’ per se. Challenging fundamental truths, brain pain and good old fashioned learning anxiety play a big role in the perceived success of an implementation.