Perficient Business Intelligence Solutions Blog

Blog Categories

Subscribe via Email

Subscribe to RSS feed

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

Qlik leadership – vision, guts and glory… hopefully

“It is not the strongest of the species that survives, nor the most intelligent that survives. It is the one that is most adaptable to change.” – Supposedly Darwin from ‘Origin of Species’… or NOT

According to the most recent report from Gartner, no one vendor is fully addressing the critical space in the market for “governed data discovery”. Governed data discovery means addressing both business users’ requirements for ease of use and enterprises’ IT-driven requirements – nobody is really doing that. So, who will be the most adaptable to change and embrace the challenges of an ever-changing and increasingly demanding BI and Analytics market?

Qlik leadership – vision, guts and glory… hopefullyThis year, Qlik plans to release a completely re-architected product – QlikView.Next – that will provide a new user experience, known as ‘Natural Analytics’. The lofty goal of QlikView.Next and this Natural Analytics approach is to provide both business-user-oriented and IT-friendly capabilities. According to Gartner, this approach has ‘the potential to make Qlik a differentiated and viable enterprise-standard alternative to the incumbent BI players.’

Will QlikView.Next be able to deliver the combination of business user and IT capabilities that are currently lacking in the market? Will Qlik be able to reinvent itself with Natural Analytics and deliver the “governed data discovery” solution that the market needs so desperately? Only time will tell; however Qlik is definitely showing all the traits of a real leader in the BI and Analytics space – once again, setting the bar pretty high. The vision and the guts are definitely there and accounted for. Will glory follow? That will depend on execution and delivery.

However, QlikView.Next is more than a year behind its scheduled release… so I guess we’ll have to rely on past behavior for now. Back in 2006, when Qlik carved its space on Gartner’s Magic Quadrant for Analytics and BI platforms (BusinessWire article), it positioned itself right in the ‘Visionary’ quadrant and it has been delivering on its vision ever since. For about eight years, Qlik has been delivering on its vision for Business Intelligence, i.e. user-driven BI – Business Discovery. Given this track record, I’ve reasons to believe that Qlik will be able to deliver on its vision once again.

I also believe that leadership is all about having a vision, along with the guts and ability to execute on that vision. That is probably one of the reasons why Gartner came up with quadrants that organize technologies along two dimensions – ‘Completeness of Vision’ and ‘Ability to Execute’. For the past few years, thanks to its ability to execute and deliver on its vision, QlikView has been able to work its way to the leaders quadrant and secure its position (GMQ 2014) – by demonstrating excellence in both vision and execution. So, how is Qlik planning on executing on its vision over the next few months – what’s .Next?

Well, there are several features worth mentioning… but, we’d be able to review only a few here, namely:

Read the rest of this post »

Three Big Data Best Practices

One of the benefits of the Hadoop is its ability to be configured to address a number of diverse business challenges and integrated into a variety of different enterprise information ecosystems.  With proper planning these analytical big data systems have shown to be valuable assets for companies.  However, without significant attention to data architecture best practices this flexibility can result in an crude April Fool’s joke resulting in a system that is difficult to use and expensive to maintain.

Three Big Data Best PracticesAt Perficient, we typically recommend a number of best practices for implementing Big Data. Three of these practices are:

  1. Establish and Adhere to Data Standards – A data scientist should be able to easily find the data he/she is seeking and not have to worry about converting code pages, changing delimiters, and unpacking decimals.   Establish a standard and stick to it then convert the data to the standard encoding and delimiter during the ingestion process.
  2. Implement a Metadata Configured Framework – Remember when ETL was all hand-coded?   Don’t repeat the sins of the past and create a vast set of point to point custom Sqoop and Flume jobs. This will quickly become a support nightmare.   If the costs of a COTS ETL tool are prohibitive, then build a data ingestion and refining framework of a small number of components that can be configured using metadata.   The goal for a new data feed to be added by configuring a few lines of metadata versus scripting or creating code for each feed.
  3. Organize Your Data – This practice may seem obvious, however, we have seen a number of Hadoop implementations that look like a network file share vs. a standards driven data environment.   Establish a directory structure that allows for the different flavors of data.   Incremental data (aka delta’s), consolidated data, data that transformed, user data, and data stored in Hive should be separated by into different directory structures.   Leverage a directory naming convention; then publish the standard so that data scientists/users can find the data they are seeking.

Addressing these three best practices will ensure that your Big Data environment is usable and maintainable.   If you are implementing or considering a Big Data solution, Perficient has the thought-leadership, partnerships, and experience to may your Big Data program a success.

Tag Splunk, you’re it!

Splunk does a wonderful job of searching through all of the data you’ve indexed, based upon your search command pipeline. There are times though that you can add additional intelligence to the search that Splunk cannot add on its own – perhaps this information is specific to your organizational structure, like host names or server names. Rather than typing this information within the Search pipeline each time, you can create a knowledge object in the form of a Splunk search tag.

