we have been told by our provider that if they load a new table to the schema for datamodule they have to load all tables? Can they not load just one table?
They did show us they clear the ticks on all tables then tick one table and click load and when when they go to data module select the DW only this table shows and errors out
Is this a known issue or are they doing this incorrectly?
They're doing it incorrectly.
When new tables are added to a data base the source schema table list will add them with an unchecked checkbox. Tables which already exist in the schema will show up with their associated checkbox in a checked state. To add the new tables to the schema you need to select those tables that you want to add to the schema. You do not want to change the selection state of existing tables.
The actions that your provider did, by unchecking the existing tables' checkboxes, act to tell CA to remove those tables from the loaded schema metadata. The result is what you saw, where only your newly added table was visible.
I can't say that I've seen arrays of checkboxes where the behaviour is any different from what CA is doing.
I'm not sure what you mean by 'errors out'. The data module is opened and, because the first query subject in the module references a table which is no longer loaded into the schema, there's an error when the data grid tries to show data? Or, when you add the newly added table to the module and select it there's an error when the data grid tries to get data?
thank you it is an error because when you add the newly added table to the module and select it there's an error when the data grid tries to get data.
so their issue they believe is because they leave all the tables ticked then system reloads all the tables not just the new one. They cancelled the reload because it takes a long time to load and this caused a lot of issues for our data modules, because they cancelled half way. They are going to reboot and reload all tables this Sunday.
Is this correct system behaviour, reload all ticked tables not just the new one.
I am wondering to future proof this if we are going to have a lot of tables should we break them out into different schemas grouping by like tables? This may be a lot of for the adminstrators to maintain though.
QuoteIs this correct system behaviour, reload all ticked tables not just the new one.
Yes that's the behaviour.
I don't think setting up a series of different schemas is really the answer as any potential relationship that you would want between tables in different schemas would not be imported and would need to be created manually in the module. Apart from the extra work, it is possible that relationships manually-created in the module will have an effect on the SQL is eventually passed to your data base and be less efficient than using something which exists in the data base itself.
There's a trade off-- if your dw is still undergoing churn then you'll have to bite the bullet and accept the occasional hits to your DB when the schema is reloaded.
Part of the hit is the scanning of the tables during the sampling stage. Some people have suggested that sampling isn't worth the cost of the hits to the DB.
Keep in mind that the loading of the schema is not a frequent event.
many thanks this was extremely helpful :)