Hello everybody, 

Several users have inquired on how Prism decides what type the fields are in the data source they are connecting to,  since this ability is directly connected to the type of dimensions/measures that can be created over those fields.

Database Tables
Most database servers (SQL Server, Oracle, MySQL, etc) provide detailed schema information about the tables they hold.  When connecting to a database server, the types of fields are determined by the data types defined on the server.

Time dimensions can only be defined on DateTime/TimeStamp fields.

Regular dimensions can be defined on any type of field.

Measures with Sum/Avg/Min/Max aggregations may only be created over numeric fields.

Measures with Count/DistinctCount aggregations may be created over any type of field.
 

OLAP Cubes
When connecting to an OLAP cube, you only have two types of fields - dimensions and measures, as they appear in the cube.

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

Hope this helps,

Elad
 

Hi Elad,

I am trying to connect to an Excel worksheet where each field contains mainly numeric data, but also one row of text.
When I set up the data source, Prism identifies each field as a numeric field. I then get a 'Synchronization Error' when I try to connect to the data source. The error message is:

"Error converting value 'Red' to Floating Point."

Is there any way that I can display mixed data types in the same field in a table?

Hi,

In order to import Excel data into Prism, you cannot mix data types.

What I would do is change the value Red to -9999 (for example).  If you want this value to appear as Red in a pivot, you can rename it directly in the pivot by double clicking the -9999 member.

We will be addressing this inconvenience soon.

Elad