Exporting Your Data - What Are You Doing?

All,

So, I do not live on RAMP.  However, I know that about ½ of the community does. 

I know that John Jakovich understands this set of issues. In conversation with him, I believe that he gets this issue and one of the reasons that Sisence was chosen is that it would work in a RAMP (web) environment.  J  Yes, waiting is going to be a challenge.

I’m lucky to have Tessitura onsite and the ability to directly connect analytic solutions directly to Tessi Server, Test Servers, and T-Stat Servers.  (Yes, maybe I am spoiled.)

If I was in a Ramp Situation I would be looking at standard reports like the transaction reports, that does very little aggregation on the data.  The Order export utility.  The NCOA Export utility and I would take really wide swaths at the data. I do not know if there are size limits on the files you might be able to download from the RAMP environment.   And do my analysis from there.  I’d also be investigating the OData interface to SSAS reports, and the REST API server as ways to programmatically get to bulk data.

For items that you might want to report on, and where you can connect the thing to the Customer, I would likely be looking at the Execute an Output set option, and creating a bunch of special Query Elements to get the data I will need.  Some of this data is not the “tidiest”. But, I suspect that you could find a way to make it work.

One of the nice things about M (the language under Power Query) is that it can, in a reproducible way, rip into CSV file and import data into the data model inside of Power BI, or the data model in MS Excel 2013 and above. The Power Query user interface can do about 80% of what you might want.  For the other 20% there are lots of tutorials online that could get you through some of the harder times.  Power Query should make the labor or importing the data a bit easier.  While we wait for the Tessitura Business Analytics Solution.  I can show some of my ideas and some of the things that I have already done if folks are interested.

In the R world this same kind of approach could be done with a local SQLlite or postgress SQL database as the local repository for analytic data.  If I was trying to do this, I would then be looking at the tidyverse utilities. (Dplyr, DBI, odbc, tidyr, liquidate, ggplot2) as the tools to manipulate and do exploratory data analysis.

I’d really like to continue the conversation.  See if we can get some repos setup with best practices so we don’t all have to reinvent all of these things.  I’ve been considering doing a Virtual TUG around this Advanced Analytics group on TessituraNetwork.com.  Do folks want to get together for a call and talk through some strategies?  And see how we can help one another.  If so does anyone want to help out?

--Tom

…

718.724.8135

tbrown@BAM.org

P.S. The price point of many of the data analytics tools have been a big roadblock for me as well.

From: Self-service Business Intelligence [mailto:groups-selfservicebi@tessituranetwork.com] On Behalf Of Andrew Gerritsen
Sent: Wednesday, May 03, 2017 5:56 PM
To: Thomas Brown <tbrown@bam.org>
Subject: Re: [Self-service Business Intelligence] Exporting Your Data - What Are You Doing?

Summer,

As a RAMP user I'm still working out the best way to get more detailed data to use - not being able to connect PowerBI or PowerQuery presents some challenges.  If aggrgated data is what you are looking for, I find T-Stats is usually the easiest way and can export into Excel.  The problem is when you need disaggregated data.  My usual process for getting data to analyze is to write a query that compiles as much as possible in one go.  If the dataset ends up not being too large I can copy and paste the grid into excel directly from SSMS.  This is kind of a low-tech solution but works reasonably well.

As a long term project that I work on when there is nothing else pressing, I am trying to build a data model in PowerBI that I can use for tackling all sorts of projects.  I think I can get it to encompass most things I would reasonably need with around 30-40 standard tables and views.  The next step is to come up with a good way of simplifying the process of updating the data that I use on site - this is what I haven't quite figured out.  I want to avoid just copying tables or views after taking any information that can be used to identify people out of it.  I think there might a way to drop each section into a csv that I can use to update the reference files.

I would love to hear from anyone that has successfully integrated PowerBI using RAMP.  I was hoping the new analytics portion of Tessitura would be out soon enough that I could just use that, but I guess I have to wait a bit longer.  At least once I also teach myself R, PowerBI can handle that as well.

-Andrew