Prism Business Intelligence Tools - Tips and How-to's

Today I will answer a very interesting question from Anthony.

Anthony need to know how many days pass from one sale to the other from a specific type of sale, lets say the sale type is [Code]  field and sale date is [DATE SOLD]

The table looks like that:

 

First I duplicated this table using the duplicate item when hovering a table, now we have two identical tables:[Daily Sales By Code] and [Daily Sales By Code2]

Next step is to Add an SQL statement that will combine this tables together:

SELECT a.[CODE],
       a.[DATE SOLD],
       b.[DATE SOLD],
       Daydiff(a.[DATE SOLD], b.[DATE SOLD]) diff
FROM   [Daily Sales By Code] a,
       [Daily Sales By Code2] b
WHERE  a.[CODE] = b.[CODE]
       AND a.[DATE SOLD] <> b.[DATE SOLD]
GROUP  BY a.[CODE],
          a.[DATE SOLD],
          b.[DATE SOLD] 

This will give us all permutations of the dates while its not the same date (a.[DATE SOLD] <> b.[DATE SOLD]) and both dates are from the same code  (a.[CODE] = b.[CODE]) I also added Daydiff(a.[DATE SOLD], b.[DATE SOLD]) to get the number of days between the date pairs.

This SQL statement will return the positive and negative date diff between the pairs so to get all dates with all positive Day Diffs we will wrap it by another sql statment:

SELECT *
FROM   (SELECT a.[CODE],
               a.[DATE SOLD],
               b.[DATE SOLD],
               Daydiff(a.[DATE SOLD], b.[DATE SOLD]) diff
        FROM   [Daily Sales By Code] a,
               [Daily Sales By Code2] b
        WHERE  a.[CODE] = b.[CODE]
               AND a.[DATE SOLD] <> b.[DATE SOLD]
        GROUP  BY a.[CODE],
                  a.[DATE SOLD],
                  b.[DATE SOLD]) t1
WHERE  t1.diff > -1   

Where  t1.diff > -1 will give us all positive date pairs.

 

Build and Sync the eCube, open Prism and calculate the Avg days between each sale from the same type:

Create a pivot with [DATE SOLD]on the rows and Min([Diff]) custom measure on the columns, add a combo with the [Code] dimension on it and you are done.

Note: To get the avg days between sales you can right click on the [Diff] header on the pivot select Sub Totals and Avg.

Yesterday, a customer asked me how to get the total from a max aggregation. This is an interesting question, and I wanted to share the answer with you.

Let's say we have three dimensions (Customers, Projects, Items) and we want to get the max number of items in each project:

 

 Let's create a max aggregation on #Items (more info) and call it Max Items. Here you can see an example on a Pivot:

 

 

Now, let's create a new custom measure to sum all the max items in Projects: sum ( [Projects] , [Max Items] ) and call it Sum if Max Items,

Add this measure to a Numeric Indicator:

 

Tip: Adding a selector with the Customers dimension will yield the Sum of Max for each selected company.

We frequently want to compare the current month-to-date (MTD) Sales to the sales of the same period in previous month(s), rather than to the sales of entire previous months. For example, if today is the nth day of the month, we would want to compare sales of the first n days in the current month (or quarter or year) to the first n days in the previous month (or quarter or year).

PrismCubed has some built-in period-to-date functions that make this comparison very easy to implement.

The basic function MTDSum([Sales]) returns the MonthToDate Sales (where Sales is the measure for Sales).

The function PastMonth(MTDSum(Sales),M) will return the MTD Sales going back M months (where MTDSum(Sales) is a custom measure presenting the generic MonthToDate sales).

As an example, I have created the following custom measures and dropped them on a Pivot:

MTDSales = MTDSum([Sales])                      ;Month-to-date sales, current month

MTDSales P1M = PastMonth([MTDSales],1)  ;Month-to-date sales of the previous month

