Perficient Enterprise Information Solutions Blog

Blog Categories

Subscribe via Email

Subscribe to RSS feed

Archive for the ‘Microsoft’ Category

Creating Table of contents for SSRS reports

Table of contents have always helped readers to navigate through thick volume of books. This feature can be extended to our users in SSRS to navigate through several pages of reports. Table of contents in SSRS is called the document map. A document map is a clickable table of contents that takes the user directly to that part of the report that he/she wants to see. For example: Consider a library with hundreds and thousands of books. Books are categorized into paperback and hardcover. Furthermore, these books are categorized into genres such as Fiction, murder mystery, biographies, etc. The document map will be particularly very helpful for a librarian, who wants to see a list of all hardcover fiction books.

Let’s see how a document is created and how the usability feature can be extended to our users.

For illustration, I have created a tabular report using a wizard. For those interested, this is how my table looks.

dm1

 

 

 

 

 

 

 

 

Product Types here are Candles,Hand Sanitizers and Soaps.

Product detail here is the type of fragrance and In Store is a date field to indicate when the product arrived in store.

When I run my report, I see that are 20 pages of data. Lets say I want to find in store data for fragrance type = “Mint”. I would have to find what product the fragrance belongs to and in doing so I may have to go through entire result set. Let’s create a document map and see how that can help us.

One thing that we know before creating a document map is that “Mint” is a product detail and therefore, we would need a document map on this field.

Go to your canvas and under Row Groups, click on Product_Detail. Go to Advanced tab and under Document map, select Product_Detail in the drop down.

dm2

 

 

 

 

 

 

 

 

 

 

dm3

 

 

 

 

 

 

 

 

 

 

 

 

Click on Ok and run the report.

Your report should like the screenshot given below. Clicking on any of the product type will take you to that data point.

dm4

 

 

 

 

 

 

 

 

Stay Tuned for more J

 

Evaluating In-Memory DBs

This month Oracle is releasing its new in-memory database.   Essentially, it is an option that leverages and extends the existing RDBMs code base.   Now with Microsoft’s recent entry all four the mega-vendors (IBM, SAP, Microsoft, and Oracle) have in-memory database products.

Evaluating In-Memory DB'sWhich one that is a best fit for a company will depend on a number of factors. If a company is happy with their present RDBMs vendor, then that standard should be evaluated first. However, if a company has more than one RDMBs vendor or if they are looking to make a switch, a more comparative evaluation is needed. In this case companies should evaluate:

  1. Maturity of the Offering. All the vendors products have different support “traditional” RDMS functionality like referential integrity, support for stored procedures, and online backups – to name a few.   Make sure you understand the vendor’s current and near-term support for features that you require.
  2. Performance. All IMDB vendors promise and from all accounts, deliver significantly increased performance.   However, the vendor’s ability to provide the level of desired performance on the company’s proposed query profile and the ability of the vendor’s technology to scale out should be evaluated. Compression and columnar storage will also affect performance, so understanding these features to support a company’s requirements is necessary.
  3. Sourcing of On-Disk Data. Probably the biggest difference in architecture and maturity between the vendors is their ability to source data from on disk storage systems, either files, traditional RDBMs, or Hadoop systems.
  4. Licensing & Cost Model. The costs associated with a licensing and implementing a technology need to be closely evaluated. How much training is required to develop a competency with a new technology? Is the licensing model favorable to how an enterprise uses/purchases licenses?

There are other evaluation areas, as well. For instance with SAP’s HANA offering has a robust BI metadata layer (think Business Objects Universe) that may be of value for a number of companies.

In-Memory Databases are changing and evolving quickly. So, make sure the appropriate due diligence is competed before investing in a selected technology.

More on the MDM platform…

Picking up from my earlier blog post, there are two kinds of MDM tool types, one targets specific domain (Customer and Product are the most common ones) and the others follow a multi-domain (Customer, Product, Location, Supplier etc. all in one) strategy. Most of the analysis I found are either for Customer Domain or Product Domain, which includes multi-domain types as well.

