Skip to main content

General ledger cube using Power BI [AX 2012]

With the recent release of Power BI desktop it has been an exciting week to play with new toys.

I decided to do a quick general ledger cube using Power BI desktop. I put together some SQL scripts to resolve the dimensions.

1. Open up PowerView Desktop and select Get data and select SQL as the source.

2. Paste the following query I mashed up to get the GL transactions with the 3 financial dimensions. The RecIds I have there are based on Contoso database. In my next blog I will try to explain how to get the RecIds.

SELECT
    GeneralJournalCube.*,
    OLAPBUSINESSUNIT,
    OLAPCOSTCENTER,
    OLAPDEPARTMENT
FROM GeneralJournalCube INNER JOIN
(
    SELECT
        DAVC.RecId as CombinationId ,
        MAX(CASE WHEN DA.RECID = 22565421195 THEN DAV.ENTITYINSTANCE ELSE NULL END) AS OLAPBUSINESSUNIT ,
        MAX(CASE WHEN DA.RECID = 22565421197 THEN DAV.ENTITYINSTANCE ELSE NULL END) AS OLAPCOSTCENTER ,
        MAX(CASE WHEN DA.RECID = 22565421196 THEN DAV.ENTITYINSTANCE ELSE NULL END) AS OLAPDEPARTMENT
    FROM
        DIMENSIONATTRIBUTEVALUECOMBINATION DAVC INNER JOIN
        DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION DAVGC ON
        DAVC.RECID = DAVGC.DIMENSIONATTRIBUTEVALUECOMBINATION INNER JOIN
        DIMENSIONATTRIBUTELEVELVALUE AS DAVL ON
        DAVL.DIMENSIONATTRIBUTEVALUEGROUP = DAVGC.DIMENSIONATTRIBUTEVALUEGROUP INNER JOIN
        DIMENSIONATTRIBUTEVALUE AS DAV ON DAV.RECID = DAVL.DIMENSIONATTRIBUTEVALUE INNER JOIN
        DIMENSIONATTRIBUTE DA ON DA.RECID = DAV.DIMENSIONATTRIBUTE
    GROUP BY DAVC.RECID
)
Dimensions ON
    Dimensions.CombinationId = GeneralJournalCube.LEDGERDIMENSION

Should look like this once pasted.

image 

3.  Now for the dimensions we will use SQL as our source too. Paste the following for the department dimension.

SELECT
    T1.ENTITYINSTANCE AS OLAPDEPARTMENT,
    T2.VALUE AS VALUEATTRIBUTE,
    T2.NAME AS NAMEATTRIBUTE
FROM  [DBO].DIMENSIONATTRIBUTEVALUE T1
CROSS JOIN  [DBO].DIMATTRIBUTEOMDEPARTMENT T2 
WHERE      (T1.DIMENSIONATTRIBUTE=22565421196) AND 
        (T1.ENTITYINSTANCE=T2.KEY_) AND
        (T1.PARTITION = T2.PARTITION)

4. Continue to do the same for all 3 dimensions and the main account.

For the main account it is fairly easy query

select RecId, MAINACCOUNTID, Name from MAINACCOUNT

5. Once you have added the data source. Click on Manage relationships to link up the tables. That should be easy now that you have the RecIds for the tables and the dimensions renormalized in the first Transactions query.

image

6. Now the recIds on the data sets is a bit un friendly and we don't want to show it in the report. Just right click the column and click on Hide from report.

image

7. Also the column names are a bit unfriendly as its the field names and not the labels. Just right click and Rename.

8. Click on Reports and start putting your report together.

image

We can now publish the report the PowerBI portal.

Key take always from this blog:

  1. I spent a few hours getting the GL transactions query to the way I want it. Hope this saves you time.
  2. I have tested this with a large database 29million records. It took about 7 minutes on a all in one development box 16GB ram.
  3. I don’t mention how you could use alternative data sources to mash up together.

Assumptions I made:

  1. I did not consider partitions here
  2. I ignored Enums. However you could get it from SRSANALYSISENUMS table.
  3. I ignored column names
  4. Multi language and currency is not considered
  5. I have tried the same with OData but found it a little heavy for such a large table. For the most thing I would have probably used OData.
  6. I did not add date dimensions

Some great tips on the Microsoft Solution Architecture blog.

http://blogs.msdn.com/b/axsa/archive/2015/02/18/power-bi-and-dynamics-ax-part-1-introduction.aspx

Popular posts from this blog

AX - How to use Map and MapEnumerator

Similar to Set class, Map class allows you to associate one value (the key) with another value. Both the key and value can be any valid X++ type, including objects. The types of the key and the value are specified in the declaration of the map. The way in which maps are implemented means that access to the values is very fast. Below is a sample code that sets and retrieves values from a map. static void checkItemNameAliasDuplicate(Args _args) { inventTable inventTable; Map map; MapEnumerator mapEnumerator; NameAlias nameAlias; int counter = 0; ; map = new Map(Types::String, Types::Integer); //store into map while select inventTable { nameAlias = inventTable.NameAlias; if (!map.exists(nameAlias)) { map.insert(nameAlias, 1); } else { map.insert(nameAlias, map.lookup(nameAlias) + 1); } } //retrieve fro

AX - How to use Set and SetEnumerator

The Set class is used for the storage and retrieval of data from a collection in which the values of the elements contained are unique and serve as the key values according to which the data is automatically ordered. You can create a set of primitive data types or complex data types such as a Class, Record or Container. Below is sample of a set of records. static void _Set(Args _args) {     CustTable       custTable;     Set             set = new Set(Types::Record);     SetEnumerator   setEnumerator;     ;     while select custTable     {         if (custTable && !set.in(custTable))         {             set.add(custTable);         }     }     if (!set.empty())     {         setEnumerator = set.getEnumerator();         setEnumerator.reset();         while (setEnumerator.moveNext())         {             custTable = setEnumerator.current();             info(strfmt("Customer: %1",custTable.AccountNum));         }     } } Common mistake when creating a set of recIds

Import document handling (attachment) files #MSDyn365FO

Out of the box you have limited data entities for migrating attachments. If you search what is already in the AOT, you will see a few various examples. I suggest you look at the LedgerJournalAttachmentsEntity as it is the simplest and cleans to copy from. I wont go into detail but I will give a quick run down of what it looks like. Use the DocuRefEntity as your main datasource. It does most of the work for you. Set your table you want to import for as the child datasource Add the Key You will need to add the postLoad method. There is minor code to update the virtual field FileContents. Below is an export I did for the general journal attachments. The import zip structure should be the same way. It will create the usual artifacts such as the excel, manifest and package header xml files. You will see a Resources folder under that. If you drill down to the resources you will see the attachments. This is an export and it used the document GUID for uniqueness. The other thing is the extensi