BeyeBLOGS | BeyeBLOGS Home | Get Your Own Blog

December 16, 2010

Advanced Record-Level Business Intelligence with Inner Queries

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,
WHERE Orders.CustomerID = Customers.CustomerID
) As Orders,
SELECT Max(OrderDate) FROM Orders
WHERE Orders.CustomerID = Customers.CustomerID)
) 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,
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,
FROM Orders WHERE Orders.CustomerID = Customers.CustomerID) /
DATEDIFF(mm,(SELECT Min(OrderDate) FROM Orders
WHERE Orders.CustomerID = Customers.CustomerID),getdate()) As OrderPercent
FROM Customers

Posted by Sanjay Bhatia at 8:15 AM | Comments (0)

December 14, 2010

Izenda Reports 6.3 Top 10 Features

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

Posted by Sanjay Bhatia at 8:15 AM | Comments (0)

May 11, 2010

High Performance SQL Views Using WITH(NOLOCK)

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

Posted by Sanjay Bhatia at 8:45 AM | Comments (2)

March 30, 2009

The Hidden Costs of Free Reporting Tools

Your business needs reporting now. So why not free reporting? In this post, I discuss how "free" reporting can wind up costing you plenty.

More here:

Posted by Sanjay Bhatia at 12:30 PM | Comments (3)

March 17, 2009

Is SQL the x86 of business?

SQL, like the x86 instruction set has served us well but is starting to seem a bit dated. While It may lack elegance in some ways, trillions of dollars have been invested into SQL based systems and expertise. Like x86, attempts to replace it have had limited success in the marketplace. Here I discuss why it is still the best option we have today.

More here:

Posted by Sanjay Bhatia at 10:15 AM | Comments (0)