MDS: Polulate 3 tables

Question

MDS: Polulate 3 tables

river1

SSC-Insane

Points: 24309
More actions
October 11, 2017 at 7:57 am

#329150

Dear all,
I have 3 tables in my MDS that I would like to populate with the informarmation (metadata) of one database.
Tables are:
                     stg.Databases_Leaf
                     stg.DatabaseEntities_Leaf
                     stg.DatabaseEntitiesAttributes_Leaf
respectively with the following fileds:                     Code; Name
                     Code; CodeDabases; Name
                     Code; CodeDatabaseEntities; Name; DataType; AllowNulls
The goal is:
For Database , to a DB name
For DatabaseEntities, to add all the user tables that this database has
For DatabaseEntitiesAttributes, To add all the attributes of each table
As business rules on the MDS side, I told:
    stg.Databases_Leaf - Both fields should not allow nulls and name must be unique
    stg.DatabaseEntities_Leaf - All the fields should not allow nulls. Fields CodeDabases; Name should form a unique constraint
    stg.DatabaseEntitiesAttributes_Leaf - Nulls mot allowed for Code; CodeDatabaseEntities; Name; DataType; AllowNulls. Fields CodeDatabaseEntities. and name should form a unique
Can someone help me understand How can I load this infromation into does tables?
I mean, for example, can I create a stored procedure that will only insert new data (no updates nor deletes)?
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 1

Where is this information coming from?

I am assuming that "Databases_Leaf" is a list of databases that will be coming from an external source and not the internal set of databases on that particular instance. If it is for that one particular instance, you are reinventing the wheel as sys.databases, sys.tables and sys.columns all hold that information already.

So, assuming that it is storing external data, I'd just design the table to enforce the constraints you have in place. For example, the table Databases_Leaf, I'd design it like:
CREATE TABLE [stg].[Databases_Leaf] ( Code INT IDENTITY(1,1) NOT NULL PRIMARY KEY, Name varchar(1024) NOT NULL UNIQUE )
and build the other tables in a similar manner. You didn't indicate the data type of Code, so I assumed it was an INT. I imagine that CodeDabases on DatabaseEntities_Leaf would be a foreign key to Databases_Leaf and you'd make a unique nonclustered index on CodeDabases and Name to handle the constraint (or do it as a constraint).
I wouldn't recommend using a stored procedure for inserts as that feels messy if you don't have the constraints in place.
For adding a multi column unique constraint, add:
CONSTRAINT CodeDabaseName_Unique UNIQUE(CodeDabases, Name)

That should get you going. Extrapolate the first one and the second note to build the table for [stg].[DatabaseEntities_Leaf] and build off of that to get [stg].[DatabaseEntitiesAttributes_Leaf]. If the tables already exist, you will need to either drop and recreate them OR alter them (ALTER TABLE <table name> ADD CONSTRAINT <constraint name> UNIQUE ( <column list> )).

Hope this helps 🙂

EDIT - also, for future posts, it is nicer if we have some DDL to work with. Seeing your current table creation scripts will make it easier to write things up that will hopefully be more helpful. For example, I was guessing that "Code" was an INT, it could be a GUID, a varchar, a char, a bit... I really have no idea. I'm just looking at it and thinking that that is my best guess. Having a "CREATE TABLE" script to see what you have currently would make this easier to help.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

river1 SSC-Insane Points: 24309 More actions · Answer 2

Hello,

Thank you for your reply. Yes. the columns were created according to what you have written. No need to change nothing from my side in relation to that.

The databases and all its metadata are internal, I need to add them into my MDS model because I want to keep history if someone deletes fields , databases or changes attributes.

My question was more, How can I populate the structure that you have stated above, knwing that this are MDS entities and not regular tables.

Should I use a stored procedure to populate it? What is the way to do it MDS?

thank you for the support.

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 3

Sorry, I skimmed over the MDS part of that. My bad.
Have you looked at the MSDN articles on MDS's (https://docs.microsoft.com/en-us/sql/master-data-services/overview-importing-data-from-tables-master-data-services)? That should help lead you in the right direction.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.