When preparing a spreadsheet to be used with the software it is important that it conforms to the correct format and can be found in the predefined work files location. Failure to do so may result in an inability to locate the data or to represent it properly within the dashboard platform.
Defining the Spreadsheet Location
The dashboard looks for Excel spreadsheets in a single location, defined within the dashboard server configuration file normally found at:
C:\Program Files\Intuitive Business Intelligence\Intuitive Dashboard\...Server\Service\Dashboard.Config
The setting is found in the <appsettings> section and is called workfiles_path. If your implementation uses the default settings then it will have been set to c:\intuitive_workfiles\ during the installation process.
The reason for using a single location rather than multiple is so that the application is less susceptible to changes within the folder structure of networked fileshares, and is therefore more stable. Normally the location will be set to a folder on the dashboard Windows server, and that folder will be made available to users using Windows network configuration. In a distributed or heterogeneous environment it is possible to make the folder available using (S)FTP.
In order to make the location accessible to business users, the network administrator can (a) share the location on the dashboard server onto the network, granting access to the appropriate users or.. (b) choose a location within an existing network fileshare and configure the dashboard software to read from that location by altering the dashboard.config file mentioned above.
Defining the Spreadsheet Format
The dashboard software reads any worksheet within any Excel document that conforms to the standard definition of a database table - ie a square table with column headers in the first row. If in doubt, examine the demo spreadsheets distributed with the Intuitive software and normally found in c:\intuitive_workfiles\. Other formats may not load correctly. It is also possible to load from a named range provided that range also conforms.
One of the disadvantages of using a spreadsheet to store and transfer data is that there are no explicit definitions of the datatypes of the columns. Therefore the dashboard infers the datatype by inspecting the first 20 entries in each column. This could result in incorrect typing eg; if the first 20 entries were all numeric then at row 1020 a string appeared, row 1020 would be rejected by the loader with a typing error. For this reason it might make sense to sort the spreadsheet appropriately before loading.
The double inverted comma (“”) is considered by most computer systems as a blank string, however in our experience most users of spreadsheets are entering this value (from a formula..) as an alternative to NULL, which does not exist in Excel. Therefore the dashboard translates “” from a spreadsheet to NULL which results in better typing and the correct calculation of COUNT DISTINCT totals.
Intuitive Dashboards uses XML as a transport mechanism within its web services. Because of this, certain characters are not permissible within column headers and will cause load failure. In particular it would be best to avoid non-alphanumeric characters such as % $ < { % @ etc. Use the same rules you would apply when naming a column within a database table in SQL Server or Oracle. Also the dashboard does not permit duplicate column names.
Once the spreadsheet has been correctly configured and saved to the appropriate file location it is then possible to create the connection to the software using the steps detailed in creating a connection.
Defining the Spreadsheet Location
The dashboard looks for Excel spreadsheets in a single location, defined within the dashboard server configuration file normally found at:
C:\Program Files\Intuitive Business Intelligence\Intuitive Dashboard\...Server\Service\Dashboard.Config
The setting is found in the <appsettings> section and is called workfiles_path. If your implementation uses the default settings then it will have been set to c:\intuitive_workfiles\ during the installation process.
The reason for using a single location rather than multiple is so that the application is less susceptible to changes within the folder structure of networked fileshares, and is therefore more stable. Normally the location will be set to a folder on the dashboard Windows server, and that folder will be made available to users using Windows network configuration. In a distributed or heterogeneous environment it is possible to make the folder available using (S)FTP.
In order to make the location accessible to business users, the network administrator can (a) share the location on the dashboard server onto the network, granting access to the appropriate users or.. (b) choose a location within an existing network fileshare and configure the dashboard software to read from that location by altering the dashboard.config file mentioned above.
Defining the Spreadsheet Format
The dashboard software reads any worksheet within any Excel document that conforms to the standard definition of a database table - ie a square table with column headers in the first row. If in doubt, examine the demo spreadsheets distributed with the Intuitive software and normally found in c:\intuitive_workfiles\. Other formats may not load correctly. It is also possible to load from a named range provided that range also conforms.
One of the disadvantages of using a spreadsheet to store and transfer data is that there are no explicit definitions of the datatypes of the columns. Therefore the dashboard infers the datatype by inspecting the first 20 entries in each column. This could result in incorrect typing eg; if the first 20 entries were all numeric then at row 1020 a string appeared, row 1020 would be rejected by the loader with a typing error. For this reason it might make sense to sort the spreadsheet appropriately before loading.
The double inverted comma (“”) is considered by most computer systems as a blank string, however in our experience most users of spreadsheets are entering this value (from a formula..) as an alternative to NULL, which does not exist in Excel. Therefore the dashboard translates “” from a spreadsheet to NULL which results in better typing and the correct calculation of COUNT DISTINCT totals.
Intuitive Dashboards uses XML as a transport mechanism within its web services. Because of this, certain characters are not permissible within column headers and will cause load failure. In particular it would be best to avoid non-alphanumeric characters such as % $ < { % @ etc. Use the same rules you would apply when naming a column within a database table in SQL Server or Oracle. Also the dashboard does not permit duplicate column names.
Once the spreadsheet has been correctly configured and saved to the appropriate file location it is then possible to create the connection to the software using the steps detailed in creating a connection.