Custom Data Options in Analytics

Hi Galen,

Thanks for these great questions. Regarding the custom fields, the same Custom 01 field could possibly hold, for example, a date value for one member of a consortium, and a text value that serves a wholly different purpose for another member of that consortium. This is enabled by the keyword and control group settings in Tessitura. In that scenario, it’s impossible to give the field a single, meaningful name. There are also technical challenges that arise when trying to support database column name changes from one Analytics data refresh to another. Since it is so easy to rename fields in the Analytics dashboards themselves, we delivered v15 Analytics with generically named “custom” fields for Contributions, Orders, and Plans. I’m sure we’ll revisit that at some point as we continue to enhance Analytics.

The Data tab in Analytics, available only to locally-hosted system administrators, will improve with future releases, but can currently be cumbersome, and is limited in its ability to customize cubes, when compared to the Elasticube Manager application. To license the ECM, please open a TASK request. For your specific Fund grouping use case, in a network configuration where the Analytics cube server has access to the production Tessitura database, pulling small doses of dimensional data from Tessitura directly into the cube and bypassing the warehouse ETL seems reasonable. I recommend you flatten the data in a SQL view in the Tessitura database and extract just that view rather than extracting several tables into the cube and flattening them in a custom cube query. Doing that work outside the cube is more aligned with the future cube customization work you referenced, and will transition more cleanly when that time comes.

One caveat I feel the need to share: the cubes are one giant inner join. If you find after adding a custom dimension that data in the cube goes missing, it is very likely due to not all values that exist on one side of your join also existing on the other side.

Best,
Chris

Hi Galen,

Thanks for these great questions. Regarding the custom fields, the same Custom 01 field could possibly hold, for example, a date value for one member of a consortium, and a text value that serves a wholly different purpose for another member of that consortium. This is enabled by the keyword and control group settings in Tessitura. In that scenario, it’s impossible to give the field a single, meaningful name. There are also technical challenges that arise when trying to support database column name changes from one Analytics data refresh to another. Since it is so easy to rename fields in the Analytics dashboards themselves, we delivered v15 Analytics with generically named “custom” fields for Contributions, Orders, and Plans. I’m sure we’ll revisit that at some point as we continue to enhance Analytics.

The Data tab in Analytics, available only to locally-hosted system administrators, will improve with future releases, but can currently be cumbersome, and is limited in its ability to customize cubes, when compared to the Elasticube Manager application. To license the ECM, please open a TASK request. For your specific Fund grouping use case, in a network configuration where the Analytics cube server has access to the production Tessitura database, pulling small doses of dimensional data from Tessitura directly into the cube and bypassing the warehouse ETL seems reasonable. I recommend you flatten the data in a SQL view in the Tessitura database and extract just that view rather than extracting several tables into the cube and flattening them in a custom cube query. Doing that work outside the cube is more aligned with the future cube customization work you referenced, and will transition more cleanly when that time comes.

One caveat I feel the need to share: the cubes are one giant inner join. If you find after adding a custom dimension that data in the cube goes missing, it is very likely due to not all values that exist on one side of your join also existing on the other side.

Best,
Chris

Thanks for the very helpful info. I guess the logical followup question is this: Which kinds of things are safe to customize, and which things will get wiped out during Full Loads or will cause problems during future version upgrades? It seems likely that changing the supplied views is a bad idea, and adding additional views is relatively safe. What about renaming columns by changing the queries in the ElastiCubes, as I'm now experimenting with for custom element names? What about adding custom calculated fields to the cubes?

I really think that supporting the names for custom fields is important, and shouldn't be too hard to do, even with the need to enable different names for different Divisions. For example, the load script could create separate tables for each Division, apply security to them, and join all of those tables to the main table. Then regular users would only have access to the table that belongs to their Division, and the column names could be set correctly by the script.

That's a very useful caveat about the inner join situation. Knowing that going in will probably save me a lot of grief somewhere down the line!

-Galen

Hi Galen,

The Analytics Warehouse has an upgrade mode as of 15.1, so customizations should be maintained upgrading to that version and beyond. However, v15.0 service packs only have install mode for the warehouse, and will likely result in customization loss without appropriate prep. You can be assured that for all versions, the standard BI views will be overwritten during upgrade. As will the standard Elasticubes.

Creating local BI views (bi.LVT_DW_%) is the safest way to manipulate the business logic behind the existing fields being extracted from Tessitura. Then by subbing out the [source] in TR_ANALYTICS_DATA for the row associated with the standard view, the updates will be reflected in the warehouse and cubes. After upgades, for any local BI views, review the release notes and perhaps the standard source views themselves for changes from the previous version, and if found, apply the customization to a fresh copy of the source view. If you're adding new columns I suggest that happen in a new source/table/view rather than by altering and expanding the columns of an existing one. It'll be easier to maintain in the long run.

Renaming those custom fields directly in the Elasticube is probably fine if you don't mind doing it every time. If you rename a column, then any dashboards that reference those fields will be broken after upgrade until you restore your naming exactly. Custom calculated fields would also need to be re-added after upgrade.

Also, Analytics doesn't currently support table/column level security at this time. Only data source and row level security.

Best,
Chris

Thanks, Chris! This is all extremely helpful.