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

Brian,

I’m so glad to hear that you have worked this out.  I know that going from “On Premises” to RAMP hosted Tessitura meant a bunch of changes for you. 

When you say Power BI what parts of Power BI are you using.

·         MS Excel (Power Query, Power Pivot)

·         Power BI Desktop

·         MS Power BI web site

Would love to hear more about some of your successes…

--Tom

…

718.724.8135

tbrown@BAM.org

From: Self-service Business Intelligence [mailto:groups-selfservicebi@tessituranetwork.com] On Behalf Of Brian Ramos
Sent: Thursday, May 11, 2017 4:34 PM
To: Thomas Brown <tbrown@bam.org>
Subject: Re: [Self-service Business Intelligence] Exporting Your Data - What Are You Doing?

We at Opera Philadelphia have started using PowerBI with great success! We are also on RAMP, and it took us many month to finally work out a way with the RAMP Team to push sql view data to an Azure Database that we connect to PowerBI. So we have just under 40 views that are scheduled to be pushed to Azure every morning. We are still working with Tessitura to figure out the best method to easily update the views if we want to add views or additional fields, but they are actively working on a solution for us to do that. Currently, if we need to make any of these changes, it requires us working with the consulting team and the RAMP team to update scripts and files.

I'm more than willing to share our views if you are interested in them.

We are using all three aspects of Power BI. I’ve created a pretty robust data model in PowerBI Desktop, which I publish to PowerBI.com for Report/Dashboard development and consumption by the organization. The Azure database is accessible to anyone in the organization to connect to in Excel as well provided they have the db username and password. That way, they can create Excel based stuff separate from the PowerBI Data Model. They can also connect to the Data Model in Excel via the Analyze in Excel functionality on PowerBI.com.

Brian,

I’m so glad to hear that you have worked this out.  I know that going from “On Premises” to RAMP hosted Tessitura meant a bunch of changes for you. 

When you say Power BI what parts of Power BI are you using.

• MS Excel (Power Query, Power Pivot)
• Power BI Desktop
• MS Power BI web site

Would love to hear more about some of your successes…

--Tom

…

718.724.8135

tbrown@BAM.org

From: Self-service Business Intelligence [mailto:groups-selfservicebi@tessituranetwork.com] On Behalf Of Brian Ramos
Sent: Thursday, May 11, 2017 4:34 PM
To: Thomas Brown <tbrown@bam.org>
Subject: Re: [Self-service Business Intelligence] Exporting Your Data - What Are You Doing?

We at Opera Philadelphia have started using PowerBI with great success! We are also on RAMP, and it took us many month to finally work out a way with the RAMP Team to push sql view data to an Azure Database that we connect to PowerBI. So we have just under 40 views that are scheduled to be pushed to Azure every morning. We are still working with Tessitura to figure out the best method to easily update the views if we want to add views or additional fields, but they are actively working on a solution for us to do that. Currently, if we need to make any of these changes, it requires us working with the consulting team and the RAMP team to update scripts and files.

I'm more than willing to share our views if you are interested in them.

Excellent.

Will you be showing some of this at conference this year?

Are there other opportunities when I can see this in some more detail?

We are using all three aspects of Power BI. I’ve created a pretty robust data model in PowerBI Desktop, which I publish to PowerBI.com for Report/Dashboard development and consumption by the organization. The Azure database is accessible to anyone in the organization to connect to in Excel as well provided they have the db username and password. That way, they can create Excel based stuff separate from the PowerBI Data Model. They can also connect to the Data Model in Excel via the Analyze in Excel functionality on PowerBI.com.

Brian,

I’m so glad to hear that you have worked this out.  I know that going from “On Premises” to RAMP hosted Tessitura meant a bunch of changes for you. 

When you say Power BI what parts of Power BI are you using.

• MS Excel (Power Query, Power Pivot)
• Power BI Desktop
• MS Power BI web site

Would love to hear more about some of your successes…

--Tom

…

718.724.8135

tbrown@BAM.org

From: Self-service Business Intelligence [mailto:groups-selfservicebi@tessituranetwork.com] On Behalf Of Brian Ramos
Sent: Thursday, May 11, 2017 4:34 PM
To: Thomas Brown <tbrown@bam.org>
Subject: Re: [Self-service Business Intelligence] Exporting Your Data - What Are You Doing?

We at Opera Philadelphia have started using PowerBI with great success! We are also on RAMP, and it took us many month to finally work out a way with the RAMP Team to push sql view data to an Azure Database that we connect to PowerBI. So we have just under 40 views that are scheduled to be pushed to Azure every morning. We are still working with Tessitura to figure out the best method to easily update the views if we want to add views or additional fields, but they are actively working on a solution for us to do that. Currently, if we need to make any of these changes, it requires us working with the consulting team and the RAMP team to update scripts and files.

