Configure Power BI Embedded in Dynamics 365 for Finance and Operations (One-box environment)

By default, Power BI Embedded will be available only in UAT & Production boxes.
Showcasing to the customers in Demo or Presales system would be challenging.
Follow the below steps to enable the same in Demo environment as well.

1. Create new SQL Server in Azure Portal, as shown below. Add Server admin login as axdbadmin and provide the password from the LCS Demo environment full details. Create new Database "AxDW" with the default settings
   

2. Create a Deploy a custom template

JSON String: Use the scrip as is, we can replace the below highlighted value and the same name will be used while generating access keys.

{ "$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#", "contentVersion": "1.0.0.0", "parameters": {}, "resources": [ { "type": "Microsoft.PowerBI/workspaceCollections", "sku": { "name": "S1", "tier": "Standard" }, "name": "DEVPBI", "apiVersion": "2016-01-29", "location": "North Europe" } ] }

3. Login to the Demo Box and Run below commands in PowerShell (e.g. ISE) as an administrator:
   
   set-executionpolicy remotesigned
   Install-Module -Name Az -AllowClobber
   Import-Module Az.PowerBIEmbedded
   Connect-AzAccount
   
   Use below command, if you have multi tenant liked to your account
   Connect-AzAccount -Tenant *****.onmicrosoft.com

4. Access Keys: Run below command to get Access keys
   Get-AzPowerBIWorkspaceCollectionAccessKey ResourceGroupName "ResourceGroupName" WorkspaceCollectionName "DEVPBI".
   The ResourceGroupName assigned to the SQL Sever at the time of creation in the 1st step.

5. Create below users in newly created Azure SQL Server, along with their passwords specified in the LCS for the Demo box enviornment.
   'axdwadmin’ and ‘axdwruntimeuser’
   
   GO CREATE LOGIN [axdwadmin] WITH PASSWORD=N'******'
   GO CREATE LOGIN [axdwruntimeuser] WITH PASSWORD=N'******'
   
   
   
6.  Add above users to AxDW Database, which we created newly in Azure SQL Server and assign the roles.
   
   CREATE USER axdwadmin FROM LOGIN axdwadmin;CREATE USER axdwruntimeuser FROM LOGIN axdwruntimeuser;
   
   ALTER ROLE db_owner ADD MEMBER axdwadmin;
   ALTER ROLE db_datareader ADD MEMBER axdwruntimeuser;
7. Update web.config file with latest PowerBI access keys, which we get from Step 4.
   
   Search for BiReporting.DWServer  and replace the value with newly created azure sql server name
   <add key="BiReporting.DWServer" value="****.database.windows.net"/>
   Add the below lines before the </appSettings>
   <add key="PowerBIEmbedded.AccessKey" value="Access key 1" />
   <add key="PowerBIEmbedded.AccessKey2" value="Access key 2" />
   <add key="PowerBIEmbedded.ApiUrl” value="https://api.powerbi.com" />
   
   <add key="PowerBIEmbedded.IsPowerBIEmbeddedEnabled" value="true" />
   <add key="PowerBIEmbedded.WorkspaceCollectionName" value="DEVPBI" />
8. Restart IIS and D365FO Batch Services.


9. To test Embedded PowerBI is working:
   
   Refresh any entity Store in the application.
   Verify structure and records got pushed to newly created AxDW database, by seeing the related Measures and Views.
   Go the the relavent entity store workspace and click on Analytics tab.
   We should be able to see the PowerBI Reports.
   
   Sometimes we may get error saying that, “Failed to publish Power BI Embedded report ****BIReport.”
   Resolution: Add public IP address of the Demo box to the newly created Azure SQL Server, in Firewall and virtual networks.
    
   
   

Hope this content helps you to configure PowerBI Embedded in your Demo or Development boxes..

Extension of Class in D365 for Operations

We can create extension for class but can use/call only Public Members/Methods. Private and Protected can not be accessed from Extension Class.

Added new field TstField in MainAccount Table using extension.
Would like to show newly added field on Trail balance Form/Report.
Added new field TstField in LedgerTrialBalanceTmp Table using extension.

/// <summary>

/// Extension Class for LedgerTrialBalanceDP

/// </summary>
[ExtensionOf(classStr(LedgerTrialBalanceDP))]

final class HNWLedgerTrialBalanceDP_Extension

{

    protected void new()

    {

    }

    /// <summary>

    /// Updating new field in <c>LedgerTrialBalanceTmp</c> Table

    /// </summary>

    public void UpdateTstField()

    {

        MainAccount                         mainAccount;

        DimensionAttributeValueCombination  ledgerDimension;

        LedgerTrialBalanceTmp               ledgerTrialBalanceTmp;

       

        ledgerTrialBalanceTmp = this.getLedgerTrialBalanceTmp();     

        update_recordset ledgerTrialBalanceTmp

            setting
            TstField     =   mainAccount.TstField
            join mainAccount
        exists join ledgerDimension where
            ledgerDimension.MainAccount == mainAccount.RecId &&
            ledgerDimension.RecId == ledgerTrialBalanceTmp.LedgerDimension;

        this.setTrialBalanceTmpTable(ledgerTrialBalanceTmp);

    }

}

