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.
|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.
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…
“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?
This 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:
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.
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.
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.
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.
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:
2. Under Actions, click on the arrow next to that field value:
3. Now select Edit Tags:
4. Create your tag and click Save:
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:
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:
Or, you can even further narrow down your search by associating your tag to a specific field using the following syntax:
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-99, you can search for all of them with:
If you wanted to find all hosts whose tags contain “22″, you can search for the tag:
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:
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.
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:
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.
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.
Today, 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.
It would be remiss in a blog on Splunk searching without at least mentioning the 6.0 version dashboard.
If you take a look at the Splunk search dashboard (and you should), you can break it down into 4 areas
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.
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:
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:
I recommend you having a look!
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”:
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.
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!!
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:
|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.
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:
The (MS Windows) Installation
On MS Windows, once your download is complete, you are prompted to Run.