LNP Model generation using Data Guru

Contents

SET UP 3

Work Flow for Model Refresh: 9

High Level steps to model refresh: 9

Detailed steps: 10

SQL Transform for Detach operation: 10

File Copy Transform: 12

File Rename Transform: 13

Delete File Transform: 14

SQL Operation – Attach Model 15

Synchronize Logic Control: 17

SET UP

Create a new Data Guru project with all database connections set to SQL2008R2 (My workspace included).

Key is to have a LNP model built in SQL 2008 version (In order to run the model using LNP tool)

Have a backup of LNP model and LNPModel.mdf in the .\Templates folder for the project created

Connect to SQL Server Console to get the user instance name for SQL 2008 R2

Now go to SQL Server Management Studio (SMSS)

In the dropdown choose database engine option.

The following dialog appears.

In the Server name paste the pipe name value captured using SQL Server Console as indicated in the red box below.

Note that the pipe name varies from machine to machine

Once the connection to User instance is established as seen the image below

Right click on Databases à Attach

Click on Add and navigate to the location to the .MDF file in the .\Templates folder

Attach the backup to the .\Templates location in the DG project subfolders

Once the model is attached refresh the Databases in Object Browser to see the LNPModel as one of them.

Now create a new SQL database connection in Data Guru. Use the pipe name for the server name to connect the database that was attached previously. As indicated the image to follow.

Work Flow for Model Refresh:

High Level steps to model refresh:

o  Detach LNPModel from the pipe named server

o  Copy the model from .\Templates to .\Outputs folder using a File Transform Action

o  Rename the copied over file in the .\Outputs folder to a different name using File Rename Transform

o  Delete the log files (ldf) for both the .\Templates folder (only need the first time) and also for the renamed files on subsequent runs

o  Now reattach the mdf file from the .\Outputs folder to pipe named user instance

o  Use a synchronize logic control to allow the database engine to make the newly attached database ready for use before running any actions on the database

Detailed steps:

First the detach operation is a SQL transform with a connection to the My Workspace database.

SQL Transform for Detach operation:

SQL operation – Detach Model pic 1

SQL Operation – Detach Model pic 2

File Copy Transform:

File Rename Transform:

File moved to the .\Outputs folder is now renamed

Delete File Transform:

Delete the log files for both the first time ever run. In the image below #2 will only be needed the first time around. As the macro is run repeatedly #1 will be needed.

SQL Operation – Attach Model

SQL Operation – Attach Model pic 1

SQL Operation – Attach Model pic 2

Synchronize Logic Control:

‘A must use action’ after executing Attach DB script. This helps the DB engine process the command and ensures the attached database is ready for use. This is to be executed on My Workspace database as well

Generate LNP Model in DG / Page 18 of 18 / 5/22/2015 6:13:34 PM