Data is the heart of analytics. In Vista, that means data from your Vista SQL database. PDS provides predefined views, functions, and procedures, making much of that data easy to access. However, there are times when your data needs (what you need and the way you need it) are more involved. If you’re database-savvy, there’s always a way to get to it—but, as a general rule, you should avoid making your analytics queries too complicated. It makes them difficult to understand, more prone to issues, and harder to maintain.
So, here are a few tips to simplify your Vista Analytics data access (SQL).
- If you’re not sure whether or not there is a Vista database view that meets your needs, check with your PDS representative before attacking it yourself.
- If you wish to collect and analyze historical data (i.e., data collected from different points in time), then consider creating Vista KPIs to automatically collect that data. (This greatly simplifies your analytic based on that data.)
- If you wish to bring back data into cross-tabulations (PIVOTs), then use the Vista Analytics “Transform-SQL” syntax.
- Similarly, you would also use the Vista Analytics “Transform-SQL” syntax if you wish to limit your results to a “top N” with a bucket for “all others.”
- If you write your own SQL for an analytic and that SQL ends up being more than a few lines, consider moving your query into a function. Then, query the function in your analytic.
Marco Padovani
Senior Development Manager | PDS
mpadovani@pdssoftware.com