Jump to content

Table definitions and ERD diagrams


Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

  • 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.

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...