Skip to main content

Posts

Showing posts from February, 2014

Personalized Data for In-DB Connectors

Applying row level security for in db connectors (Pre 6.5) for 6.5 onwards check this  articles  http://spotfiretipsntricks.blogspot.com/2014/12/personalized-in-db-connectors-for.html In  6.5 this is part of out of the box functionality to pass in username, group and domain  By default InDB Connectors can ask for username password from the user and that cna be used to apply row level security. However certain times, you would want to keep using the service account to query the database, but still apply row level security while using IN-DB queries Though InDB connectors directly do not support passing in  %CURRENT_USER%  parameter, we can still apply row level security using a simple personalized information link and loading the in-db connector as a ondemand table. This article will walk thru the steps needed to do so. Lets assume each spotfire user has access to some states data and we have  a lookup table which maps ...

HTML Text area and supporting all tags

With the new text area in 6.0 it is possible to have rich html layouts and using javascript and libraries like jquery you can quickly build a great User Experience However by default in the new text area all html tags are not allowed. This is a precautionary step and basically only allows html tags that are considered safe. Though I do not have a list of tags that are allowed or not, in case where you really need to use some of the disallowed tags you can allow so. Please use this option carefully, since there may be security implications to this. Some starting article about HTML Sanitization http://en.wikipedia.org/wiki/HTML_sanitization But here are the steps needed to prevent sanitization check You need to be an administrator to do so. 1. Start the Administration Manager (Tools menu > Administration Manager). 2. Select the Preferences tab. 3. Select a group on the left pane. Use the Everyone group to set this for all users. 4. Click the Edit button in the l...

Sorting on different values Dynamically

Sometimes you need to sort charts based on different values rather than metrics the chart is showing. For e.g You could be showing total sales for territories, but you really want to sort the results by Average Discount offered per territory , or may be average sales per for territory. Or may be you want to give the user the flexibility to choose how he wants to sort. Spotfire's DenseRank function and property controls comes in real handy here. In my example I mentioned above, I will introduced a DocumentProperty SortBy and use expressions to set the value. The Property si exposed as a drop down controls in the text area. The four expressions I use are DenseRank(Avg([Gross Profit]) over ([Territory])) DenseRank(Sum([Gross Profit]) over ([Territory])) DenseRank(Avg([Discount]) over ([Territory])) DenseRank(Sum([Discount]) over ([Territory])) And the on the chart I introduced the property on the category axis as $sortby So my Axis Expression looks l...

Spotfire Information Link caching & Scheduled updates caching

Spotfire Information Links and the entire Information Modelling layer in Spotfire is a great way to democratize your data. Information links become great starting point for your users to build their analytics without even knowing SQL or where the server is, what table to use and how are they joined  and all the geeky stuff.  In a typical instance there is a high likelihood that some of the information links are shared between different reports. With new versions of Spotfire now there is an option available to cache Individual Information links. The data behind the information link is cached at the Spotfire Server level. The cache is a Spotfire specific binary file which is stored on the Hard Drive of your Spotfire server. So you need to ensure that if you are caching there is enough space.  The cache itself is self cleaning depending on timeouts and validation query. Also a Spotfire server Restart would clear up the cache, so you need to accommodate f...

Spotfire UI Checklist

Below are screen shots of the same analysis, One before I went thru my checklist and one after.. A basic checklist I always go thru before I call it a day on any analysis ..I am sure all of you seasoned Spotfire users do this all the time..but keeping it here to help new Spotfire users ...... I would love to hear other things you do to make your analytic application a great user experience...  Lead to action : Analytics and fancy pictures are useless if user does not know what are possible action items for him at the end. Keep that as a goal. Action can be inferred in a single chart or may need series of charts. Understand goals of every chart, every workflow. Simple things can take you a long way 1)  Legends   Legends are powerful. They bring context to the chart you are looking at. But lot of times authors leave it to default and they show information that may not help. Simple cleanup will prevent an author from overwhelming the user with unw...

Spotfire Geo Capabilities Using your browser geo location

With the new rich HTML text area in Spotfire it is possible to get the user location while the user is looking at the analysis in a consumer or business author mode (basically in a browser that supports location) With the geo location you could now create a calculated column which calculates distance between you and your datapoints like stores, wells, clinics, etc Spotfire 6.0 has a new function GreatCircleDistance() that makes this easy. E.g.: DistanceInMiles = 3959 * GreatCircleDistance(${AppGeoLatitude},${AppGeoLongitude},[Latitude],[Longitude]) This is especially great for people in the field and are interested in analysing data around them geographically. To get this feature in the regular browser we will do following series of steps 1) (${AppGeoLatitude} and ${AppGeoLongitude} are two reserved properties that can be used by Ipad app to set the location to. So for the purpose of this exercise we will use the same properties, so go ahead and define them in your analysis. Th...

Using a custom sort order for values in a column

Lot of times data in columns needs to be sorted in a particular order. For e,g you bring a string column which has the month name. Since it is string data type, Spotfire would sort it in order of alphabetical order..so April comes before March and so on To fix this issue is very simple and Spotfire has inbuilt setting to define a custom sort order. The steps are pretty simple 1) From you professional client go to Edit >> Column Properties 2)  Select the table and column of interest 3) You will see a Tab for Sort Order for your selected column 4) Select the Radio Button that says "Custom Sort Order' 5) The configure button then gets enabled. 6) Click on the configure button and then arrange your world your way!!!

Spotfire Writing back to a database

Lot of time users need to write back to a database. Spotfire provides different ways to do this, Without going thru the code I am going to talk thru the different options available. But if you need detailed instructions, please leave some comments and will try to provide one 1) Stored Procedures. Advantages : Complex logic is easy to encapuslate in procedures. Also you are not storing username/passwords anywhere  Spotfire can execute stored procedures and these procedures could be fetching data, or can be procedures that can be run Pre or Post running a query. You can then use procedures that basically update your database table or even inserts new rows in tables. When using Spotfire information model it allows you to use these procedures which you can repurpose for updating your database. Spotfire also understands inputs to a procedure and they can be mapped to your marked rows/filtered rows/ properties/constants etc from your analytics. Steps to do that would be ...

Using Accordions in Spotfire Text Areas

Spotfire 6.0 provides great tools for quickly building your dashboard, but here we will learn to spice up things with the help of new 6.0 text area. If you are not aware the new 6.0 Text area supports HTML, Javascript and CSS. This opens up possibilities for doing a lot more with the text area. Some of the basic stuff can be seen here http://spotfire.tibco.com/qrt/UBSAN/presentation.html?pcode=100044   One of the cool things you can do is build an accordion panel in the text area for better utilization of the valuable screen real estate and helping the overall experience.(Watch the 0:15 second video below to see how accordion works - Sorry for the jitter in the video. The screen recorder does not record fast enough) Here is the html behind the text area : Please note that Spotfire controls are automatically created when you add text area controls, like filters, drop down lists etc. <DIV id="myaccordion"> <H3>Analysis Details</...