Create postEventHandler for processReport method in LedgerTrialBalanceDP Class
and call our newly created method in Extension Class of LedgerTrialBalanceDP

public class HNWLedgerTrailBalanceHandlersExt
{

    [PostHandlerFor(classStr(LedgerTrialBalanceDP), methodStr(LedgerTrialBalanceDP, processReport))]

    public static void LedgerTrialBalanceDP_Post_processReport(XppPrePostArgs args)        {

       LedgerTrialBalanceDP ledgerTrialBalanceDP = args.getThis();
        ledgerTrialBalanceDP.UpdateTstField();
     }
}

 

Using/Extending SysOperationFramework instead of RunBaseFramework in AX 2012

Below are the Classes created to Demonstrate
 

1.  ESS_DemoSysOperationController Class which extends SysOperationServiceController – (For Batch Dialog)

2.  ESS_DemoSysOperationService Class which extends SysOperationServiceBase – (For multitask Batch Initiation)
3.  ESS_DemoOperationDataContract Class – (For parameter values)
4.  ESS_DemoSysOperationTaskService Class – (For actual logic to execute in Batch)

1.   Create a class extends SysOperationServiceController

    class ESS_DemoSysOperationController extends SysOperationServiceController
    {

}

    a.   Override new method
       protected void new()
       {
            super(classStr(ESS_DemoSysOperationService), methodStr(ESS_DemoSysOperationService, update),   SysOperationExecutionMode::Synchronous);
       }

b.   Create  Construct method

        server static ESS_DemoSysOperationController construct()
        {

    ESS_DemoSysOperationController      controller; 

    controller = new ESS_DemoSysOperationController ();

    controller.parmShowDialog(true); // Actually the default value

    controller.parmShowProgressForm(false);

    return controller;

        }

c.   Create main method

static void main(Args args)

        {

    ESS_DemoSysOperationController operation;

            operation = new ESS_DemoSysOperationController ();
            operation.startOperation();
        }

2.   Create a class extends SysOperationServiceBase

class ESS_DemoSysOperationService extends SysOperationServiceBase

    {

    BatchHeader                         batchHeader;

        SysOperationServiceController       controller;
        ESS_DemoSysOperationDataContract    contract;
    }

 

a.   Create update method

[SysEntryPointAttribute]

    public void update()
    {

    RefRecId minRecId, maxRecId, fromRecId, toRecId;

        minRecId =  5637775244;
        maxRecId =  5648390004;
        fromRecId = minRecId;

    if(this.isExecutingInBatch())

        {
            if(!batchHeader)
            {
                 batchHeader = BatchHeader::getCurrentBatchHeader();
            }

        while (toRecId < maxRecId)

            {
                 toRecId = fromRecId + 100000;

            // Create a service controller to run the task

            controller = new SysOperationServiceController(classStr(ESS_DemoSysOperationTaskService), methodStr(ESS_DemoSysOperationTaskService, insertRecords),SysOperationExecutionMode::Synchronous);

        contract = controller.getDataContractObject();

            contract.parmValues(["value01", "value02",fromRecId,toRecId]);
            batchHeader.addRuntimeTask(controller, this.getCurrentBatchTask().RecId); // Create a runTimeTask within the current batch job
           fromRecId = toRecId;
        }
    }
    else
    {
        // Create a service controller to run the task
        controller = new SysOperationServiceController(classStr(ESS_DemoSysOperationTaskService), methodStr(ESS_DemoSysOperationTaskService, insertRecords),SysOperationExecutionMode::Synchronous);

        contract = controller.getDataContractObject();
        contract.parmValues(["value01", "value02",minRecId,maxRecId]);
        controller.run();
  
    }
    // If we're processing in batch, then save the batch header
    if(batchHeader)
    {
        batchHeader.save();
    }
}

3.   Create a Class ESS_DemoSysOperationDataContract

[DataContractAttribute]

    public class ESS_DemoSysOperationDataContract
    {
         container   conValues;
    }

a.   Create parmValues method

[DataMemberAttribute]

        public container parmValues(container _values = conValues)
        {
             conValues = _values;
         
             return conValues;
        }

 

4.   Create a Class ESS_DemoSysOperationTaskService

 
     public class ESS_DemoSysOperationTaskService
     {
         ESS_SysOperationDemoTable       ess_SysOperationDemoTable;
     }

a.   Create insertRecords method

[SysEntryPointAttribute]

        public insertRecords(ESS_DemoSysOperationDataContract _contract)
        {
            str                         value01, value02;
            RefRecId            fromRecId, toRecId;    
            RecordInsertList     recordInsertList = new RecordInsertList(tableNum(ESS_SysOperationDemoTable));

    [value01, value02, fromRecId, toRecId] = _contract.parmValues();

    While (condition)

            {
                 ess_SysOperationDemoTable.clear();
                 ess_SysOperationDemoTable.Field01 = 1
                 ess_SysOperationDemoTable.Field02 = 2;
                 recordInsertList.add(ess_SysOperationDemoTable);
            }
       
            recordInsertList.insertDatabase();
        }

