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

Labels:

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
Read more

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
Read more

Approve Workflow via email using template placeholders #Dyn365FO

Dynamics 365 for Finance and Operations has placeholders which can be inserted into the instructions. Normally you would want this to show up in the email that is sent. One of the most useful ones is the URL link to the exact record that you are approving. In the workflow configurations use the placeholder and build up your message. Towards the end it has workflow specific ones. The URL token is %Workflow.Link to web% . For the technical people the token is replaced in this class WorkflowDocumentField. This is what I inserted into my email template. <BODY> subject: %subject% <BR> message: %message% <BR> company: %company% <BR> for: %for% <BR> </BODY> Should look like this. The final result looks like this. If you debug these are the place holders that are put together.
Read more