• Replies 6 replies
  • Subscribers 961 subscribers
  • Views 219 views
  • Users 0 members are here
Related

Correspondence between variables in Analytics and SQL Studio data

Carlo Palazzi
$organization

Hello All,

I am wondering if we can see how the variables in Analytics are populated - i.e. what data each variable available in dashboards is connecting to in the database visible in SQL Server Management Studio? It would be important to know to make sure that custom reports (from SQL stored procedures) are consistent with the Analytics reports. 

Any help will be greatly appreciated.

Thanks,

Carlo

  • $organization

    If you look at the widget options in the top right corner the icon furthest to the left (looks sort of like a scroll) is the widget data dictionary. This shows where it is pulled from in the widget, the cube, the data warehouse, and then Tessitura tables/views. Hopefully, this will help give some insight! 

  • $organization

    I know that the Tessitura analytics team is very interested in building trust when it comes to the numbers.

    I'm not clear if there is documentation that is exactly like what I think you are looking for.  However, 

    In the Tessitura Documentation, you can find written descriptions of all of the data elements.

    In addition in Tessitura Analytics, you can find a description of the values you are looking at in a specific dashboard widget.  To do this:

    Then you will see information like the following:

    Which I think is closer to what you are looking for.   You would then have to go and look at the Views under the Tessitura Column in SSMS to determine the exact sourse of the data and any manipulation that might be done to produce the values.

    That said, I don't know of a single document that fully covers all of the cubes in this level of detail.

    Hope this helps

  • $organization in reply to Madeline Dummerth (Past Member)

    Thanks Madeline! That was helpful. Unfortunately the tables that are indicated in the data dictionary (such as F_SEATS_AND_TICKETS) aren't available to us in SSMS

  • $organization in reply to Tom Brown (Past Member)

    Thank you Tom for the great reply. Unfortunately can't access the views shown in the data dictionary (such as duo.LOAD_SEATS_AND_TICKETS) with our remote implementation of Tessi. Do you have access to these? If so it would just be a permissions issue for me. 

  • $organization in reply to Carlo Palazzi

    &

    Inquiring minds want to know. Where do I find the ETL stored procedure dbo.LOAD_SEATS_AND_TICKETS, and function F_SEATS_AND_TICKETS that show up in the Data Dictionary?  I can not see them either in my RAMP implementation and would like to able to trace back values in analytics if necessary.  Is this possible? 

  • $organization in reply to Tom Brown (Past Member)

    Hi Tom,

    That stored procedure is in the TessituraAnalytics data warehouse, which is in a shared environment and therefore not directly accessible to members. There are several data sources in Tessitura that feed into that stored procedure, including...

    • BI.VT_DW_ORDER
    • BI.VT_DW_ORDER_DETAIL (for sales data in v15.0.x)
    • BI.VT_DW_PERFORMANCE (for pricing data in v15.0.x)
    • BI.VT_DW_ORDER_DETAIL_AT_PRICE_LAYER (for sales data in v15.1.x)
    • BI.VT_DW_PERFORMANCE_PRICING_AT_PRICE_LAYER (for pricing data in v15.1.x)
    • BI.VT_DW_PERFORMANCE_SEATING (for seating data)
    • BI.VT_DW_BENEVOLENT_SEAT (for donated seating data)

    The stored procedure then does quite a bit of transformation to get all the necessary pieces of related fact data into an appropriately denormlized format for the Seats and Tickets cube. Querying these views in your Tessitura can give you a sense of what's being extracted to the warehouse, and scripting the views out to review their SQL code can give you a sense of what source tables in Tessitura are being used to produce those extractions.

    Best,
    Chris