Square Peg, Round Hole

Advanced Record-Level Business Intelligence with Inner Queries

Thu, 16 Dec 2010 15:07:00 GMT

While business intelligence is generally applied at an aggregate level to large data sets, it's often useful to provide a more streamlined insight into an individual records or to be able to sort and rank them. For instance, a salesperson looking at a specific customer could benefit from basic stats on that account. A marketer trying to define an ideal customer could pull the top entries and look for insights or patterns. Inner queries let you do sophisticated analysis without the overhead of traditional BI or OLAP technologies like Analysis Services.

Example - Order History Constancy

Let's assume that management has realized that the best thing for our business is to have customers ordering every month. We'll need to identify and rank customers based on how consistently they buy and when their last purchase was so sales & marketing can respond accordingly. Our current application may not be able to provide this and adding an OLAP server like SSAS may be overkill for our needs. Luckily, SQL Server provides the ability to do relatively sophisticated analytics via inner queries. Here's the kind of output we'd like to see.

Creating the Queries

Before you create a view, you need to create the SQL query that does the calculations. Here we are calculating the total number of orders as well as the number of months since the last order. These fields might be very useful to sort by but may not be available in the app. This approach provides a very streamlined and high performance method of delivering actionable information without radically changing the application. It's also works very well with self-service reporting tools like Izenda.

SELECT CustomerID,CompanyName,
(
SELECT COUNT(OrderID) FROM Orders
WHERE Orders.CustomerID = Customers.CustomerID
) As Orders,
DATEDIFF(mm,
(
SELECT Max(OrderDate) FROM Orders
WHERE Orders.CustomerID = Customers.CustomerID)
,getdate()
) AS MonthsSinceLastOrder
FROM Customers

Creating Views

To turn this or any query into a view, just put CREATE VIEW AS before it. If you want to change it use the statement ALTER VIEW AS.

Creating Computed Columns

If you'd prefer not to create a view, inner queries can also be applied by using computed columns. Place you SQL in the (Formula) field of the Computed Column Specification or check out this article here.

Advanced Scoring and Ranking

One of the best uses for this approach is to score leads based on multiple fields. For instance, you may be in a business where customers that don't order every month require more persistent follow up. You could devise a simple formula that shows the continuity of an account. If they ordered every month since their first order, they would be at 100 indicating that they have been ordering 100% of the time. Here's the query that would calculate that. It uses a few SQL tricks to make this happen. We are extracting the count of unique months and then dividing by the months since initial order. This query will give you the following information which can be used to help sales and marketing now where to focus. You could sort by this percentage to know where to start calling or to find patterns describing your best customers.

Number of orders
First Order Date
Last Order Date
Percentage of months order was placed since last order.

SELECT CustomerID,
(SELECT COUNT(OrderID) FROM Orders
WHERE Orders.CustomerID = Customers.CustomerID) As Orders,
(SELECT Max(OrderDate) FROM Orders
WHERE Orders.CustomerID = Customers.CustomerID) AS LastOrder,
(SELECT Min(OrderDate) FROM Orders
WHERE Orders.CustomerID = Customers.CustomerID) AS FirstOrder,
DATEDIFF(mm,(SELECT Min(OrderDate) FROM Orders
WHERE Orders.CustomerID = Customers.CustomerID),getdate()) AS MonthsSinceFirstOrder,
100*(SELECT COUNT(DISTINCT 100*DATEPART(yy,OrderDate) + DATEPART(mm,OrderDate))
FROM Orders WHERE Orders.CustomerID = Customers.CustomerID) /
DATEDIFF(mm,(SELECT Min(OrderDate) FROM Orders
WHERE Orders.CustomerID = Customers.CustomerID),getdate()) As OrderPercent
FROM Customers

Izenda Reports 6.3 Top 10 Features

Thu, 16 Dec 2010 14:54:00 GMT

Izenda 6.3 Top 10 New Features and Capabilities