MTDSales P1M = PastMonth([MTDSales],2)  ;Month-to-date sales of two months ago

MTDSales P1M = PastMonth([MTDSales],3)  ;Month-to-date sales of three months ago

 

 

The figures in Pivot 1 above show the aggregated sales of the 17 first days in each of the following months: November, October, September and August 2009.

Changing the Date will automatically adjust the figures respectively, i.e. in Pivot 2 above the figures show aggregated sales of the first 23 days in each of the following months: June, May, April and March 2009.

Similarly, PrismCubed provides these quarter-to-date and year-to-date functions:

QTDSum(X), YTDSum(X), PastQurater(X) and PastYear(X)

These functions can be used in Pivots, Charts and Indicators.

The following example shows various aggregations as of the 17th of November, 2009:

MTDSales P1M - Sales of the first 17 days in October 2009

MTDSales P12M - Sales of the first 17 days in November 2008

QTDSales - Aggregated Sales in the period 1.10.2009 through 17.11.2009

QTDSales P1Q - Aggregated Sales in the period 1.7.2009 through 17.8.2009

QTDSales P4Q - Aggregated Sales in the period 1.10.2008 through 17.11.2008

YTDSales - Aggregated Sales in the period 1.1.2009 through 17.11.2009

YTDSales P1Y - Aggregated Sales in the period 1.1.2008 through 17.11.2008

Having a pivot table with data in it is not always enough. Sometimes, you want to display different colors for different value ranges.

In order to see coloring by values (Threshold), I will create a new custom string field [Indicator] in the ElastiCube Manager.

ifstring(([Amount]>50), 'Green', 'Red')

 

Now open PrismCubed, create a new Pivot and add Customer and Indicator dimensions on the rows and Amount measure on the columns.

 

Using the span selection on a member, select the Red member such that all Red members and Amount values will be selected and set its back color to red.
Do the same for the Green member.

 

Final step is to minimize the Indicator field such that it won't be visible.

 

We often receive questions from users who need to create special/custom fields. Here, I am happy to share with you the way you can convert an INT field [Seconds] into HH:MM:SS string format

First, we will need to add three custom INT fields (Hour, Min, Sec) to our table:

1. Hour Field = div([Seconds],3600)
   
   
2. Min Field  = div(Mod([Seconds],3600),60)
   
   
3. Sec Field = Mod([Seconds],60)

Finally, we will create a fourth field to contain the final HH:MM:SS string (HourRep). We do this by concatenating the three values, separated by colons (:) and we also include a '0' before any single-digit values. This is the code:

concat(
    concat(
        concat(
    -- Get hours indication depending on number of resulting digits.
    ifstring(([Hour]< 10), concat('0',ToString([Hour])),ToString([Hour])),':'),
        concat(
    -- Get minutes indication depending on number of resulting digits.
    ifstring(([Min]< 10), concat('0',ToString([Min])),ToString([Min])),':')),
    -- Get seconds indication depending on number of resulting digits.
    ifstring(([Sec]< 10), concat('0',ToString([Sec])),ToString([Sec])))

 

 

Good luck!

 

We just posted a new customer case study to the site, and since it is one of the coolest so far, I wanted to drop a word or two in our blog:

Roy Man, the CTO of saveanalien.com, has strong opinions about web analytics. He is also in the business of saving aliens.

Roy is one of the founders of a virtual alien (pet) site called saveanalien.com. Save an alien has 10 million unique aliens (each alien is one of a kind) and they are all up for adoption. The reason so many aliens are looking for a home is that "a meteor was heading towards their planet. They were going to die in 6 months if we didn't help..."

Aliens are flex clients developed with Ruby on Rails. The whole infrastructure runs on Amazon Web Services (EC2 and S3). The more care and virtual money earned by an alien-adopter, the better off the alien is – in terms of housing, furniture, looks, clothes etc.

