MDM Tables
Q:I want to know what are the backend database and tables name where MDM repository data is stored and how to read them.
My intention is to write MS access query to perform a search to retrieve material parts as it is very difficult to do so through front end.(Data manager)
As in front end i need to search one part at a time through free form search.
I am on MDM 5.5 and i use SQL server as my Database.
My intention is to write MS access query to perform a search to retrieve material parts as it is very difficult to do so through front end.(Data manager)
As in front end i need to search one part at a time through free form search.
I am on MDM 5.5 and i use SQL server as my Database.
Ans1:The backend database name for a MDM repository is derived from the repository name (with non-alphanumneric characters removed. A repository named "products" would have an database called "products_M000".
Ans2:
The backend database name for a MDM repository is derived from the repository name (with non-alphanumneric characters removed. A repository named "products" would have an database called "products_M000".
Ans3
Each repository on your DB server will get stored in Databases Folder . Repo can be identified by RepositoryName_m000.
Under this folder tree, 2 folders A2i_CM_Tables and A2i_CM_Fields exist which store the table and field names.
You can read these DB tables from below mentioned options.
1.MS Access > Connect to SQL Source > Define DB Server name > Select Repository name>Choose the table
2. Use SQL Server Enterprise manager > Navigate to required table (table names mentioned above).
Under this folder tree, 2 folders A2i_CM_Tables and A2i_CM_Fields exist which store the table and field names.
You can read these DB tables from below mentioned options.
1.MS Access > Connect to SQL Source > Define DB Server name > Select Repository name>Choose the table
2. Use SQL Server Enterprise manager > Navigate to required table (table names mentioned above).
Ans4:
It is very difficult to get the entire Table data because there would be few tables corresponding to one MDM table in database hence i will suggest you to try with Expression (part of FreeForm Search) which allows you to write expressions similar to validations and in addition you can use other search parameters of free form search as well.
If you want you can share what kind of search you are looking for, so that we can try with Data Manager only.
If you want you can share what kind of search you are looking for, so that we can try with Data Manager only.
Ans5:
My requirement is I want to search multiple material numbers.
The material numbers in my data model is a text field and so i m not sure how to run a query to find out say if 100 mat exist in mdm ?
If you can think of a feasible expression plz help.
Vinay,
As per you,i should not face any major problem to using Access to run this query at the database level.
If so ill be happy to use your suggestion.But plz tell me on which database table should I query and how to get the above result.
If i query on the A2i-CM_tables will i get the extract of the 100 mat i want to search.
-------
The material numbers in my data model is a text field and so i m not sure how to run a query to find out say if 100 mat exist in mdm ?
If you can think of a feasible expression plz help.
Vinay,
As per you,i should not face any major problem to using Access to run this query at the database level.
If so ill be happy to use your suggestion.But plz tell me on which database table should I query and how to get the above result.
If i query on the A2i-CM_tables will i get the extract of the 100 mat i want to search.
-------
Say you want to search whether Material Numbers 100, 300, 500 and 900 exists or not then your Expression would be
Material Number = 100 OR Material Number = 300 OR Material Number = 500 OR Material Number = 900
Click on Browse button of Expression field present under Free Form Search and select the Material Number field from the Fields dropdown (don't write it)
Material Number = 100 OR Material Number = 300 OR Material Number = 500 OR Material Number = 900
Click on Browse button of Expression field present under Free Form Search and select the Material Number field from the Fields dropdown (don't write it)
-----------
Thanks for the reply..
ill def try this option..but i am really interested in knowing how to query this frm the database.
As it is really convient to use these MS Access.
ill def try this option..but i am really interested in knowing how to query this frm the database.
As it is really convient to use these MS Access.
------------
Please clarify
Will I be able to see the Main table(products) and the main tables records under the A2i_CM_Tables in the backend database?
If yes,then can i query on this table directly from MS Access and what are the complexities in doing this if any?
Will I be able to see the Main table(products) and the main tables records under the A2i_CM_Tables in the backend database?
If yes,then can i query on this table directly from MS Access and what are the complexities in doing this if any?
-----------
A2i_CM_Tables contains all the tables in Repository . Note the table name or Table ID. Eg.Main table name - Products
A2i_CM_Fields contains all the fields in a given table. Select the table name/id to get the list of all fields in the table .
Note the Field name or field Id that you are looking for .Eq. Product Name.
Write a simple query using above table and field names to search field values.
Hope this helps.
A2i_CM_Fields contains all the fields in a given table. Select the table name/id to get the list of all fields in the table .
Note the Field name or field Id that you are looking for .Eq. Product Name.
Write a simple query using above table and field names to search field values.
Hope this helps.
