Поделиться через

Running more than one active configuration in BA 4.00

  • Статья

As promised at my session last week I would post a detailed walk through of how to be able to run more than one active configuration with BA in Navision 4.00. First of all I would like to state that this is not a thing that I'd recommend doing unless you are used to working with both SQL Server, Analysis Services and Data Transformation Services. For more info on those technologies - see my previous post on the components used in BA 4.00.

First we will move the Relational database created by BA to new one. This is necessary because BA always will create a database with the same name. If we do not complete this step the database will be overwritten when creating a new configuration. Navigate to Query Analyzer located in the SQL Server program group in the Start menu. there connect to the database server and execute the script below:

sp_renamedb 'BA for MBS - Navision 400', 'BAVendorDB'
go
sp_detach_db BAVendorDB
go
xp_cmdshell 'rename "c:\Program Files\Microsoft SQL Server\MSSQL\Data\BA for MBS - Navision 400_dat.mdf" BAvendordb_dat.mdf'
go
xp_cmdshell 'rename "c:\Program Files\Microsoft SQL Server\MSSQL\Data\BA for MBS - Navision 400_log.ldf" BAvendordb_log.ldf'
go
EXEC sp_attach_db @dbname = N'bavendordb',
@filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\bavendordb_dat.mdf',
@filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\bavendordb_log.ldf'
go

Note that this is specific to your installation and rights on the server. You will have to be a member of the sysadmin group on the database server to be able to execute this. Also note that there should be no users running on the database when doing this. Normally I'd recommend scripting the database and then edit the script and run it appropriately or backing up the database and restoring it with a new name. For the sake of the demo I did it this way which is easy but requires a specific setup to run.

When this is done the cubes should be copied and edited to point to the new database. Start by navigating to the Analysis Services administration tool. Here browse to the server where the Navision Cubes are placed.

Copy the database by right-clicking the database and chose copy

Right-click on the Server name and chose paste.

Since there is already a database of that name you are asked to choose a new one. Write an appropriate name for the configuration at hand. In this example it will be Vendor Database which is the name I will use for the rest of this post.

You now have an exact copy of the Analysis Services database. Expand the Data Source folder and right-click the data-source that is in it. We need to edit this to make it point to the new database that we just created named BAVendorDB.

Point to the server (in this case (local)) and chose BAVendorDB from the drop-down.

After this we have a new Analysis Services database pointing to the relational database BAVendorDB in SQL Server.

The next task is to alter the DTS packages created by BA. Open Enterprise Manager and browse to the Data Transformation Services folder. In Local Packages there are 4 packages that we will need to alter.

First open the package BA for MBS-Navision 400 - initialize. This will open the package in DTS Designer which is where DTS Packages can be edited.

Double click BA for MBS-Navision and create new connection called BA Vendor DB. Point the connection to the new database called BAVendorDB in the drop-down.

After this the task Create Company needs to be altered to point to the new connection

The task Truncate tables also needs to be pointed to the new connection.

Click the Package menu – chose Save As and choose a new name for the package. In this case I will call the package BA Vendor DB Initialize

Now open the package BA for MBS-Navision 400 - Transfer [CompanyName]. In this package we also need to create a new connection called BA Vendor DB. this step equals the one above.

When clicking OK a pop-up called Task References will appear. DTS has detected that several tasks are using the connection we just changed and asks if it should delete the mappings. This is not needed and clicking OK without selecting any transformations will preserve the transformations. We can do this since the databases are exact matches.

To make sure that all transformations are pointing to BA Vendor DB all connections called BA for MBS-Navision 400 should be changed. For each of them repeat the step about preserving all mappings in the tasks. Screen shots are below for each connection in the package.

Click the Package menu – chose Save As and choose a new name for the package. In this case I will call the package BA Vendor DB Transfer [CompanyName]

Now open the package BA for MBS-Navision 400 - Post Process. In this package we also need to create a new connection called BA Vendor DB. this step equals the one above.

Double-click the task Populate Posting Date. Point the connection to the connection created above called BA Vendor DB.

Double-click the task Update Posting Date. Point the connection to the connection created above called BA Vendor DB.

To be able to edit the connection settings for the task Quality Check we need to do a Disconnected Edit. In disconnected edit all properties can be edited with no check on the validity of the value. This means that one can really mess up in this and I cannot stress enough that you should not do this unless you know what you are doing. Doing the wrong things here can ruin the package for good. To enter Disconnected Edit right-click the Quality Check task and chose Disconnected Edit.

In Disconnected Edit locate under Tasks the Quality Check task double-click the ConnectionID property.

In the Edit Property box set the value to 2 and click OK.

Exit Disconnected Edit and double-click the Quality Check task. The connection name should now be BA Vendor DB.

Double-click the Update Statistics tasks and set the connection to BA Vendor DB

Click the Package menu – chose Save As and choose a new name for the package. In this case I will call the package BA Vendor DB Post Process

Now open the package BA for MBS-Navision 400 - Process Cubes. Double-click the Process Shared Dimensions task and point it to the dimensions in the Vendor Database.

Double-click the Process Cubes task and point it to Cubes in the Vendor Database

Click the Package menu – chose Save As and choose a new name for the package. In this case I will call the package BA Vendor DB Process Cubes

The next task is to alter the Agent Job created by BA. The job will enable you to schedule the job in running the packages at a given time of day/night.

Browse to Management and SQL Server Agent. Under Jobs you will find a job called Update Business Analytícs Database.

Double-click the job. Here you will see four steps representing each of the four packages mentioned above.

Edit the first step and change the name of the command to reflect the new name of the initialize package. In this case BA Vendor DB - Initialize

Alter the following steps accordingly to reflect the new names of the packages. Also note that you will have to give the job a new name to be able to coexist with other parallel configurations.

To run the job either schedule it or right-click and chose run from the menu.

Comments