Search Tags

To help you search more efficiently for particular groups of event data, you can assign one or more tags to any field/value combination (including event type, host, source, or source type) and then do your searches, based on those tags.

Tagging field value pairs

You can use Splunk Web to create your tags directly from your search results. As an example, I’ve indexed multiple Cognos TM1 server logs into my Splunk server. These logs are generated from many different TM1 Admin servers but are all indexed by one Splunk server. If I’d like to have the ability to search a particular server source without having to qualify in each of my searches, I can create a tag for that server.

In a resulting search, I can select any event that has the field value pair that I want to tag, then:

1. Click on the arrow next to that event:

tag1

 

 

 

 

 

 

 

 

2. Under Actions, click on the arrow next to that field value:

tag2

3. Now select Edit Tags:

tag3

4. Create your tag and click Save:

tag4

 

 

In my example, I created a tag named “TM1-2” that specifies a particular TM1 server source. In the future, I can then use that tag to further narrow my search and isolate events that occurred only in that server log:

tag5

tag=TM1-2 product x plan

You can use the tag to narrow down the search (like in my example above) by using the following syntax:

tag=<tagname>

Or, you can even further narrow down your search by associating your tag to a specific field using the following syntax:

tag::<field>=<tagname>

Use wildcards to search for tags

As a Splunk Master, you can “get wild” and use the asterisk (*) as a wildcard when searching using your Tags. For example, if you have multiple event-type tags for various types of TM1 servers, such as TM1-1 and TM1-99, you can search for all of them with:

tag::eventtype=TM1-*

If you wanted to find all hosts whose tags contain “22″, you can search for the tag:

tag::host=*22*

Here is an interesting example that I have yet to utilize (although you’ll find it in several places in the Splunk documentation): if you wanted to search for the events with event types that have no tags associated with them, you can search for the Boolean expression:

NOT tag::eventtype=*

Wildcards in general

Wildcard support makes searching very flexible, however it is important to understand that the “more flexible” (or less specific) you’re Splunk searches are, the less efficient they will become. It is recommended that care be taking when using wildcards within your searches.

Splunk On! 

 

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 »

QlikView… QlikTech… Qlik…

Several years ago, when I started using QlikView (QlikTech’s flagship product), I had a strong preference for more traditional BI tools and platforms, mostly because I thought that QlikView was just a visualization tool. But after some first-hand experience with the tool, any bias I had was quickly dissipated and I’ve been a QlikView fan and fulfilling the role of Senior QlikView Architect on full lifecycle projects for a while now.

QlikViewToday, Qlik Technologies (also known as QlikTech or simply Qlik) is the 3rd fastest growing tech company in the US (according to a Forbes article) but my personal journey with QlikView, and probably QlikTech journey as well, has not always been easy – a paradigm shift in the way we look at BI is required. Most importantly, I understood along with many others, that this isn’t a matter of QlikView or SAP BI, of QlikView Agile approach to BI or Traditional BI – it is NOT a matter of ORs, but rather a matter of ANDs.

It is a matter of striking the right balance with the right technology mix and do what is best for your organization, setting aside personal preferences. At times QlikView may be all that is needed. In other cases, the right technology mix is a must. At times ‘self-service’ and ‘agile’ BI is the answer…. and at times it isn’t. Ultimately, it all revolves around the real needs of your organization and creating the right partnerships.

So far, QlikTech has been able to create a pretty healthy ecosystem with many technology partners, from a wide variety of industries and with a global reach. QlikTech has been able to evolve over time and has continued to understand, act on and metabolize the needs of the market, along with the needs of end-users and IT – I wonder what’s next.

That’s one of the reasons why Qlik has been able to trail-blaze a new approach to BI; user-driven BI, i.e. Business Discovery. According to Gartner ‘Qlik’s QlikView product has become a market leader with its capabilities in data discovery, a segment of the BI platform market that it pioneered.’

Gartner defines QlikView as ‘a self-contained BI platform, based on an in-memory associative search engine and a growing set of information access and query connectors, with a set of tightly integrated BI capabilities’. This is a great definition that highlights a few key points of this tool.

In coming blogs, we’ll explore some additional traits of QlikTech and its flagship product QlikView, such as:

Ø  An ecosystem of partnerships – QlikTech has been able to create partnerships with several Technology Partners and set in place a worldwide community of devotees and gurus

Ø  Mobility – QlikView was recently named ‘Hot Vendor’ for mobile Business Intelligence and ranks highest in customer assurance (see WSJ article here) with one of the best TCO and ROI

Ø  Cloud – QlikView has been selected as a cloud-based solution by several companies and it has also created strong partnerships with leading technologies in Cloud Computing, such as Amazon EC2 and Microsoft Azure

Ø  Security – provided at the document, row and field levels, as well as at the system level utilizing industry standard technologies such as encryption, access control mechanisms, and authentication methods

