Saturday, 15 August 2015

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

3 comments:

Data Bear Solutions said...

Microsoft has actively sustained what they call “a blistering pace of innovation” and, whilst listening to user needs, they continue to update Power BI with monthly updates. Microsoft Power BI is new, but excitingly it’s evolving virtually on a monthly basis. So just when you realize how brilliant the Power BI tool is, you suddenly discovered it’s just got even better! Data Bear is one of the many companies helping clients with this powerful self-service business intelligence (BI) offering, and it didn’t take long for their team to identify Power BI as a serious game changer. Specializing in business intelligence consulting, Data Bear founder, Johann Joubert believes Power BI has become the single most powerful tool for businesses to use to ensure the full range of businesses are ahead of the rapidly changing BI world and the opportunities it offers.

Find More : Power BI Reports

oracle fusion cloud procurement training said...

This is the proper weblog. it's miles very interesting weblog ever. thanks for giving this sort of amazing article. for further information please visit the site
Oracle Fusion procurement Coaching
Oracle Fusion procurement Training Institute

paragchapre said...

HI,

Can I get XPO/Project for this post? I am doing same thing in Power BI desktop with Dynamics 365. Also, creating data entity for whole query.