Older SQL Backup files deletion through batch

To keep backup files up to 30 days and delete rest of the files in Windows Batch:

Create a batch file using the below command:

forfiles /M *.bak /P "Backup Folder" /S /D -NoOfDays /C "cmd /c del /F /Q @path"

forfiles /M *.bak /P "E:\SQL Backup" /S /D -30 /C "cmd /c del /F /Q @path"

Copy paste the below command in notepad and change your folder path and Days then Save as .bak file.

Refer below link for further:

https://technet.microsoft.com/en-us/library/cc753551(v=ws.11).aspx

Use the Windows Task Scheduler to call the batch script:

https://technet.microsoft.com/en-us/library/cc748993(v=ws.11).aspx

AX7 - How to get recent modifications/changes in application

1.       To count particular of objects in a model:

[System.IO.Directory]::GetFiles("C:\Packages\ESS_WBS\ESS_WBS\AxClass\","*.xml").count

 

 

 

2.       To get list of objects modified in past 120 days:

Get-ChildItem -Path "C:\Packages\*" -Recurse -Include *.xml |select LastWriteTime, FullName,Name| Where{$_.LastWriteTime –ge (Get-Date).addDays(-120)} |Sort-Object lastWriteTime -descending| out-gridview

 

Reference: http://instructorbrandon.com/real-world-examples-to-understand-the-most-significant-ax-7-code-change-towards-enterprise-code-stability/

 

Get Department Financial Dimension Value

• To get Department financial dimension value from Journal Transactions (LedgerJournalTrans)

// BP Deviation Documented

public display OMOperatingUnitNumber sha_displayDepartment()
{

    DimensionAttribute                  dimAttribute = DimensionAttribute::findByName('Department'); // Change 'Department' to which dimension you are looking for

    DimensionAttributeValueSetItemView  dimAttributeValueSetItemView;

    select firstOnly dimAttributeValueSetItemView
        where dimAttributeValueSetItemView.DimensionAttributeValueSet == this.DefaultDimension &&
              dimAttributeValueSetItemView.DimensionAttribute == dimAttribute.RecId;
   
    return dimAttributeValueSetItemView.DisplayValue;
}

• To get Department financial dimension value from Voucher Transactions (GeneralJournalAccountEntry)

 

// BP Deviation Documented

public display OMOperatingUnitNumber sha_displayDepartment()

{

    DimensionAttribute                  dimAttribute = DimensionAttribute::findByName('Department'); // Change 'Department' to which dimension you are looking for

    DimensionAttributeLevelValueAllView dimAttributeLevelValueAllView;

   

   

    select firstOnly dimAttributeLevelValueAllView

        where dimAttributeLevelValueAllView.ValueCombinationRecId == this.LedgerDimension &&

              dimAttributeLevelValueAllView.DimensionAttribute == dimAttribute.RecId;

   

    return dimAttributeLevelValueAllView.DisplayValue;

}

AX 7 Form Event Handlers

We must use Event handlers, if you are using Extension Model Development in AX 7.

1. How we can handle the Datasource fields based on active/selected record, in Event handler method.

[FormDataSourceEventHandler(formDataSourceStr(FormName, DataSource), FormDataSourceEventType::Activated)]

public static void DataSource_OnActivated(FormDataSource sender, FormDataSourceEventArgs e)
{
    Common    common = sender.cursor();
    sender.object(fieldNum(DataSource, ProjActivityNumber)).enable(common.ProjId);
}

2. How we can handle the Controls like, Tab/TabPage/Button etc

 

[PostHandlerFor(formStr(

LedgerJournalTable), formMethodStr(LedgerJournalTable, init))]

    public static void LedgerJournalTable_Post_init(XppPrePostArgs args)

    {

        FormRun             sender = args.getThis();

        LedgerJournalType   journalType = sender.args().parmEnum();

        if (journalType == LedgerJournalType::Assets)

        {

            sender.control(sender.controlId(formControlStr(LedgerJournalTable, YourControl))).visible (true);

        }

} 

 

3. To get new number sequence reference for an existing module.

 

 

    [PostHandlerFor(formStr(AssetParameters), formMethodStr(AssetParameters, numberSeqPreInit))]

    public static void AssetParameters_Post_numberSeqPreInit(XppPrePostArgs args)

    {

        TmpIdRef                                tmpIdRef;

        NumberSeqScope                          scope;

        NumberSeqApplicationModule              numberSeqApplicationModule;

        container                               numberSequenceModules;

 

        numberSequenceModules = [NumberSeqModule::Asset];

        numberSeqApplicationModule = new NumberSeqModuleAsset_XXX();

        scope = NumberSeqScopeFactory::createDataAreaScope();

        NumberSeqApplicationModule::createReferencesMulti(numberSequenceModules, scope);

        tmpIdRef.setTmpData(NumberSequenceReference::configurationKeyTableMulti(numberSequenceModules));

 

        args.setArg('tmpIdRef',tmpIdRef);

    }