Ø  Social Business Discovery – Co-create, co-author and share apps in real time, share analysis with bookmarks, discuss and record observations in context

Ø  Big Data – Qlik has established partnerships with Cloudera and Hortonworks. In addition, according to the Wall Street Journal, QlikView ranks number one in BI and Analytics offering in Healthcare (see WSJ article here), mostly in connection with healthcare providers seeking “alternatives to traditional software solutions that take too long to solve their Big Data problems”

 

In future posts, I am going to examine and dissect each of these traits and more! I am also going to make sure we have some reality checks set in place in order to draw the line between fact and fiction.

What other agile BI or visualization topics would you like to read about or what questions do you have? Please leave comments and we’ll get started.

Searching with Splunk

It would be remiss in a blog on Splunk searching without at least mentioning the 6.0 version dashboard.

The Search dashboard

If you take a look at the Splunk search dashboard (and you should), you can break it down into 4 areas

  • Search Bar. The search bar is a long textbox that you can enter your searches into when you use Splunk Web.
  • Range Picker. Using the (time) range picker you set the period over which to apply your search. You are provided with a good supply of preset time ranges that you can select from, but you can also enter a custom time range.
  • How-To. This is a Splunk panel that contains links you can use to access the Search Tutorial and the Search Manual.
  • What-To. This is another Splunk panel that displays a summary of the data that is installed on this Splunk instance.

as1

 

 

 

 

 

 

 

 

 

 

The New Search Dashboard

After you run a new search, you’re taken to the New Search page. The search bar and time range picker are still available in this view, but the dashboard updates with many more elements, including search action buttons, a search mode selector, counts of events, a job status bar, and results tabs for Events, Statistics, and Visualizations.

Generally Speaking

All searches in Splunk take advantage of the indexes that where setup on the data that you are searching. Indexes exist in every database, and Splunk is not an exception. Splunk’s indexes organize words or phrases in the data over time. Successful Splunk searches (those that yield results) return records (events) that meet your searching criteria. The more matches you find in your data (the more events Splunk returns) will impact the overall searching performance so it is important to be as specific in your searches as you can.

Before I “jump in”, the following are a few things worth alerting you to:

  • Search terms are case insensitive.
  • Search terms are additive
  • Only the time frame specified is queried
  • Search terms are words, not parts of words

Splunk Quick Reference Guide

To all of us future Splunk Masters, Splunk has a (updated for version 6.0) Splunk Language Quick Reference Card available for downloading in PDF format from the company website:

www.splunk.com/web_assets/pdfs/secure/Splunk_Quick_Reference_Guide.pdf.

I recommend you having a look!

To Master Splunk, you need to master Splunk’s search language, which includes an almost endless array of commands, arguments and functions. To help with this, Splunk offers its searching assistant.

The Splunk searching assistant uses “typahead” to “suggest” search commands and arguments as you are typing into the search bar. These suggestions are based on the content of the datasource you are searching and are updated as you continue to type. In addition, the searching assistant will also display the number of matches for the search term, giving you an idea of how many search results Splunk will return.

The image below shows the Splunk searching assistant in action. I’ve typed “TM1” into the search bar and Splunk has displayed every occurrence of these letters it found within my datasource (various Cognos TM1 server logs) along with a “hit count”:

as2

The search assistant uses Python to perform a reverse-url-lookup to return description and syntax information as you type. You can control the behavior of the searching assistant with UI settings in the Search-Bar module, but it is recommended that you keep the default settings and use it as a reference.

Some Basic Optimization

Searching in Splunk can be done from Splunk Web, from the command line interface (CLI) or the REST API. When searching using the Web interface you can (and should) optimize the search by setting the search mode (Fast, Verbose or Smart).

Depending on the search mode, Splunk automatically discovers and extracts fields other than the default fields, returns results as an events list or a table, and runs the calculations required to generate the event timeline. This “additional work” can affect the performance and therefore the recommended approach would be to utilize the Splunk Fast Mode during which time you conduct your initial search discovery (with the help of the searching assistant) after which you can move to either the verbose or smart mode (depending upon your specific requirements and the outcome of your discovery searching).

Time-out

I should probably stop here (before this post gets any longer) – but stay tuned; my next post is already written and “full of Splunk” …

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 »

Thank you for downloading Splunk Enterprise. Get started now…

install_image_002

 

 

 

 

 

 

 

 

Once you have found your way to the (Splunk.com) website and downloaded your installation file, you can initiate the installation process. At this point you would have received the “Thank You for Downloading” welcome email.

More than just a sales promotion, this email gives you valuable information about the limitations of your free Splunk Enterprise license, as well as links to help you get started quickly, including links to:

  • Online Tutorials
  • Free live training with Splunkers
  • Educational videos
  • Etc.

The (MS Windows) Installation

On MS Windows, once your download is complete, you are prompted to Run.

install_image_003

 

 

 

 

 

 

 

 

 

Read the rest of this post »