1. Izenda Maps Add-On

The Izenda Maps add-on allows rapid visualization of geographic or geo-spacial data. It is fully integrated with the the rest of Izenda report package and adds a Maps tab which allows users to add interactive maps to their reports. Contact your representative or sales@izenda.com for limited time discounts. Izenda Maps even has rich drill-down capabilities that allow you to dive deeper with a simple hover (also requires dashboards).

2. Streamlined Pie Charts with "Other" Slices

The advanced properties of the Pie Chart now allows you to combine the smaller slices into a single "Other" slice. This reduces the visual complexity without throwing off the scale of the chart. Compare the difference below.

3. Combined Bar + Line Charts

The Bar chart now allows dual visualization of multiple metrics simultaneously by adding a line for secondary data. Enabled via AdHocSettings.AllowLineOnBar = true;

4. Stacked Bar Charts

The stacked bar chart lets you see a breakdown of a measure based on categorical data. It is enabled with the following code. AdHocSettings.AllowStackedBarChart = true;

5. Self-Joining Data Sources

The self-join features allows for parent-child relationships to be accessed from the Data Sources tab. The same table can be used as a secondary child table within the Report Designer.

6. Report Design From Dashboard View

Dashboards now sport both view and design icons to allow quick access to both.

7. Field Arithmetic on Dates

Differences between dates can now be used as measures with the arithmetic feature.

8. Simplified Multi-Tenancy

Integrating with multi-tenant systems is now easier than ever. The following APIs have been added to facilitate common scenarios. AdHocSettings.CurrentUserTenantId = value; AdHocSettings.SchedulerTenantID = value; AdHocSettings.SchedulerTenantField = "AccountID";

9. Support For SQL 2008 R2 and SQL Azure

Izenda now supports the latest version of Microsoft's database as well as the SQL Azure service.

10. Enhanced Performance and Compatibility for Stored Procedures

Izenda now supports more stored procedures than ever and runs them faster too.

High Performance SQL Views Using WITH(NOLOCK)

Tue, 11 May 2010 13:53:00 GMT

Every now and then you find a simple way to make everything much faster. We often find customers creating data warehouses or OLAP cubes even though they have a relatively small amount of data (a few gigs) compared to their server memory. If you have more server memory than the size of your database or working set, nearly any aggregate query should run in a second or less. In some situations there may be high traffic on from the transactional application and SQL server may wait for several other queries to run before giving you your results.

The purpose of this is make sure you don’t get two versions of the truth. In an ATM system, you want to give the bank balance after the withdrawal, not before or you may get a very unhappy customer. So by default databases are rightly very conservative about this kind of thing.

Unfortunately this split-second precision comes at a cost. The performance of the query may not be acceptable by today’s standards because the database has to maintain locks on the server. Fortunately, SQL Server gives you a simple way to ask for the current version of the data without the pending transactions. To better facilitate reporting, you can create a view that includes these directives.

CREATE VIEW CategoriesAndProducts AS
SELECT *
FROM dbo.Categories WITH(NOLOCK)
INNER JOIN dbo.Products WITH(NOLOCK) ON dbo.Categories.CategoryID = dbo.Products.CategoryID

In some cases quires that are taking minutes end up taking seconds. Much easier than moving the data to a separate database and it’s still pretty much real time give or take a few milliseconds. You’ve been warned not to use this for bank balances though.

More from Data Stream

Interesting perspective on BI for SQL Server using an asp.net application

Tue, 21 Apr 2009 03:12:00 GMT

This interview is pretty interesting, the company is seeking to replace consultants with software rather than supplement or create work for them which is not the norm in the BI software space. Worth the 10 minute listen:

BI for the end-user

A neat trick to report on data from SalesForce.com using SQL Server

Thu, 16 Apr 2009 19:14:00 GMT

I have had to report on salesforce.com data before, and it can be a pain since their database uses strange naming conventions and composite keys. Here is a nifty article that addresses how to report on leads and their associated tasks.

