Special Characters in Jet Analytics

Overview

Setting up the Assembly and Function

1. Copy the following SQL script and past it into a new query window.
   

   -- Configure the server for CLR operation
   sp_configure 'clr enabled', 1
   GO
   reconfigure
   GO

   
   -- Drop objects
   IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ChangeEncoding]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
   DROP FUNCTION [dbo].[ChangeEncoding]
   GO
   IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'Jet.DataManager.Functions')
   DROP ASSEMBLY [Jet.DataManager.Functions]
   GO

   
   -- Import CLR assembly
   CREATE ASSEMBLY [Jet.DataManager.Functions]
   FROM 'C:\Program Files\Jet Data Manager Server\Jet.DataManager.Functions.dll'
   WITH PERMISSION_SET = SAFE
   GO

   
   -- Create function from assembly
   CREATE FUNCTION [dbo].[ChangeEncoding] (@Input nvarchar(4000), @SourceCodePage int, @TargetCodePage int)
   RETURNS nvarchar(4000)
   AS EXTERNAL NAME [Jet.DataManager.Functions].[Jet.DataManager.Functions.UserDefinedFunctions].[ChangeEncoding];
   GO

2. Execute the new query
3. Make sure the database context is set to your stage database.
4. Copy the SQL script below (Step 11) and paste it into a new query window.
5. If you have installed Jet Data Manager in the default path you can execute the query, otherwise you first need to update the file path to the DLL under the “Import CLR Assembly” section of the query.

Updating the Staging Database in the Jet Data Manager

1. Launch Jet Data Manager and open your project.

2. Scroll down to Data Sources, right click on the data source name (default: NAV), and select “Edit Microsoft SQL Provider”.

   If connecting to a NAV Native database, select "Edit Navision Native Database" instead.

3. Check the “Force Unicode Conversion” checkbox and click OK.

   If connecting to a NAV Native database, select the "Use Preset Settings" radio button and then select the appropriate version from the associated drop-down list.

4. Right click the data source name again and select “Synchronize Objects”.

   If this is the first time you are connecting to this data source, this will read as "Read Objects" instead of "Synchronize".
5. In your stage database, create a new custom table named “Parameters”.
6. Create a custom field in the table called “SourceCodePage” with the data type set to “Integer”.
7. Create a custom field in the table called “TargetCodePage” with the data type set to “Integer”.
8. Create a custom field in the table called “SourceDatabase” with the data type set to “Text” with the default text length.
9. Right click Parameters table and click Advanced > Custom Data.
10. Type in the Source Code Page and  Target Code Page for each NAV adapter that you have connected to the project. Type in the NAV adapter name (default: NAV) in the SourceDatabase field.

11. For each text field in the staging database that needs to be converted you will need to right click on the field name and select “Field Transformations”. For the Operator select “Custom” and click Add. Insert the following SQL statement:

    dbo.ChangeEncoding( [Name] , (SELECT SourceCodePage FROM Parameters_V WHERE SourceDatabase = [DW_SourceCode]), (SELECT TargetCodePage FROM Parameters_V WHERE SourceDatabase = [DW_SourceCode]))

    NOTE: You need to change the highlighted field name to the actual field name you are performing this transformation on.

Updating the Data Warehouse in the Jet Data Manager

1. For each text field that you have converted on the staging database you need to convert the field to Unicode on the Data Warehouse as well.
2. Right click the field and select “Edit Field”.
3. Check the “Unicode” checkbox and click OK.
4. Deploy and execute the project.