Sswaroop Posted February 23, 2021 Posted February 23, 2021 Hi all, I'm totally new to invision and need help understand the data storage and connection options. We are using Forums and pages from Invision line of products. Our hosting company loads the invision data into MYSQL database on AWS. There are nearly 100 tables. We use powerBI as a reporting tool for our org. We want to connect our powerBI with the invision data for reporting. Below are some of the reporting use cases: Top 10 posts as per comments/reactions/voteups Trending last month/year Top 10 active users I need to understand which tables holds the required data for analysis. I have created a support ticket but didn't receive any helpful output. Is there any document/article that describes the tables and data that is held in them and also is there any ERD that shows how the tables are connected together.
CoffeeCake Posted February 23, 2021 Posted February 23, 2021 No, not that is publicly available. However, the table names and columns aren't too difficult to sort out, and if you have access to the source code, you can fill in the blanks, especially where some value represents something internal. The only tricky bit is that "nexus" somehow means "commerce." Some of your use cases already exist within the ACP reporting, so you can reverse engineer some of these asks from what IPS is doing. You'll need to define what each of your use cases means and then translate that appropriately. You'll also want to create a reporting instance of your MySQL database so that your analytics aren't impacting transactional queries on your live site. Your other option is to explore using the API, and try reporting from there.
Sswaroop Posted February 23, 2021 Author Posted February 23, 2021 Thank you so much Paul. I don't have access to source code. I'll check with my hosting company. Thanks for advising on the reporting instance as well.
CoffeeCake Posted February 23, 2021 Posted February 23, 2021 4 hours ago, Sswaroop said: Our hosting company loads the invision data into MYSQL database on AWS. I think I missed this. That kind of sounds like they created a reporting instance for you (meaning, that the database on AWS that you're pointing PowerBI to isn't the database that's actually running your community's web site). You'll want to verify this. Confirm with them that the copy you have access to is a snapshot or replicated copy of the one running your community. Otherwise, it's very likely that your work in PowerBI will slow down your web site. In an ideal world, you'd have a separate copy updated with some regularity that your requirements dictate (daily, hourly, instantly via replication), that you could do statistical and reporting work on without impacting the performance for users actively on your site. 6 minutes ago, Sswaroop said: I don't have access to source code. Are you a Community in the Cloud customer or is this a self-hosted install?
bfarber Posted February 23, 2021 Posted February 23, 2021 Top 10 posts as per comments/reactions/voteups These are different metrics, so are you looking to create three different reports (in this case)? "Posts" won't have comments (unless you are looking at quotes of the post, but this isn't something easily query-able). For "voteups" are you referring to the ability in Q&A forums to vote a post up/down? Trending last month/year What is "trending" to you? You'll need to define this more specifically. Top 10 active users How do you determine "active"? Most recently logged in to the community? Most number of posts? Most reactions, most followers? You get what I mean. First you'll need to clearly outline the definitions for what you want to report on and then I'm sure someone can help point in a more specific direction.
CoffeeCake Posted February 23, 2021 Posted February 23, 2021 Yup! Translate your asks into measurable, quantifiable metrics! @bfarber points this out beautifully. You'll want to end up with formulas, and define every term in your ask explicitly. This will be harder than finding the right column. 3 hours ago, Paul E. said: You'll need to define what each of your use cases means and then translate that appropriately.
Sswaroop Posted February 24, 2021 Author Posted February 24, 2021 Thank you @bfarber. The ideas here was to see if there's any data definition document. I just used some general cases to help understand the question better. We are still in the design phase now. Once we develop the exact questions I will post them with more information.
Sswaroop Posted February 24, 2021 Author Posted February 24, 2021 17 hours ago, Paul E. said: Are you a Community in the Cloud customer or is this a self-hosted install? I will verify if there is a separate instance for community with my hosting company. We are a self hosted customer.
Recommended Posts