It may seem like a logical thing to do, but creating a view which has every dimension on the rows and all attributes into one single all-encompassing view is not only data intensive, it also does not leverage the business logic in TM1.

When you first install and begin to use TM1Connect, it may be tempting to want to create a single view on a cube that has every dimension on the rows along with all hierarchies and all attributes so that you can point your Tableau or Qlik users to the single data source and let them explore. Doing this, however, causes a number of problems:

1. This kind of view enables the users to request the entire contents of the cube, on demand, each and every time they open or refresh their report (and dump they will!)
2. A cube dump view does not pull data at an aggregate level, so all of the consolidation power of TM1 will be circumvented.
3. Much of the business logic, especially non-aggregate friendly measures such as Headcount or Average Price, will have to be re-created in the querying application.
4. Performance perceived by the end user will suffer and impact on TM1 read/write users may be significant.

::: tip Create and use views in TM1Connect the same way you would in TM1 when creating Excel/Websheet reports :::

TM1 (or any OLAP database) is great at displaying small amounts of data at varying degrees of granularity and with embedded business logic, it does not perform well at extracting large volumes of data in a relational -style format. Thus, the reason for TM1Connect. If you want to dive into the nitty gritty details as to why OLAP systems like TM1 inherently have this issue, please see our white paper Understanding TM1Connect Performance.

Constrain dynamic views to be at the right level

As a general rule, the all-encompassing view is only needed during the design phase of a report or dashboard. It is used to selectively choose the components for the report, but once the report is created, then it is oftentimes the case that the views used in the report, become static in nature. Therefore, consider creating subject matter views which pull only the data needed to service the reports, and do so at the right level of aggregation so that the business logic is not repeated in the reporting environment.

Would you create an all encompassing view that dumps all the cube data into an Excel and then use pivot tables, vlookups, and formulas to filter and display the charts? Certainly Not! So why would you want to subject your ODBC users to that same paradigm? Rather, pull from TM1 only the data that you need into the various "subject matter slices" and then bring those slices into your reports. The less data pulled from TM1, the faster it is, and you can better leverage your model in TM1.

::: tip Did you know that dimensions placed on the titles are still accessible in the WHERE clause of the SQL statement even though they don't show in the rows/columns? ...more... :::

Consider using globally cached views

If your users want data exploration capabilities with TM1 data, you may want to consider using a globally cached view. A globally cached view can be configured to pull the data once from TM1 and store it temporarily for all users requesting information from that view. Therefore, if repeated requests from the same or different users are encountered, TM1Connect can alleviate the burden of servicing those queries, especially large ones, from the TM1 server. In addition, cached views are significantly faster than TM1.

At the end of the day, a globally cached view still needs to pull data at the level of aggregation you select and tie up the TM1 server while it retrieves the data. The larger your cube dump, the longer it takes TM1 and TM1Connect to prepare the data. You may choose to load a globally cached view every 4 hours, so that your users get reasonably real-time data but only impact your TM1 read/write users infrequently.

Be aware that globally cached views are public and all data within those views can be seen by any and all users. Globally cached views may not be appropriate for sensitive data.

Consider using scheduled view extracts

Scheduled exports are another way to create data files from large cubes that can be extracted and loaded into the destination system. In the case of Qlik and Tableau, this extract can be loaded and refreshed nightly into their proprietary storage format for high-speed access to TM1 data. Once it is loaded into their server, it can be combined with information from other sources to assemble a proper dashboard. By using TM1Connect to create the exports, you can save yourself many hours of writing and testing TI scripts, especially ones that need to reformat the data into tabular layouts or ones that need to flatten dimensional hierarchies. These data extracts can then be changed quickly and easily by a simple drag and drop user interface in the TM1Connect Manager.

The flip side of this is that you are indeed performing a cube dump and the data is at the detailed granular level. This means business logic may have to be repeated for some metrics to calculate at consolidated levels. You may also be required to use the intrinsic security provided by these products rather than using TM1 security.