http://izenda.com/Blog/sql/reporting-on-salesforce-com-data-with-sql-server/

Enjoy! Hopefully, salesforce will incorporate reporting features that make it easier to pull marketing data out of their system in a straight forward way.

The Hidden Costs of Free Reporting Tools - Conclusions

Tue, 31 Mar 2009 17:46:00 GMT

If you haven't read the first post on this subject, please see here:

http://weblogs.asp.net/brendansullivan/archive/2009/03/30/the-hidden-costs-of-free-reporting-tools.aspx

After putting some more thought into it, I came to some general conclusions, and I will close with a couple of recommendations based only on my experience. I also will address only reporting systems. I do this because although businesses need decision support, reporting is a separate component of BI in itself. It is the component that most often interfaces with the most decision makers. Because of this, all other aspects of BI will fail (analytics, prediction, etc) without giving regard to exactly what reports are needed and in what form. My criteria when selecting a tool for use with web apps:

Does it need the involvement of IT staff beyond training or setup?
Can a user make a change to a report (add column, add sum, add chart) without needing a DBA or IT?
Is it totally browser based-- can a user create and deploy a report using Firefox or IE?
Can it be seamlessly integrated into an existing application or rebranded? ie, is there an API for making customizations at the code level?
Do I, the BI solutions provider, have to know anything about their business in order for them to begin building reports and be successful?

These are a general criteria that can help establish an organizations expectations about the reporting process. I believe (shameless plug) there are a couple of great approaches to this.

If you are on a Microsoft stack, and integrating or building web application. In my opinion, Izenda builds a great product that meets these criteria. If you using a Java application, I like jasper reports due to its flexibility. The alternative is purchasing a widget and adding a full user interface, which will mean a lot of development and testing. On the other hand, both packages are lightweight, quick to implement, and expose an extensive API to the developer to allow for complete integration.

The Hidden Costs of Free Reporting Tools

Mon, 30 Mar 2009 15:57:00 GMT

Being that I work in the reporting sector, I would like to offer my thoughts on the current state of providing self-service reporting to end-users. By self-service, I mean the ability for business users to create or customize their own reports without having to understand the data model. Although it sounds simple, it is actually a huge challenge to design software that does this. There is alot of hype about "free" reporting tools--  they have their place in organizations but I think that you have to carefully evauluate the meaning of "free."  Because these free reporting tools lack of adaptability and user friendliness, they can potentially have enormous costs over time.

These Tools Require A Developer

Most reporting solutions claim to be web-based, but they essentially only allow a desktop report to be published to the web. The limitation with this approach is that this only works with static or canned reports. Since there is no way to anticipate most of the reports that will be needed, users tend to need customized reports. Traditional reporting tools like Crystal Reports require a software developer to make any and all changes. Additionally, this individual will require administrator level permissions to the database. Everyday users can not use these tools because they do not know how to connect to databases, use programming tools or the SQL language.

User Communities Need Numerous Reports Every Year

In situations where self-service reporting is available or report providers are responsive, even small teams may ask for dozens of custom reports. Larger teams may ask for over a hundred. For software vendors that are serving multiple customers, this problem is compounded further and the user community may need thousands of reports every year.

Keeping Up With Requests Requires Significant Engineering Time

Even in harsh economic times, qualified software engineers are difficult to find and command ever-increasing salaries. Outsourcing does not work well for reporting due to language and time zone barriers. Additionally, reports may contain sensitive information which must remain within the domain of the organization. Reporting demands time, and even a simple customization may take a few hours of work because the developer needs to understand the requirement, load the existing report, analyze the data model, update the model, validate the report with the user and finally publish the report to the web server. Creating new reports from scratch can take even longer. Since developers generally do not understand the data they are working with, it may take a few tries before the report is correct.

Free Reporting Tools are Not Completely Self-Service Or Browser Based

