Si Blog

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.

If you’ve dug around the product and/or the online documentation, you must have encountered the filtering and segmentation features Prism offers.   If not, this is a good time to get acquainted with this powerful feature here.

The first advantage of a Prism filter is that it allows you to filter your dimensions to focus only on the members that interest you, such as your best customers in terms of sales, products with a positive selling growth, the 20% of customers that account for 80% of your revenue, etc.

These filters are saved in your repository (under the corresponding dimension’s Filters node in the data browser) and always return the members that fit the filter’s definition at any specific point in time.  Requesting results for the “Best Customers in terms of Sales” today, may not yield the same results tomorrow because the best customers changed during the night.

 

 

So, what can you do with these filters once you create them?  This short article will show you how you can use the same filter to achieve multiple business goals, without having to put in any extra work.
 

 

1. Calculate Filter Member Values or - "Show me the total sales for each one of my best customers"

Placing the filter on the Rows (or Columns) panel with one or measures, will calculate the values of the measure(s) for each of the members returned by the filter. The filter returns Customers B, C, H, L and T which are the best 5 customers in terms of sales.  The following pivot calculates the Sales measure for each of these customers.

 

 

 

2. Calculate values in the context of a filter or - "Show me how much sales my best customers produced in each of my store cities"

Placing the filter on the background panel will present the pivot values for the filter members only.  The following pivot will show the total sales just for customers belonging to the filter for each store city.  Note that since the filter is placed on the background, it is calculated for all stores, not for a specific store.

 

 

3. Calculate filter for members individually or - "Show me the best customers for each one of my store cities, and their sales"

The same 'Best 5 Customers' filter we created can be used to calculate the best 5 customers for each city individually.  If you place this filter immediately after the Store City dimension, Prism will behave like this automatically.  The following pivot shows the best 5 customers in terms of sales for each store city individually.  You will notice that this filter returns different customers for each city.

 

 

 

4. Using a Filter to Scope a Measure or - "Show me the total sales of each store city's best customers, without showing me the list of customers"

My Excel file has a 'Sales' measure.  I would like to create a new Measure that is called 'Sales for Best Customers' that I can use again and again in my reports.  If you have yet to explore Prism's custom measures capabilities, now would be a good time to learn.

 

Prism introduces a concept called 'Measured Values'.  In simple terms, they are expressions that include a measure (or custom measure) and a scope in which to calculate.  Measured Values contain a single measure and (at most) a single representative from each available dimension.  They are defined by enclosing the list of measures and representatives in parentheses while separating each Measured Value element with commas.

 
The following formula is created by dropping the 'Sales' measure and 'Best 5 Customers (Sales)' filter onto the formula editor, and adding parentheses and a comma.  This tells Prism to calculate the Sales measure in the context of the Best 5 Customers filter. We'll call this custom measure 'Sales Of Best Customers'.

 

Placing this new formula on a pivot with the Store City dimension will yield the total sales of best customers for each store individually.  This is because the filter within the formula still works within the context of the current members on the pivot.  If you add subtotals to the pivot in section 3 you will notice that the values of this new pivot match the values of the subtotals.
 

 

 

 

5. Aggregating a Filter or - "Show me the average sales for each store city's best customers"

Filters can also be aggregated over a measure using one of the defined aggregation functions - Sum, Avg, Min, Max, Count, DupCount.  What aggregation functions do is calculate the value of a measure for each member in the filter and then aggregates all the values according to the specified function.

The following formula calculates the average sales to 'Best 5 Customers (Sales)':

 

Placing this formula on a pivot with the Store City dimension will yield the average sales to the best 5 customers in each city.

 

1. 'Businesses I paid the most to' - This pivot table shows me the businesses I paid the most to altogether, and in how many transactions.
2. 'Most expensive single transactions' - Unlike the previous Pivot, this one only takes single transactions into consideration.
3. Monthly gas expenses - This chart shows me my monthly expenses on fuel. I can also see my average yearly expenses on gas below the chart.
   To create this chart I created a 'Filter by Label' that filtered out only businesses with names of gas companies.
4. Yearly analysis - whenever I choose a different year, I can see my average monthly expenses for that year, and the average number of transactions I do with my credit card.
5. Spending or Saving? Whenever I choose a different month on the main chart, I get a green light if I'm saving, a red light if I'm spending, or a yellow light if I'm neutral. How do I decide? The traffic light widget uses a measure I created, that compares the selected month's expenses to the average expenses of the last 12 months (relative to the selected month). If I spent over 20% above the average, then I'm spending, if over 20% below, then I'm saving, and if my spendings are in-between the +/- 20% range, then I'm neutral. This traffic light gives me a quick indication on my spending during that month.

