Use Special Characters in Jet Analytics
Overview
For databases that use special characters (e.g., Asian characters) it will be necessary to make modifications to the standard Jet Analytics project in order for the characters to be displayed properly in the data warehouse and staging databases.
Setting up the Assembly and Function
-
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
- Execute the new query
- Make sure the database context is set to your stage database.
- Copy the SQL script below (Step 11) and paste it into a new query window.
- 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
- Launch Jet Data Manager and open your project.
-
Scroll down to Data Sources, right click 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. -
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. -
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". - In your stage database, create a new custom table named “Parameters”.
- Create a custom field in the table called “SourceCodePage” with the data type set to “Integer”.
- Create a custom field in the table called “TargetCodePage” with the data type set to “Integer”.
- Create a custom field in the table called “SourceDatabase” with the data type set to “Text” with the default text length.
- Right-click Parameters table and click Advanced > Custom Data.
- 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.
-
For each text field in the staging database that needs to be converted you will need to right click 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
- 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.
- Right click the field and select “Edit Field”.
- Check the “Unicode” checkbox and click OK.
- Deploy and execute the project.