In terms of Web Analytics, Roy has strong opinions. "We do the Web Analytics that everyone should do, although most sites don't". The philosophy is to constantly measure the value of each user, understand what they do and know what their lifetime value is. Roy is an avid SiSense Prism user: data is analyzed daily and then sent to company-wide distribution. "Without SiSense I would have needed to develop this myself". If he would have done this rather than use SiSense's common sense approach to business intelligence, he wouldn't have had time to save aliens.

This morning I discovered the existence of the Business Intelligence Index. It is a stock index of Business-Intelligence related companies published by Rick Sherman, the founder of Athena IT solutions. 

Rick says the index has been treading water, but compared to what's going on in the market, I'd say it is holding its head ABOVE water. He concludes that the "The software companies in the BI index, although many being in the middle of the performance rankings YTD, are likely to fare better and rebound quicker than other industries and even other high tech firms". This is evident in the numbers themselves, where the drop in value of the BI index is lower, in 2008 terms, than the general NASDAQ or the software segment in it. 

It's interesting to note that the index covers mostly "old school" approaches to business intelligence, with data warehouses and large, platform based solutions and none of the new breed of business intelligence vendors (in memory and SaaS). It's no wonder, since in the current climate the ability to IPO anything is close to zero, although companies in the new generation business intelligence market are reporting sales gains. 

In any case, we take all this as proof that business intelligence is a must in tough economic times, and not a luxury. However, pricing and project wise, doing it 'right', with low pricing and manageable IT expenses, is the way to go. This is why we believe that were the new generation BI companies in the index, they would fare better than the old school ones. 

Roni

 

Forrester research just published a report titled "BI Belt-tightening in a tough economic climate". In it Forrester Analyst Boris Evelson discusses the role of business intelligence in tough economic times.  

      "As an economic downturn becomes a sobering reality, enterprises look for various ways to increase revenues and reduce costs. While overall IT budgets become targets for cost cutting, business intelligence applications and infrastructure need not fall into the same category. Smart information and knowledge management professionals are leveraging BI as a corporate asset to continue to survive, compete and thrive…" 

There are two takeaway messages:

1. Don't panic: business intelligence is here to stay

2. Consider low cost alternatives to complement an existing BI stack 

One of the low cost alternatives to complement the existing enterprise BI stack is, of course, SiSense. We're very glad to be under Forrester's radar. 

Evelson's analysis of why small BI vendors are sometimes the better choice goes as follows: 

"Traditionally, mainstream BI vendors have catered primarily to IT target audiences. Even though these large vendors will disagree with such an IT-centric label, one look at their complex architectures, multiple layers and components, and integration and support requirements, reveals that these are indeed IT tools…. On the other hand, smaller BI vendors… pitch simplicity, flexibility, lower license cost, and little or no reliance on IT". 

He then lists the key things that small BI vendors need to offer:

• Data integration function to build an analytical data model
• Production/operational reporting for pixel perfect mass report distribution
• Ad hoc query tools for quick answers to business questions
• OLAP tools, for when business questions are more about the "whys" than the "whats"
• Dashboards for an interactive, visual UI – not reporting or analytical tool by itself
• SDKs

 

Congratulations to the team
Adi

Working and founding a business intelligence company means that you (a) belong to the analytics-obsessed (b) when obsessively analyzing your own business you begin to eat your own dog-food. This is entirely the case with our AdWords campaigns: we pore over them using our Prism business intelligence software. 

In 2008, we released our Amazon S3 dashboards, created using SiSense Prism. Now, we're launching pre-packaged reports and dashboards that show how we analyze our AdWords data using SiSense Prism. This time around, we've taken care to package it in a way that is meaningful and useful.  

Google AdWords have packaged reports and are beginning to allow limited drilldown. But no one can argue they were built for in-depth analysis. Many use Excel for this purpose, but it requires too much effort for anything but the basic drilldown. Other tools have pre-packaged algorithms that aim to replace the human. This is why we just launched our dog food with an AdWords label.  