So to round-up the top list equitably, I looked at Gartner research as well, thanks to the vendors, most of the reports are in public domain. There is a report from Gartner which you can buy, if you need complete analysis and understanding. Not sure how one gets on the list of these research. But I am assuming, if the market share of a tool is big enough or the technology is way superior, the tool should have made the list. Just a disclaimer, my intention is not to write  research paper but just commentary and some observation.

I looked at 2009, 2011/12 and 2013 magic quadrants for Product and Customer MDM. We see few more companies and some missing ones. Going back to my Forrester slide from 2007 (See my earlier blog), gives us an idea of type of companies approaching MDM and then retreating.

Reading the market news, and from my client experience, most of the medium to large enterprises do fall within the list of vendors we are seeing here. But there are other vendors very much in the market. Also my feeling is that the traditional Data Management software vendors are gaining market share through consolidation and through improved product lines. I am sure market will continue to surprise with new products and services. Microsoft is still playing a low-key in MDM space. Robust MDM from Microsoft will be a game changer.

What is your observation? What is your experience?

customer_mdm

product_mdm

MDM Tool Vendor Landscape

My exposure to Master Data Management as a tool and all the surrounding process, organization and platforms dates back to 2005 in one form or another. MDM as a tool and its expected functionality are evolving constantly. I was curious to see what MDM tools and vendor landscape looked like in 2006 compared to MDM Tools as it stands in 2014. MDM market typically has been a fragmented market place with major market share (Over 50%) among the small vendors.

As with any new technology, start-ups go for the market share until the consolidation happens. So let’s look at the charts and see how the market place has changed. My quick observation is that the big companies with no core Data Management expertise vanished along with their MDM products. Some of the data rich companies stayed within that domain (D&B still has an MDM product).  So the large software vendors has secured their dominance in terms of product offering and market share, though a lot of small vendors are still in the market. My experience is that MDM is gravitating towards a tool with bells & whistles. But two major themes remain strong, MDM for specific Domain and  Multi-Domain MDM. I also find big vendors have multiple MDM products and they may consolidate those products. I got a kick out of seeing some of the familiar but non-existent companies. Enjoy!

MDM_tool_1  mdm_tool_3

Yarn – The Big Data Accelerator

Yarn….. Yes, Hadoop may be changing everything, but when Yarn was released, the change pedal has been pushed aggressively to the floor. Putting the technical details aside, the bottom-line is that now multiple concurrent workloads can be executed and managed on Hadoop clusters. This “pluggable” service layer has separated the data processing and cluster resource management layer. Result is that we are not dependent on MapReduce to access and process HDFS data.

Yarn - the Big Data AcceleratorMost companies with products accessing HDFS data are doing it without MapReduce. Oracle, SAS, IBM and many niche providers run their own software components on the data nodes. This will change the dynamics of how we construct clusters. More memory and more CPU will be required to support these additional processing requirements. It is too early to tell if we should beef up our nodes or add more nodes. Short of running your own POC and tests, keep an eye on the “all-in-one” appliance vendors as they bring out their new appliances in the year. How they move will be a good indicator.

Does any vendor have a “silver bullet”?   Until these solutions get into production and mature, there will be challenges.   However, they still will provide exceptional value creation – even with any associated headaches. Do not shy away. Do your due diligence and choose tools that leverage your current capabilities. Move forward, Big Data is here to stay and you need to move forward or be left behind. The accelerator has been pushed. Are you stuck in neutral or are you in the race to develop a competitive advantage from Big Data?

If you want to learn how to quickly gain value from your Big Data; contact Perficient!

“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.


DataDividend
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:

SELECT

*

FROM

[candle_soap_shop$]

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.

pic1

 

 

 

 

 

 

 

 

 

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.

[candle_soap_shop$].Product_Type

,[candle_soap_shop$].Product_Detail,

count(*)

FROM

[candle_soap_shop$]

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

group by [candle_soap_shop$].Product_Type

,[candle_soap_shop$].Product_Detail

order by count(*) desc

 

Let us run the report and see what we have.

pic1

 

 

 

 

 

 

 

 

 

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.

pic2

 

 

 

 

 

 

 

 

 

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

pic3

 

 

 

 

 

 

 

 

 

 

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.

pic4

 

 

 

 

 

 

 

 

 

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 »