Most vendors provide end-user reporting tools which are designed for intranet environments and behave like desktop applications. They run on a users desktop and require installation. Additionally, they require a direct connection to the database server which is normally not available to users for security reasons. While users can use these tools to generate reports, a developer must first create a model and update the model every time new information is needed. In contrast, a true self-service solution allows users to create and customize reports directly while ensuring that users only see data to which they have access.

The Costs of Developer-Driven Reports

A software vendor, IT organization or solutions provider may serve up to 100 or more customers or departments. Each organization may need between 10 and 100 custom reports per year. Some larger organizations may need well over 100. Let's assume that a software engineer with good communication skills costs about $100,000 per year after salary, benefits and other expenses. Serving 50 customers that require 50 reports every year each would require three full time engineers dedicated to reporting. For a fraction of this cost, self-service reporting solutions exist that can be implemented in a manner of days.

Time = 50 Customers * 50 Reports Per Year * 2.5 Hours Per Report = 6,250 Man Hours = 3 Man Years
Cost = 3 Man Years * $100,000 = $300,000

Building vs. Buying Self-Service Reporting

Many organizations that understand the true costs of traditional reporting have tried to implement self-service capabilities in-house. Unfortunately such projects are often risky and provide underwhelming results. Providing a full solution with exports, charts, filters, pivots and dashboards requires a significant capital investment.    This is a large project, there are numerous development and QA details that involve critical challenges such as PDF rendering, AJAX, data validation, ensuring security and optimizing performance. Implementing a high performance flexible reporting system for an individual application can take as long as 3-5 man years of engineering and QA time. Additionally, it could be one or more years before it gets delivered to customers which creates the potential for a competitor to offer reporting to their customers more quickly. In contrast, a few vendors offer self-service reporting who provide a complete solution that can be delivered in a manner of days for a fraction of annual cost.

3-5 Man Years * $100,000 = $300,000 - $500,000 + Delayed Release

The Potential Cost Of Not Providing Reports Is Even Greater

While having professional software engineers create reports has enormous costs, the real cost is not providing them. Business users learn to stop asking for reports when they do not get them fast enough. This results in critical decisions being made without necessary information. Application providers and software vendors miss critical opportunities to engage decision makers with static reporting. Since decision makers tend to use reporting rather than the application, not satisfying their needs risks losing a customer or limiting adoption.

Never ending dominance of SQL?

Wed, 18 Mar 2009 17:10:00 GMT

In the recent months, there had been some worry amongst developers concerning the Azure platform in light of the early comments Microsoft folks had made such as “If it works with SQL Server, it will largely work with SQL Data Services.” In fact, it based wasn't worry for some, it was outright panic. I am involved with QA and the prospect of supporting yet another not fully SQL compliant "data connector" was unappetizing.

Recent announcement have allayed my worries for the moment, and I am confident Microsoft will not leave billions of dollars in SQL infrastructure in the mix! But it does raise some good questions about the trade-offs between sticking with ACID compliance vs BASE availability and scalability. Obviously, these have been questions which the Azure folks have been thinking deeply about. But even more fundamentally, it raises questions about SQL itself. I have seen first hand what some of the proprietary database platforms, such as Vertica can do. These database have special significance in the realm of business intelligence, and specifically in reporting, which is my industry. Simply put, transactional requirements in analytics database are next to nill and all we are concerned about is combing very large amounts of data in novel ways- and doing it *quickly*.

So is SQL on its way out the door in the analytics space? I think not-- Sanjay's article over at Data Stream brings it home.

"SQL has served us well and needs to shed some remnants of a different era, but will allow us to leverage the trillions that have been poured into SQL-based technologies." - Data Stream, is SQL the x86 of business?

It's a good article. I believe MS made a great decision. Sure, we are going to have to wait a little longer for Azure to fully implement its SQL compatibility, but as long as I can feed it SQL-92, I'll be waiting in line for its release!