Refresh schema for all tables

[Tim]

Can we get a "refresh schema for all tables" button?

Reason:
Our tables are usually "Select * From AnalyticsSchema.ViewName". We control which fields to return by editing the view, not the Sisense table definition. When a field gets added/removed/changed, we need to refresh schema. That's fine to do manually as you're working on that datamodel+views, but we need to refresh all when:

• We copy a datamodel to a different server. We need to refresh schema at least to double-check that the views are as expected on the new server. (If any fields have changed, then I'll need to go fix any widgets using those fields, or, more likely, update the view to include them.)
• A view gets edited, perhaps for a different datamodel, and my datamodel hasn't been updated. 
• I edit several views and want to refresh schema for all those Sisense tables. If I've changed used fields then I'll need to go into each table manually anyway so it doesn't matter, but I've had a case where I've removed unused fields from several views and now I need to click refresh schema on every table individually.

[bobfroncko]

Our data model changes pretty frequently by design, and currently the nightly cube builds do not pick up on these changes.  We have to go in and manually refresh the schema in the UI.  Originally it was fine, but now as we approach hundreds of cubes deployed, manually updating the UI is not ideal...

Would love an option to allow the rebuild to also refresh the schema so we didn't have to continue to manually update the schemas.  Understand the risk associated with automated schema refreshes, but again this is by design for us so there would be no risk.

Thanks. 

[DRay]

Hello @Tim and @bobfroncko,

Can you take a look at this post and let us know if addresses your use-case? https://community.sisense.com/t5/knowledge/how-to-update-live-model-dataset-configuration-through-ap...

Thank you.

[Tim]

Hi DRay,

No luck, I'm afraid.

The post you mention describes how to change connection information, but not the table's schema. It mentions an "autoRefresh" property, but that's about whether the LiveModel re-loads data at regular intervals or on-demand, nothing to do with refresh schema.

Sounds like the API can edit a table's schema, but there isn't a "refresh schema" endpoint. So, we could build our own tool that uses the API, but it would need to connect to the database to find column names and datatypes, and then update tables with those. That's doable, but quite a bit of work.  Found this post that discusses that: https://community.sisense.com/t5/build-analytics/refresh-the-schema-to-a-table-through-the-api/m-p/5...

[Oleksandr_K]

 Hi, 

I would like to inform that since L2024.3 there is ability to refresh scheme for all tables of data model in 1 action. Feel free to try it out through 3 dots context menu in the top right corner of the data model. 

 

[DRay]

Hello @Tim,

Can you look at the option provided by @Oleksandr_K and let us know it addresses your request?

Thank you.

[Tim]

Cool! I will try it once we upgrade.

[lucasaaa49611]

I tried to refresh the schema for all the product tables on the Goodwill Outlets website, but it doesn't seem to be working. The changes I made to the database schema are not reflected on the product pages.

Possible Causes:

There might be a cache issue preventing the schema from refreshing properly.

The schema update process might not have been triggered or completed correctly.

The permissions on the database may not be set up correctly to allow schema updates

[LOV122333]

I am trying to refresh the game app schema for all tables, but after doing so, my page still shows the previous structure of the tables. The new columns and changes made to the schema are not showing up on the page.

Possible Causes:

There may be an issue with the table mapping, where the refreshed schema hasn’t been correctly applied to the relevant pages.

The table structure might not have been saved properly before attempting the refresh.

Permissions might be restricting the visibility of the updated schema to the user’s account.

[Lonaav2122]

Hi folks,
I’m trying to refresh the schema for all tables in my app’s database, but I’m unsure of the most efficient way to do it without causing data inconsistencies.

Is there a way to automate this safely for all tables without breaking the existing structure?

While working on the database, I’ve also been testing feature-rich apps like spotify premium apk, which rely heavily on seamless content syncing — something I’m aiming for too!

[Tim]

Hi @Lonaav2122

I usually create views in my database that return what I need in Sisense, then in Sisense just select from those, instead of selecting from tables or writing queries in Sisense.  Those views select columns explicitly, not *. You might like to also throw in cast-as-datatype statements so the datatype doesn't change unexpectedly.

That way, the columns returned change only when you do it deliberately, so when you refresh schema you already know whether columns are changing. If the underlying tables change, then your views return an error. You have to go point them at the new column names.

Using views also means you can re-use the same views in other data models, analytics tools, queries and views, and you can manage it all in SQL instead of in the data model. That does have the downside that if you want to change a view you have to find and test every data model that depends on it, but for us the upsides outweigh the downsides, and we use conventions, documentation, and automation to help.

It's best if you can query a database that doesn't keep changing columns. But that might not be practical. Are you querying views built for other reports that other people change when they modify their report? I've sometimes started a data model that way, but then copied their query into a new view for my report.