Prism for Adwords is a Google AdWords analysis, dashboard and reporting tool, to be used on AdWords raw data. Typically, heavy AdWords users download AdWords raw data and analyze it in other tools, custom or excel. The reason most people use raw AdWords data is that online AdWords reporting and analysis doesn't provide all the comparisons and views that are required to optimize campaigns. One of the main needs is either to calculate custom KPIs (Key Performance Indicators) or to sort and filter keywords across ad groups and campaigns.

The optimization process makes you invest more in the better performing campaigns and words; it also means that you can adjust keywords, ads and campaigns to seasonal and other patterns. This process is iterative and continous. The better you are at digging into the data, testing hypothesis and acting upon them, the more results you'll have. There is no magic algorithm; it's a human-assisted process. This is why our software doesn't try to do the work for you – it just packs all the indicators in front of you, so you can easily make the right decision.

We're in private beta. Drop us a line here if you'd like to join.

Well, back in December we announced the SiSense Dashboard Competition. 

We're pleased to announce we have a winner: Mahmoud Wardany of Madar Holding in the UAE.

Here's the winning dashboard: 

A few words from our winner about the dashboard and its uses: 

"The dashboard is used to track orders sent to customers. We deliver thousands of orders a month, in the tens to hundreds of millions of dollars, using an Oracle based ERP. Our CEO is very concerned about the quality of the service to our customers. If we miss a shipment, the team sits on the hot spot. With SiSense, the CEO, from the luxury of his office, can take a look at the orders, by customer name and quantity, and see what's shipped. In our industry support is important and your tool is a great "service support provider"." 

The competition was judged by Peter Traynor of the DashboardInsight and by Lyndsay Wise of Wise Analytics . Their reasons for selecting the winner were as follows: 

Lyndsay: "the relevant KPIs in one user interface, making it more effective for end users to get immediate value out of the dashboard. In addition, I find it more intuitive at an initial glance." 

Peter said "I could see "at a glance" that this dashboard revealed critical data for the supply chain that any manager could quickly understand, drill down into if required, and react to. The data visualization, while not perfect was nonetheless quite effective in its presentation of the salient facts." 

Thanks again to our judges and kudos to our winner, Mahmoud Wardany. 

Adi

Have you ever wanted to see how many times a value appears in the data, so you casually dropped the 'Value' dimension onto the rows panel of a pivot widget and the 'Count Value' measure onto the columns panel and get the value '1' for everything?

 

Then you scratched your head and said to yourself "hmm, this can't be right.  I'm pretty sure I sold more than one Acer 2400 this month".  Relax, you're not going out of business :-)

There are two ways to count values- counting distinct values and counting frequency of values.

If you right click the relevant dimension ('Product' in our case) and navigate to the Show/Hide Measures menu item, you will get the following options:

 

Count Product is the default and means counting distinct products.  This measure is best used with dimensions other than the Product dimension. For example, if you want to know how many different products each one of your customers bought, this would be the measure to use.

Count Product (duplicates) means counting how many times each different product appeared.  This measure is best used with the Product dimension, although it also has uses with others.

When you 'check' the measures in the menu, the corresponding measure will be visible under the Measures node in the Data Browser.

 

Using the Count Product (duplicates) measure will yield the results I wanted.

 

Have you ever wanted to give your viewers the possibility to filter a widget with a single click?  Somewhat like the image below, that lets a user click on either the Best Selling Products or Least Selling Products and see the appropriate products in the pivot and their respective sales, cost and profit.  Here's how you do it.

 .........................................................................................................................................................................

 

Step 1: Create a pivot with all products and required measures.

This step is pretty straight forward.  Create a pivot and populate it with the Product dimension as well as the require measures.  If you've never worked with a pivot table before, check out this tutorial.  This pivot should look something like the follow image.

 .........................................................................................................................................................................

 

