Microsoft sql server MDS

Documents and links related to implement Microsoft MDS

Checklists/Points to be aware off-
Make sure the version of DB and MDS are same.
You can download excel plugin from MDS web url.
Make sure all the required IIS programs are added.
Package file cannot be deployed using MDS-UI if the package has data.
To deploy package with data, use MDSModelDeploy.exe tool (accessed via command line utility)
To deploy package without data you could use create/deploy option at MDS-web UI.

This is a good course to get an idea –
https://www.udemy.com/sql-server-master-data-services-master-data-management/learn/v4/questions/6408682

Master Data Services Installation and Configuration
https://docs.microsoft.com/en-us/sql/master-data-services/master-data-services-installation-and-configuration?view=sql-server-2017#InstallMDS

Uninstall and Remove Master Data Services
https://docs.microsoft.com/en-us/sql/sql-server/install/uninstall-an-existing-instance-of-sql-server-setup?view=sql-server-2017

Master Data Services Developer Documentation
https://docs.microsoft.com/en-us/sql/master-data-services/develop/master-data-services-developer-documentation?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/tutorials/enterprise-information-management-using-ssis-mds-and-dqs-together-%5Btutorial%5D?view=sql-server-2014

https://docs.microsoft.com/en-us/sql/master-data-services/master-data-services-overview-mds?view=sql-server-2017

 

 

 

float to nvarchar in sql server

when you need to select from one column of datatype float(which has phone number) and insert it in another column as nvarchar- cast wont work

cast : cast([Column_Name] as nvarchar(50)). The result will be in format 9.07235e+009 instead of a 10 digit number (phone number).

use SELECT STR([Column_Name],10,0) ** This is SQL Server solution, for other servers check their docs

Recursive SQL query to find the parent record or first and last or latest records

When we have a scenario to pick up first and  last record/hierarchy from a table which maintains history of changes(EmployeeHistory_Table).

 

Hierarchy_Table

role hierarchies
Trainee_System_Engineer
System_Engineer
Senior_Software_Eng
Team_Leader
Manager
Senior_Exec

 

EmployeeHistory_Table

emp_name current_role new_role
Dan Trainee_System_Engineer System_Engineer
Dan System_Engineer Senior_Software_Eng
Dan Senior_Software_Eng Manager
Tom System_Engineer Senior_Software_Eng
Tom Senior_Software_Eng Team_Leader
Jack System_Engineer Team_Leader
Jack Team_Leader Manager
Jack Manager Senior_Exec

 

expected output:

emp_name started_role current_role
Jack System_Engineer Trainee_System_Engineer
Tom System_Engineer Team_Leader
Dan Trainee_System_Engineer Manager

 

below is the sql for this kind of scenario:

CREATE TABLE #emp_RoleChanges (
emp_name VARCHAR(30),
current_role VARCHAR(30) ,
new_role VARCHAR(30)
);

INSERT INTO #emp_RoleChanges (emp_name , current_role , new_role )
VALUES
(‘Dan’, ‘Trainee_System_Engineer’, ‘System_Engineer’),
(‘Dan’, ‘System_Engineer’, ‘Senior_Software_Eng’),
(‘Dan’, ‘Senior_Software_Eng’, ‘Manager’),
(‘Tom’, ‘System_Engineer’, ‘Senior_Software_Eng’),
(‘Tom’, ‘Senior_Software_Eng’, ‘Team_Leader’),
(‘Jack’, ‘System_Engineer’, ‘Team_Leader’),
(‘Jack’, ‘Team_Leader’, ‘Manager’),
(‘Jack’, ‘Manager’, ‘Trainee_System_Engineer’)
;

select * from #emp_RoleChanges
;
WITH q AS (
SELECT 1 AS LEVEL, emp_name , current_role , new_role
FROM #emp_RoleChanges
UNION ALL
SELECT LEVEL + 1, q.emp_name, q.current_role, u.new_role
FROM q
INNER JOIN #emp_RoleChanges u ON u.current_role = q.new_role and u.emp_name = q.emp_name
)
–select * from q
–order by 2,1

SELECT –q.LEVEL,
q.emp_name , q.current_role started_role, q.new_role current_role
FROM q
where q.level = (select max(p.level) from q p where p.emp_name = q.emp_name)