Analyzing personal expenses has never been easier.

 

 

 

We've just released a private beta for Amazon S3 users. The idea is that if you use Amazon's Simple Storage Service (that's where the S3 comes from) you get information about what you're using, why and how much it costs you.

 

However, all the S3 information comes in as a log, but what if you'd like to show your boss a dashboard that looks more like Google Analytics?. (you can't use Google Analytics here - since you can't insert an html tag into the S3 parts of your site).  That information is worth quite some $$ if you can use it to optimize the way you work with Amazon S3. For instance, you can compress a file, or find out that a lot of files are addressed from a lesser wanted source, and change something about the way your website works.

 

The SiSense team has given this some serious thought for the following reasons.

• First of all, we're Amazon S3 users ourselves.
• Second, as a bunch of guys developing business intelligence software, we've become obsessed with analyzing stuff
• Third - our goal is to connect as many data sources as possible to our software.

 

A note on the data sources: we try to connect to any valuable store of data:

• Excel 2003
• OLAP sources
• Oracle
• SQL
• SQL analysis services
• MySQL
• We're about to add csv files, excel 2007 and other goodies I can't tell you about yet...

 

So we configured a custom S3 dashboard, using our software (which allows you to create any dashboard). This saves the Amazon S3 user all the work. You just download our software with the pre-configured S3 dashboard, connect it to Amazon S3 - and, voila - it works. It shows you anything you'd like to know about your S3 use, and has already, for our data, generated some real efficiencies.  Just don't take too long a time period the first time around because the data transfer might be slow.

 

And we're offering this, free of charge, as a private beta accessible here. If you'd like to join the private beta email us here

 

Update:  Jason Kincaid just covered our Amazon S3 Dashboard release in TechcrunchIT.

 

One day I was browsing around Amazon looking for a book about some boring technical stuff (diving into the company pocket is always a good way to start a lazy morning). 

When I finally found the book I was looking for, I immediately got a list of products who were bought by other customers who also bought my book (try saying that three times fast).  Amazon have had this feature for as long as I can remember (it's called Collaborative Filtering), but being a bit of a data freak - that feature was always extra cool to me.

Now coming back from fantasy land - from a Business Intelligence perspective, this reminded me of Basket Analysis.  In simple terms, Basket Analysis is the idea of finding products that will yield more profit if they are sold together - like shampoo and conditioner.  Should they be sold together for maximum profit?  Or should they be sold separately?

I was wondering if it's possible to perform similar analysis with Prism and how long it would take me.  Soon enough, I had it up and running.  It looks something like the following image.  You can download the sample PSM file here: Basket Analysis.psm

This PSM document lets you select a product, and in turn see what products users (who bought this product) also bought.

-- The data --

This is the example table we're going to analyze.  It's pretty straightforward and describes daily product sales per customer.  

For my example, I used an Oracle table but you can download the data in Excel format and it will look and feel just the same: Basket Analysis.xls.  If you need help connecting to it, you can watch the Connecting To Data video that explains it.

-- The Prism Data Source --

After connecting to  the table, it looks like this:

 

-- Step 1: Add Widgets and Data --

This one's pretty easy. Just select the Product ID dimension and drag-n-drop it onto the canvas.  Repeat this action for the Customer ID dimension. Dropping a dimension (or filter) onto the canvas automatically creates a Drop-down list Widget. 

To create a pivot, you select the Pivot Widget Widgets Pane and drop it onto the canvas, or by right-clicking the canvas and navigating to the New menu item and selecting "Pivot".

To populate the pivot, select the Product ID dimension and drop it onto the Pivot's Rows axis in the Data Layout Editor.  Finally, select the Measures node and drop it onto the Columns axis - and click update.  If you wish, you can watch the Working With Pivots video to see how this is done.

-- Step 2: Add Background to Widgets --

Now here's the whole trick.  Prism's Background (or Scope) feature basically lets you connect one Widget to another to indicate that the results of the target Widget should be limited to the selection in the input Widget.  To activate the Background feature, just hover with the mouse of the Customer ID Drop-down Widget and click on the Background button (see image below).

 

Selecting Product ID and clicking Apply will cause the Customer ID Drop-down to contain only those Customer IDs that intersect with the selected members in the Product ID Drop-down.

Similarly, click on the pivot's Background button and select the Customer ID Drop-down as its background.

The rest is just text and some formatting!

Now you can select a product in the Product ID Drop-down and see a table with the products bought by others that also bought the selected product! 

Did I mention that as you add rows to this Excel file, the Prism document will be automatically updated to reflect this new data?

Have a great day,

Elad
SiSense

Ok, this one isn't from me, it's from one of our users who wanted to show us how he handled currency conversions with Prism.  Thank you!

I've simplified his scenario, just to get the main point across.  Note that this user was working with Excel files, but this example is just as valid for any data source with similar data.  Remember, as far as Prism is concerned, all data sources were created equal!

Imagine an Excel file with the following data in it:

 

What the user was trying to do is create a simple report that shows total sales for each product.  But the report had to be in Euros, while the data was received in US Dollars.  He was getting these figures on a daily basis, so he was looking for a way to create it in such a way that he only had to change the rate of conversion between Euros and US Dollars to update his report.  He sure wasn't about to create the same report again and again every day.

Calculating Sales in US Dollars

You probably noticed that the Excel sheet does not contain an explicit field for sales.  So the first thing he needed to do was create a Sales In US Dollars measure that is defined by the price of each individual product multiplied by its quantity.  This is easily done by creating a Calculated Measure (more about creating these can be found here):

Defining a Conversion Rate Measure

In order to convert the US Dollars value to Euros, he defined a conversion rate measure.  To do this, he created a Calculated Measure whose formula is the conversion rate (at the time of writing, this value was 0.64):

Calculating Sales in Euros

Finally, to create a Sales in Euros measure, he simply had to create a new Calculated Measure whose formula is as follows:

 

... and viola!  A Sales in Euros measure to use in his reports!  Changing the value of the Conversion Rate formula to the required conversation rate will make sure his report always shows accurate results, without having to repeat any of his work.

I'd like to thank this user again for this nice and useful example.

Elad
SiSense 

Prism dashboards often contain a Date Picker or a Drop-down List that affects the entire dashboard.  This is very useful when you wish to go back and forward in time, and see the figures for the selected time period(s).

Usually, when I refresh the entire dashboard, I wish to see the most recent data.  Meaning, having the dashboard present the information in terms of the most recent date available in the database. Instead of going back to the Drop-down List, scrolling down and selecting the last member - you can have Prism do this for you automatically.

Right clicking on the Drop-down list and navigating to the Preferences menu gives you quite a few interesting options to choose from.  One of which is the "Automatically Select" option (see image below).  Choosing "Last Member" will have Prism select the last member in the Drop-down List every time it is refreshed.  So when a new date comes in, it will be automatically selected and your dashboard will present the most recent data. 

You can use this option even when you don't wish the dashboard users (if it doesn't happen to be you) to change dates, but rather just see the most recent data.  In this case, what I do is just hide the Drop-down behind one of the other widgets.  That way, the dashboard presents the most recent data, and the dashboard consumer cannot change that selection. 

Elad
SiSense 

 

A typical dashboard or report has several widgets that require query execution in order to present up-to-date results.  Some widgets may need to be updated frequently, some on a daily basis, or on a weekly basis - and so on.

After you are done creating a dashboard, you can easily refresh it (by clicking F5 or clicking the Refresh button).  If you're information hungry like me, you find yourself doing that quite often.

If your dashboard is created over an Excel or CSV file, this is not really a problem.  But if it's over a database server, these refreshes can take a while - depending on the size of the tables and network traffic.

At some point I found myself staring at a dashboard with 56 widgets on it, each of them executing a different query against 3 different database servers down in our basement.  Taking into consideration that I am not the only one craving non-stop information in the organization and the fact that I didn't want to wait 10 minutes for my dashboard to refresh - I turned to re-organize my dashboard a little bit.

I realized that most of the widgets in my dashboards could be refreshed just once a day. There were about two groups of 5 widgets each I need to refresh at more frequent intervals.  So, I turned to the Widgets tab on the left of the screen, scrolled down until I reached the Actions section and dropped 3 "Refresh Widgets" action buttons onto the sheet.

 

 
A "Refresh Widgets" action is exactly what it sounds like.  It refreshes widgets.  Right clicking on it lets you configure which widgets will be refreshed once the button is pressed.

By adding three separate action buttons and configuring the widgets they refresh, I was able to control the amount of queries my dashboard generated every time I needed to see updated results.

Elad
SiSense

Hi everyone, 

Since the Web 2.0 boom, many of our users are companies that have websites which are the foundation of their business.  These users often use Prism to analyze user activity on their site.

The table(s) they use to perform this analysis usually hold a unique identifier for a user (the email, for example), the company they belong to, etc.  With Prism, It takes exactly 10 minutes to create a live dashboard that tells you, in real-time, exactly what you need to know about user activity.

However, a common problem these types of users usually encounter is that their table(s) contain a bunch of users that throw off their activity statistics - users created for QA purposes, fake users (users providing a fake email, for example), etc.  This scenario has a very simple and effective solution.  But before we get to it, I'll mention a few key features of Prism you should be aware of when tackling such a problem.

The Exclude Filter 

The "Exclude" filter is very simple.  It accepts a static list of members as well as results of other filters and returns all members that do not match these values.  For example, if I create an Exclude filter over the Company dimension and specify "SiSense", this filter will return all company values that are not SiSense.  We'll call this filter "Non-SiSense Companies".

Filtering By Label

Much like it sounds, the "By Label" filter returns members that match a text-based criteria.  Creating a By Label filter over the Email dimension with the criteria "Doesn't End With sisense.com" will return all emails that do not end with "sisense.com".  We'll call this filter "Non-SiSense emails".

Filtering By Attribute

The "By Attribute" filter is in a sense the exact opposite of the Exclude filter.  In simple terms, it returns all members that intersect with the filter's defined criteria.  For example, creating a Filter By Attribute over the Email dimension and specifying the "Non-SiSense Comanies" filter as its criteria will return all emails not associated with the company "SiSense".  We'll call this filter "Non-SiSense Company emails".

Now back to the problem at hand...

Ok, so let's take a look at what we have so far.  We have a filter that has only emails not associated with the company SiSense and another filter that has only emails that do not end with sisense.com.

You may ask yourself why I created these two filters that apparently return the same members.  Well, they will not necessarily return the same members.  It all depends on whether every person with a sisense.com email also entered SiSense as his or her company.

Regardless, whether we just pick one of these filters, or use an "Intersection" to combine them both into a single filter - let's call that final filter "Non-SiSense Users". 

Prism Filters are just as valid anywhere a dimension is valid.  This include on a pivot axes, in a formula, in a Drop-down List, etc.  So instead of using the Email dimension in my dashboard, I will use the "Non-SiSense Users" filter. This will instantly cause my dashboard to reflect data relevant to non-SiSense users only!

Using filters in this way has two significant advantages, apart from focusing on real data and filtering out the garbage:

1. This filters will always return up-to-date results.  If tomorrow a new SiSense employee is hired, he will automatically be added to this filter (either by his company or his email).
2. These filters can be modified at any time.  I can modify the "Non-SiSense Companies" filter to include the company "SiSense Ltd" as well, without redoing any of my work and have it automatically update all filters, formulas and widgets that use it.
   

And to top it all off - any modification I make to these filters will automatically be reflected in the dashboard I created!  I don't have to think of everything in advance, which at least for me is a very difficult task :-)

Elad
SiSense 

 
For more information about filters, click here. 

For more information about using filters in formulas, click here.

How does Prism decide what the data type of each field is when I connect to a data source?

This is a question that keeps popping up, usually because it has direct implications on the type of analysis you can later perform on these fields.  More specifically:

• Numeric Fields can act as regular dimensions as well as measures, with any type of aggregation (Sum, Avg, Min, Max, Count, DistinctCount)

• Textual Fields can act as regular dimensions as well as measures, limited to the Count and DistinctCount aggregations.

• Date/Time Fields can act as Time dimensions, Regular Dimensions and measures, limited to the Count and DistinctCount aggregations.

Identifying Database Table Fields

Most serious databases such as SQL Server, Oracle, MySQL etc provide detailed schema information about the tables they hold.  When connecting to these types of sources, Prism simply asks the database what data type each field is configured to.

Identifying OLAP Cube Fields

OLAP cubes are handled a little differently than other data sources as they are already structured within a dimensional model.  When connecting to an OLAP cube, Prism correlates a dimension for each cube dimension, and a measure for each cube measure.

Identifying Excel Range Fields

In the case of Excel, identifying field types is a little more tricky.  This is because the creator of the Excel file can mix up different types of values in different types of formats within the same field.

In order to determine the type of field, Prism will search for the first non-empty cell in the relevant fields (after skipping the title row) and evaluate two things:

1. The formatting applied to the cell.  If the cell is formatted in a way Prism recognizes (Date/Time formatting, currency, numeric value with two decimal places, etc), the field type will be determined according to that format.
2. If Prism does not recognize the applied formatting, it will try to figure out the data type according to the value itself.

This methodology is not 100% accurate, but it usually is.

However, since Prism will get confused sometimes (for example, if the first non-empty cell holds an irrelevant value that does not match in type with the rest of the field values, or if you have more than one title row), Prism allows you to define all types of dimensions/measures on any field in Excel.  If during the synchronization process (where Prism brings the Excel data into its own belly) a value that does not match the dimension/measure configuration is encountered - You can either fix this value, or skip the row it belongs to.

Elad
SiSense