SQL View for Dimension

 The following SQL view can be used to extract dimension information using SQL 

1. DefaultDimensionView: for default dimension each dimension value is listed as a separate row. Hence, we  might need multiple joins for one each dimension to get values in a columnar format. 

2. DimensionAttributeValueCombination: For Ledger dimension. Each dimension would exist as a column in this view. 

Choose design patterns

 As you learn more and more design patterns it becomes increasingly difficult to understand which one to use in a given scenario. 

Below are some of the observations:

CoR Vs Decorator:

CoR (Chain of Responsibility): To be used when there is a sequence of activities to be performed in a given sequence. These activities and their sequence can change based on a given business scenario. Each activity is a done by a separate class in the chain and we can add new classes as and when they are discovered. 

Decorator: Initially I was quite confused between CoR and Decorator as both of them provided classes that can be added to a base object and these can be changed at runtime. I later realized that the difference between the two is that while Decorator is designed to change the behavior of the base object, CoR is designed to perform different activities with each class. Which means that if the given functionality has to be enhanced based on conditions then decorators should be used, and if different functionalities have to be triggered in a given sequence CoR should be used. 

Summary: Decorators are additive in nature and every decorator handles every message. This is the basic behavior of the pattern. Hence when a client wishes to enhance one or more additional behaviors decorator is used. Decorator would exhibit at least a minimum of two behaviors.

Chain of Responsibility will exhibit one behavior or less. The potential for multiple links to handle one message is possible but not the intention. Hence ideally the cardinality is zero to one so when a CoR pattern is being used the message should be processed by one chain.

Strategy Vs Visitor

Strategy: Involves a class objects being induced into a method as a parameter and this contains an algorithm to do a certain process in a certain way. By parameterizing the algorithm the functionality can be enhanced later to accommodate new algorithms. 

Visitor: Visitor involves the current object being passed as an argument to the visitor class method and later invoking a specific method in the visitor class which can then access the current objects and perform any desired operation. 

So while strategy is used to change the behaviour, visitor is used to add new behavior 

Sort X++ Map into a List

 Maps are auto sorted based on the key. Sometimes it might be required to get these values sorted and retrieved in a particular order. 

Below is a code snippet where a map is sorted and stored in a List where it can be retrieved in a sequential manner. The sorting of the map elements is done based on the value of each item, but the list is populated with the keys. 

       

	//Sorting of the values
        //add the sorted map values into a list
        mapEnumeratorParent = resourceWeights.getEnumerator();
        resourceList = new List( Types::String );
        
        while ( mapEnumeratorParent.moveNext() )        //iteration 1
        {           
            lowestResource = mapEnumeratorParent.currentKey();
            lowestWeight = mapEnumeratorParent.currentValue();

            mapEnumerator = resourceWeights.getEnumerator();
            while ( mapEnumerator.moveNext() )      //iteration 2
            {
                if ( mapEnumerator.currentValue() < lowestWeight )
                {
                    lowestResource = mapEnumerator.currentKey();
                    lowestWeight = mapEnumerator.currentValue();
                }
            }

            resourceList.addEnd( lowestResource);

            resourceWeights.remove( lowestResource ) ;
            mapEnumeratorParent = resourceWeights.getEnumerator();
        }

 

Extra parameters on Report

 I have created a class with 2 parameters however when the Dialog box is rendered there are 4 parameters displayed as shown below

So basically when the parameters are rendered these are done from the SSRS report. When we open the SSRS report parameters we can notice that these additional parameters are added to the report and these are the same parameters which are repeating. 

To solve the issue, we need to delete the unwanted parameters from the parameters list and redeploy the report. 

Find Hyper-V Host

 When lost between multiple Hyper Hosts and Images you can always find the host of a hyper-V image using the registry. 

The following powershell command can be used to read the registry 

Get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Virtual Machine\Guest\Parameters"  | Select-Object HostName

SQL remove invisible characters

You will need a combination of 3 functions 

1. PATINDEX: PATINDEX is used to find a position in a string where a specified character or pattern exists. In our chase we have specified all characters from ascii value 0 to 31, 127 and 255. 

2. SUBSTRING: Once the PATINDEX function return the location where one of the invisible characters is found, substring function extracts that 1 length string to isolate the invisible character. 

3. REPLACE: This function then replaces all occurrences of the invisible character found and replaces it with a zero length string.