I'm more than willing to share our views if you are interested in them.

I am presenting in the BI Faceoff session, so I’m sure you’ll see some of this in action there.

I’m also more than willing to get a group together at conference or elsewhere to go through our setup and how everything fits together.

Excellent.

Will you be showing some of this at conference this year?

Are there other opportunities when I can see this in some more detail?

We are using all three aspects of Power BI. I’ve created a pretty robust data model in PowerBI Desktop, which I publish to PowerBI.com for Report/Dashboard development and consumption by the organization. The Azure database is accessible to anyone in the organization to connect to in Excel as well provided they have the db username and password. That way, they can create Excel based stuff separate from the PowerBI Data Model. They can also connect to the Data Model in Excel via the Analyze in Excel functionality on PowerBI.com.

Brian,

I’m so glad to hear that you have worked this out.  I know that going from “On Premises” to RAMP hosted Tessitura meant a bunch of changes for you. 

When you say Power BI what parts of Power BI are you using.

• MS Excel (Power Query, Power Pivot)
• Power BI Desktop
• MS Power BI web site

Would love to hear more about some of your successes…

--Tom

…

718.724.8135

tbrown@BAM.org

From: Self-service Business Intelligence [mailto:groups-selfservicebi@tessituranetwork.com] On Behalf Of Brian Ramos
Sent: Thursday, May 11, 2017 4:34 PM
To: Thomas Brown <tbrown@bam.org>
Subject: Re: [Self-service Business Intelligence] Exporting Your Data - What Are You Doing?

We at Opera Philadelphia have started using PowerBI with great success! We are also on RAMP, and it took us many month to finally work out a way with the RAMP Team to push sql view data to an Azure Database that we connect to PowerBI. So we have just under 40 views that are scheduled to be pushed to Azure every morning. We are still working with Tessitura to figure out the best method to easily update the views if we want to add views or additional fields, but they are actively working on a solution for us to do that. Currently, if we need to make any of these changes, it requires us working with the consulting team and the RAMP team to update scripts and files.

I'm more than willing to share our views if you are interested in them.

One of the great places at TLCC 2017 for this type of conversation will be at the Larger than ever open space discussions sessions.  I would love see you or someone host a Tessitura in A Power BI session.  Particularly considering that you have solved this for the RAMP world.  Please consider offering such a discussion.  And anyone else on the group.  This will be a great place for discussions for advanced and niche BI conversations.  Would love to hear about ideas folks might have for other types of conversations.

I am presenting in the BI Faceoff session, so I’m sure you’ll see some of this in action there.

I’m also more than willing to get a group together at conference or elsewhere to go through our setup and how everything fits together.

Excellent.

Will you be showing some of this at conference this year?

Are there other opportunities when I can see this in some more detail?

We are using all three aspects of Power BI. I’ve created a pretty robust data model in PowerBI Desktop, which I publish to PowerBI.com for Report/Dashboard development and consumption by the organization. The Azure database is accessible to anyone in the organization to connect to in Excel as well provided they have the db username and password. That way, they can create Excel based stuff separate from the PowerBI Data Model. They can also connect to the Data Model in Excel via the Analyze in Excel functionality on PowerBI.com.

Brian,

I’m so glad to hear that you have worked this out.  I know that going from “On Premises” to RAMP hosted Tessitura meant a bunch of changes for you. 

When you say Power BI what parts of Power BI are you using.

• MS Excel (Power Query, Power Pivot)
• Power BI Desktop
• MS Power BI web site

Would love to hear more about some of your successes…

--Tom

…

718.724.8135

tbrown@BAM.org

From: Self-service Business Intelligence [mailto:groups-selfservicebi@tessituranetwork.com] On Behalf Of Brian Ramos
Sent: Thursday, May 11, 2017 4:34 PM
To: Thomas Brown <tbrown@bam.org>
Subject: Re: [Self-service Business Intelligence] Exporting Your Data - What Are You Doing?

We at Opera Philadelphia have started using PowerBI with great success! We are also on RAMP, and it took us many month to finally work out a way with the RAMP Team to push sql view data to an Azure Database that we connect to PowerBI. So we have just under 40 views that are scheduled to be pushed to Azure every morning. We are still working with Tessitura to figure out the best method to easily update the views if we want to add views or additional fields, but they are actively working on a solution for us to do that. Currently, if we need to make any of these changes, it requires us working with the consulting team and the RAMP team to update scripts and files.

I'm more than willing to share our views if you are interested in them.