Hey folks!
Sadly, I was unable to attend the conference this year but one of my coworkers heard some rumblings about Power Query while she was there. She came back and told a few people about it and now I'm getting a lot of push from certain people to get it up and running here asap!
I've downloaded in onto my own computer now and have done very minimal testing of it. I already see some issues and I'm wondering how other organizations, that are already using it, have addressed them.
1) Do you have special user(s) created for power query's purposes that only have access to limited tables? Obviously, connecting to the database as I normally would I have access to ALL the tables. Not only do I have concerns about what data people are accessing (do I really want someone in marketing to have full access to a contributions table?) but I worry that people will easily misinterpret data from pulling from the "wrong" table. Any of you that deal with the SQL database know that sometimes pulling data from one table won't give you the info you're looking for! The number of tables in Tess can be really overwhelming if you don't know what you're doing with them and how to find the right ones.
2) Do you only allow certain people to have access to Power Query for Tessitura's purposes? If yes, who has access and what are their comfort levels with accessing the correct database information? Is it only people with database experience or do you train key people on certain areas that pertain to their needs? If no, have you had issues with the more inexperienced users creating queries incorrectly, misinterpreting things, or accessing info they shouldn't be accessing?
3) In general, how are you using it? What kinds of things do you find it easier to use Power Query for? Do you have any specials tips or gotcha's that I may find helpful?
Thanks!
Beth
Beth,
Let me see if I can work through your questions.
Q: Do you have special user(s) created for power query's purposes that only have access to limited tables?
A: Yes, We have AD controls that exposes only a few database views (not tables) to those who need the views. The data views are demoralized and digested to a point that fewer mistakes are possible. That said. Anyone can make the wrong interpretation of the data even of "standard" reports. Therefor Documentation and Training are needed. However this approach does allow for much greater flexibility for your "advanced" users who you don't necessarily want to have SSMS access to your entire system.
Q: Do you only allow certain people to have access to Power Query for Tessitura's purposes?
A: Yes, As said above. There are secure views LVS_****** that de-normalize data like Contributions, Membership, Ticket History, Customers. Some organizations in the Network provide some of their most advanced users access to some of the most daunting structures like Transactions. From which they can do things that are not really possible in any other way, even SSMS, T-Stats, RMA.
Q: Is it only people with database experience or do you train key people on certain areas that pertain to their needs
A: We are working with trusted staff who are not DBAs. Most have shown skills in data manipulation and evaluation. We are not teaching SQL. This is for folks for whom standard reports do not cut it. Folks who are working on combining data from a number of sources. (Often in Excel already.) We are trying to get out of their way, without creating yet another custom report. Often in places where Output sets are very clumsy.
Q: If no, have you had issues with the more inexperienced users creating queries incorrectly,
A: Yes of course even our advanced user can make mistakes. However we help staff by trying to give them the core data structures de-normalized. At a unit of analysis that they can understand. Many times these folks are better at seeing data problems than an IT person. Because, they have been involved in data entry. Many times they will self adjust and start using the system more appropriately because they can now see the value of putting the data in "correctly."
Q: misinterpreting things,
A: Yes, However some users do the same things with standard reports as well. We try to work with the "Trusted Terrys" in our organization. This is only given to folks who already understand the data. However, List, Extractions, T-Stats, RMA, Standard Reports, are getting in the way.
Q: or accessing info they shouldn't be accessing?
A: This is our job as IT professionals to handle. AD Security and Secure Views seem to be doing a fairly good job in this area. (More testing is needed in a consortia model I work for a single licence organization.)
Q: In general, how are you using it? What kinds of things do you find it easier to use Power Query for? Do you have any specials tips or gotcha's that I may find helpful?
A: Lot of things.
1. One of our earliest examples was a view to support the creation of Sales Curves to Budget before the RMA had sales curves. And last I checked it still does not have sales curves vs budget numbers.
2. Our Gala Data is presented in a custom view to our special events department. This one view is used for all sorts of things. Seating Cards, Envelopes, Budgets, Proofing. And likely a bunch of items that I have no idea about. The view was created by Tessitrura staff with our input, so I'm fairly comfortable that the values are correct. When letters have to be sent out in the new way for the current gala, the staff does a mail merge into whatever template they need. The point is we are trying to get out of the way of those who are expert on the galas. (Our Trusted Terrys in the Organization. Giving them the data they need. And create incentives to put the right data into the database correctly so that they and others can get the correct answers now and in the future.)
3. A different view is used to pull membership. This information was used to pull and create membership records that were being given to existing members when we created a new membership program.
4. With a ticket history view we are currently working on a predictive model for Sales in order to get our holds correct. This can now be done by someone expert in Statistics and who has access to advanced tools like SPSS without giving access to the whole database.
This data can also be used in a variety of other tools like Tableau, CliqVeiw, PowerQuery is just the least expensive and most readily available tool. That many of our staff are fairly close to being ready to use.
We are actively experimenting with other views and other tools other than Power Query. Which will lead to outcomes that I can not imagine today.
We should probable do a phone call with a variety of folks who are interested in this topic.