Step 2: Create 'Best Selling Products' and 'Worst Selling Products' Filters

I quickly create Best Selling Products and Worst Selling Products by using Question Composition.  If you've never used it before, check out this blog post.  The filters will look something like this:

For more information about the different filter types, check out the filters section of our user guide.

 .........................................................................................................................................................................

 

Step 3: Create drop-down lists from 'Best Selling Products' and 'Worst Selling Products' Filters

Create the filters, dragging and dropping them directly onto the sheet will automatically create a drop-down list widget out of each filter.

 .........................................................................................................................................................................

 

Step 4: Using the Apply Scope On Widget Action Button

This is where lies the trick.  The Apply Scope on Widgets action button lets you apply the data placed in one widget as the scope of another widget.  It is accessed from the 'Actions' category in the Widgets pane and created simply by dragging onto the sheet.

This button takes two main parameters (accessed from the right-click menu):

1. Input Widgets - Indicates where to get the scope data from.  In our case, this data is placed in the Product drop-down list widgets.

2. Output Widgets - Indicates the widget the receives the new data.  In our case, this is the pivot showing product sales, cost and profit information.

 

You should create two buttons like these.  Each button will define one of the Products drop-downs as input.  The output for both the buttons will be the pivot widget.  Make sure the (ALL) value is selected, to indicate that the all members should be used as scope.  After doing this, you should immediately have two buttons that shows the correct filters in the pivot once clicked.

 .........................................................................................................................................................................

 

Step 5: Some Finishing Touches

Our dashboard already does what we want it to, but let's make it a little bit more elegant.  First, there's no reason anyone should see the drop-down lists we used so you can simply hide them behind the main pivot.  Move them both so they are completely overlapped by the pivot and use the positioning menu to send them back behind the pivot so they cannot be seen.

 

Second, let's give the buttons themselves look more like hyperlinks than buttons (everybody loves a good hyperlink).  To do this, simply select the button and set 'No Fill' coloring to both the button's area (middle button in the image below) and borders (right button in the image below).  Then apply blue underlined text coloring (left button in the image below) and we're done.

 

 

 ...and were done!  Here's a sample PSM file to play around with.

 

 

Some of you may have probably noticed that apart from sheets, a Prism document may also contains 'questions'.  Unlike a sheet that is used to visualize data, a question is a special type of canvas used to visually create filters and segment data.  It is also useful for observing the structure of a complex filter.

Defining a Dimensionality or Base Filter

The first thing to decide before beginning is which dimension you wish to filter.  Once decided, drag this dimension from the Data Browser and drop it onto the Question canvas.  A box representing all members in the dimension will appear.

Should you drop a previously created filter on this canvas you will see the filter's entire structure directly in the Question, allowing for further filtering or modifications.

 

Filtering a Dimension

When hovering with the mouse over the box, four white areas will appear.  Clicking on one of them will present a list of available filter operations you can perform on the dimension.  This are the same filter types as available directly from the data browser.  For more information about filters and filter types, visit our introduction to filters page.

 

Clicking on the requested type of filter (in our example case a Top Ranking filter) will create this filter with default values you can edit and change.  The name of the filter can be changed by double-clicking the title area.

 

Repeating The Process

You can repeat this process as many times as you wish, to create as many filters as you require.  In this example, we've created a Top Ranking and a Bottom Ranking filter off the Product Name dimension to segment it into two groups - top selling products and bottom selling products.  The filters themselves can be further filtered in the same manner.  In the example below, we connected both Top and Bottom filters into a Union box to create a single filter that returns both top and bottom products.

 

Adding Filters to the Repository

Each box in the diagram represents a list of members that answer to the given criteria.  Each box can now be added to your repository for reuse in your reports and dashboards by clicking on the save button.

 

 

 

Sometimes, data in an Excel file is not structured ideally to be imported by Prism.  Occasionally, the data is presented in a Pivot-like manner that is more readable and takes up less storage space.  A good example for this is marketing survey data that often comes in this form:

Image 1 - Marketing Survey Data Sample

 

Each row represents the answers of a single survey respondent.  The first respondent - John, gave the answer ‘Good' to the first question, the answer ‘Best' to the second question, etc.

A typical report created over marketing survey data such as this is shown in the following pivot image.  For each question the numbers of total respondents as well as the number who answered a particular answer.

Image 2 - Marketing Survey Report

If you try to import that source data as shown in Image 1, you'll encounter some difficulties creating the report in Image 2.  Since each question description comes in a different FIELD in Excel you will not be able to place them as separate rows in the pivot.  You will be able to perform interesting analysis, but not quite what we were looking for here.

Restructuring the Excel Data

In order to create the pivot we want, we need to the data structured in a flat manner as presented in Image 3:

Image 3 -Flat Marketing Survey data

This format allows you to maximize the information you can obtain out of data with Prism.  It lets you correlate between practically any member and any other.   However, manually converting our source data to this format can be quite a test on one's patience.  Luckily, you don't have to.

The Reformatting Macro

We've created an Excel macro (written in VB Script) that automatically converts data from a pivot-like format (Image 1) to a flat format (Image 3).  It accepts 5 parameters:

1. Row Fields Range: The range containing the row descriptive members.  In Image 1, this would be A2:C5.
2. Column Fields Range: The range containing the column descriptive members.   In Image 1, this would be H1:F1.
3. The name of the sheet the original data is in.
4. The name of the sheet to contain the restructured data.
5. The maximum number of rows to use on a single sheet (Excel 2003 is limited to 65K rows per sheet).

To use the macro, download the Excel file here.  Once it opens, make sure you enable macros in Excel or this won't work.  The sheet named ‘Configuration' contains a range for you to type in the parameters and a button to begin the process.  To use this macro on your data, just create a new sheet, place your data on the top left, point the macro to this data in the Configuration sheet and click ‘Reformat'.  Now create a data source out of the reformatted data and you're ready to go. Here's an example PSM file.

Note: The flat format takes up more rows than a pivot-like format.  That is why this macro will automatically break the flat data into as many sheets as required to accommodate it.  Prism allows you to append data from different sheets so your data will be fully imported.

 

Dennis Howlett quotes Sun Microsystems' Tim Bray  on surviving the tough economic times.
John Doerr lists ten points for companies to stay afloat on Venture Beat.

Being frugal has become hip, there's no doubt about that.

Both people quoted above suggest cutting software spending and large capex IT investments. We wanted to add our top software and IT cutting strategies.... And then say something about cutting expenses on business intelligence, dashboards and reporting. Business Intelligence projects can be obscenely expensive, long to implement and difficult to maintain. Isn't it time the times
(that are definitely changing) also affect this end of the enterprise software industry?

Our top suggestions:

1. Use free email.
   
2. Use open source bug tracking systems.
   
3. Use automated QA.
   
4. Use software to manage customer support.
   
5. Do not use database systems that are too weak for your needs a year from now.
   
6. Use free anti-virus and firewall software.
   
7. Outsource as much as you can (check out ODesk).
   
8. Use Prism business intelligence.

 

Why Prism? First of all, it makes our business intelligence project a non-capex project, since payment is by month (and at tens of dollars per user month, there is no argument it is inexpensive). More importantly, there is no up front investment to get the project running, making the TCO almost zero. There's no training and certification either - we've designed the software so that anyone can figure out how to use it and anyone can connect to the data sources we support. And perhaps good number crunching, analytics, poring over numbers will get you an advantage in these times. Doerr said in the post mentioned above "For your revenue plan, develop and obsess on leading indicators - e.g. bookings, unique visitors, conversions".

And good luck - sometimes trying times do make us better, much better. Frugality isn't just about money - it is also about recognizing your core strengths and focusing just on them.