select REPLACE([Text], SUBSTRING([Text], PATINDEX('%[' + CHAR(0)+ '-' +CHAR(31) + CHAR(127)+ '-' +CHAR(255)+']%'  , [Text] COLLATE Latin1_General_100_BIN2 ), 1 ), ' ') [Text] 
from DataExtract

Please note that casting the column to be replaced with binary collation is required else some of the characters are not parsed.  

As the above statement only replaces all occurences of the the first invisible character found. It might be more meaningful to extract this data into a table and update the text column replacement until there are no more rows found with invisible characters 

update DataExtract
set Text = REPLACE([Text], SUBSTRING([Text], PATINDEX('%[' + CHAR(0)+ '-' +CHAR(31) + CHAR(127)+ '-' +CHAR(255)+']%'  , [Text] COLLATE Latin1_General_100_BIN2 ), 1 ), ' ')
where  PATINDEX('%[' + CHAR(0)+ '-' +CHAR(31) + CHAR(127)+ '-' +CHAR(255)+']%'  , [Text] COLLATE Latin1_General_100_BIN2 ) > 0

Sometimes a value starting with a double quote '"' might cause an issue when one tries to paste the data in Excel, as excel tries to interpret a start and end of text column using double quote as a delimiter. So if you are copying and pasting values in excel make sure that the column does not start with a double quote else best is to replace such occurrences:

Update DataExtract
set  [Text] = SUBSTRING( [Text], 2, 10000 )
where left( [Text], 1 ) = '"'

Fetch the Enum labels in SQL Statements

 When dealing with writing SQL Statements the Enum values and their corresponding names force us to write switch case statements which is time consuming. A shortcut for this method is as follows. 

There is already a table in the D365 Database called SRSANALYSISENUMS this table is used to store the D365 labels for use in the Datawarehouse cubes. By default all the tables that are part of any perspectives are populated in this table. However, for some of our enum we can explicitly populate this table and use the same in SQL. 

Create the following job to populate any given enum into the SRSANALYSISENUMS table. 

    public static void main(Args _args)
    {
        SRSAnalysisEnums analysisEnums;
        DictEnum currentEnum;
        str currentEnumName;
        int valueCount;
        str enumItemName;
        int enumItemValue;
        RecordInsertList records;
        Dialog dialog;
        DialogField  dfEnumName;

        dialog = new Dialog("Please enter the enum name");
        dfEnumName = dialog.addField(extendedTypeStr("Name"));
        dfEnumName.label("Enum name");
        if (dialog.run())
        {
            records = new RecordInsertList(tablenum(SRSAnalysisEnums));
         
            currentEnumName = dfEnumName.value();
            currentEnum = new DictEnum(enumName2Id(currentEnumName));
            if(currentEnum == null)
            {
                throw error(strfmt("Enum with name %1 does not exists.",currentEnumName));
            }
            valueCount = currentEnum.values();
            ttsbegin;
            delete_from analysisEnums where analysisEnums.EnumName == currentEnum.name();
            for(int j = 0; j < valueCount; j++)
            {
                enumItemName = currentEnum.index2Symbol(j);
                enumItemValue = currentEnum.index2Value(j);
             
                select firstfast forupdate EnumName, EnumItemName, EnumItemValue
                from analysisEnums where analysisEnums.EnumName == currentEnum.name() && analysisEnums.EnumItemValue == enumItemValue;

                if (analysisEnums)
                {
                    if (analysisEnums.EnumItemName != enumItemName)
                    {
                        analysisEnums.EnumItemName  = enumItemName;
                        analysisEnums.update();
                    }
                }
                else
                {
                    analysisEnums.EnumName      = currentEnum.name();
                    analysisEnums.EnumItemName  = enumItemName;
                    analysisEnums.EnumItemValue = enumItemValue;
                 
                    records.add(analysisEnums);
                }
            }
            records.insertDatabase();
            ttscommit;

            Info("Completed");
        }
    }

 

Once this job is created and compiled we can run this class for each Enum that we want to be populated. To run the class from the url use the following syntax: 

https://ds-dev-7658e683048e57b40devaos.cloudax.dynamics.com/?cmp=ds01&mi=SysClassRunner&Cls=SRSAnalysisEnumsGenerator

In the above URL the servername and cmp parameter should be replaced to match your environment.