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

This is where we post tips and how-to's in response to questions we get from Prism users.
  • New Help Desk System


    SiSense is pleased to announce the implementation of a new Help Desk System.

    As our customer base has been growing rapidly, we want to ensure that we remain able to provide first-class support and a terrific user experience for all of our customers. The new Help Desk system is now up and running, and will help us efficiently handle all the end-user support requests that we receive.

    We invite you to try it now!

    The new system includes:

    Ticketing System – When a new support request is received, it is automatically assigned a ticket which makes it easier for us to track each case and ensure that it is properly addressed.

    Forums – The new discussion forums allow an efficient means of communication between customers and SiSense, as well as among customers themselves.

    Announcements – Customers will never miss an important announcement regarding Prism.

    Feature Requests – Customers are welcome to submit new feature requests which may help us improve the product.

    Our goals are to deliver the best business intelligence software on the market along with the best customer support experience. If you have any suggestions for how we can serve you better, we would love to hear from you.



  • How many Days from Today

    Hi There,

    I recently see many of you request for a calculation between some date and today.

    Using the formula Editor you can use the DDIFF function with two parameters to solve this:

    1. Now( [Day Level] ) - Returning Today

    2. [Day Level] - The day dimension you want to calculate with


    Example:

    DDiff( Now([CreateDate (Days)]) , [CreateDate (Days)] )

    This function returns the amount of days from CreateDate till today.

    Have an amazing day!

  • Prism Goodies - a Dashboard Inspired by Google+

    Hello,

    Designing attractive dashboards can sometimes take even longer than preparing the analysis itself.

    With SiSense Prism, however, it’s fast and easy!

    Check out this PSM dashboard template inspired by the design of the new Google+.

    Design Concepts:

    • The dashboard enables sales reps to have complete control over the lead pipeline
    • Reps can click a lead to get information about them from social networks
    • Reps can see if leads from the same domain/IP already exist in the system
    • Reps can filter leads according to time zone

    Click here to download this free PSM template!



     


     

  • Application Insights: How to calculate MAU and DAU in Prism

    In the past few years analysts trying to measure user stickiness in web or desktop apps, The data exist but they found it very hard to calculate the DAU and MAU.

     First, what are DAU and MAU?

    • DAU Refers to the number of unique users of your application per day by some event (i.e. # unique users that clicked on video) 
    • MAU Refers to the number of unique users per the past 30 days by some event.

    The main problem is to calculate the MAU due to the fact you need to count the number of unique users for the past 30 days for each date.

    1. Assume we have a table called [30days] with 3 fields (Date, User, Events)


    2. First we need to create a new custom SQL Expression to have last 30 days for each selected date, lets call it eDate (SelectedDate)

    SELECT
        b.[Date] [SelectionDate], a.[Date] [30Date]
    FROM
        (SELECT
            [Date]
        FROM
            [30days]
        GROUP BY [Date]) a
    INNER JOIN
    (SELECT
            [Date]
        FROM
            [30days]
        GROUP BY [Date]) b
    ON a.[Date] > AddDays(b.[Date], -30) AND a.[Date] <= b.[Date]
    ORDER BY [SelectionDate], [30Date] ASC

    3.  To Calculate the DAU we will need to create a new SQL Expression, lets call it eEventsDay. (You can add here a WHERE statement if you would like to limit it to one event)
        

    SELECT
        [Date], [User], Distinct_Count([Events]) [EventCount]
    FROM
        [30days] a
    GROUP BY [Date], [User]


    4. Now duplicate this table creating the MAU and call it eEvents

    5. Connect the tables to each other using the illustration above.

    6. Next we need to build our eCube and start building our dashboard.

    Building the Pivot with the MAU DAU analysis

    7. Create 2 new filters by Value on the EventCount fields in both eEvents and eEventsDay tables, in this sample we want to see all users with more then one events a day


    8. Create two count measures over the User fields in both eEvents and eEventsDay tables and call them [# eEvents Users] and [# eEventsDay Users]

    9. Create two Measured Values using the formula editor counting the number of users and the filters we created in 7.  Example: ( [# eEvents Users] , [>=1])

    10. Create a Pivot and add the SelectedDays field on the rows panel

    11. Add the two Measured Values into the columns panel and click on update.

    Download Mau Dau Solution.psm


  • Pivot Values - Change Currency Values

    Some businesses need to see the same results in a different currencies, for example a sales manager needs to see the same regional sales by different currencies for each region.

    First, create a simple conversion table in Excel:

     

    Now add this as a table in the ElastiCube manager. (You don't need to connect it to any other table)

     

    Select Build Schema Changes and open Prism

    Create a Pivot, then drag a dimension onto the rows and drag some amount Measure onto the columns.

    Now, add the two dimensions (Currency, Value) from the Excel table on two Disconnected Members Pickers.

     

    Connect the Value picker to the Currency picker via the Dynamic Input such that every time you select a currency, the value changes.

    Rename the Values picker to curr (Right click -> Rename)

    Now here is the interesting part, you are going to use the Parameters feature to apply a measure calculation on the Pivot measure, To do so, click on the Pivot measure and edit the formula on the top:

    [Measure] * [@curr] (Where [@curr] is our parameter)

     

    Now every time the user selects a currency, the values picker changes and changes the pivot result.

  • Example of how to implement Chart zooming in BIStudio

     

    Posted Oct 24 2010, 07:53 PM by Adi with no comments
    Filed under: ,
  • Placeholders Sample

    Creating dashboards requires placeholders in many cases, in today's post I will show you some use cases for parameters

     

    1. Get the products that did not sold on a selected date range:

      • First we need a date picker with date dimension and a pivot with products on the rows and some measure on the columns
      • Rename the Date Picker to Date1
      • Make sure that the date picker is not connected to the Pivot
      • Create a duplicate count aggregation on the Products call it # Products
      • Create a new custom measure (# Products, [@Date1]) and call it Selected Dates Products, this will result the number of products in the selected dates
      • Now it time to create a filter on the pivot, right click on the Product dimension and Filter -> Apply New Filter...
      • Select Filter by Measure and set Selected Dates Products > 0 then click on OK

    2. Compare measure from two selected dates

      • Add two date pickers to the canvas and rename them to Date1 and Date2
      • Create a new Pivot and disconnect it from the date pickers
      • Add some dimension on the pivot and a measure you want to compare
      • Edit the Measure using placeholders i.e. (Measure, [@Date1]) then add the measure again and edit it i.e. (Measure, [@Date2]
      • Now you can start selecting different dates in the date pickers and see both measures changing according to the selected dates
      • Note: you can also combine placeholders: (Measure, [@Date1])/(Measure, [@Date2]) resulting the change between two selected dates
    Attached here Prism document and ECdata files (x32 & x64) that illustrates the placeholders functionality.
    Posted Oct 20 2010, 06:28 PM by Adi with no comments
    Filed under:
  • How many days from sale to sale

    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.

  • Sum of Max

    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.

  • Period to Date Calculations

    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

  • Exception Highlighting / Conditional Formatting in the Pivot

    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.

     

  • Converting seconds field to HH:MM:SS format in ElastiCube

    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!

     

  • Saving Aliens, Web Analytics and Business Intelligence

    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.

  • A Look at the Business Intelligence Index

    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

     

  • We're featured by Forrester in their Business Intelligence for a tough economic climate report.

    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

    Posted Feb 24 2009, 10:37 PM by Adi with no comments
    Filed under:
More Posts Next page »