Help understanding how to query the database for Power BI reporting

Hello, I am the data architect of a company that uses Sage 200 Evolution in one of its subsidies in Nigeria. I was tasked to link the Sage SaleOrders data in our Power BI reporting. I have full access to the underlying MS SQL Server database, but cannot find any documentation on which tables to query, joins, etc. I can only see weird underscore prefixed tables and views, and functions that seem to query them. I could not find ANY documentation online on these underscore prefixed tables...

The Sage partner company we use have been very reluctant to help : they only showed me how they retrieve data using the Sage Evolution Explorer interface, or another interface called "Sage intelligence reporting". I have asked multiple times for technical documentation, or means to connect Power BI to that datasource and intelligible queries I can run (I can connect PBI to the SQL Server but don't know which tables/functions to query).

I have seen online that Sage is offering a 'starter' Power BI reporting called the 'Customers' Power BI template (Sage 200 Customers Report.pbit)', and asked our Sage integrator partners to retrieve it, but they answered me with "the Power BI report for customers report template that you displayed during the last meeting is only available in the UK region, and that report is not available in our Business Partner Platform region" (Nigeria).

I do not know what to do anymore, except from running a Profiler on that SQL Server db and slowly capture the queries that are run on the db, which could take weeks.... Please help

Here's what I need : 

- Any existing means/example of connecting Power BI to the Sage 200 Evolution SQL Server db to retrieve data about SaleOrders (Inventory and customers dimensions too if possible)

- Any technical documentation on the SQL Server database (description of the important tables / columns, how to join, etc.).

Many thanks in advance to whoever can point me in the right direction

  • 0

    Hi Yassinet

    From the above, I'm gathering that you have not yet worked with or know about the Sage Evolution Data Dictionary, correct?

    Do the following to use it:

    1. Within the installation directory of Evolution, look for a folder DataDictionary and then copy the complete folder on your local Evolution server PC (into e.g., C:\Evolution

    2. Open the evDict.exe file from the C:\Evolution\DataDictionary folder

    3. In here set up connections to the SQL server instance name for the Common Database and Master Database sections. Also, select the relevant Common Db and Evo company DB's below.  When done, click the Connect button 

    4. Wait about 3-5 minutes for it to completely open and show you the results

    5. When finally opened, notice the various tables and fields within each table (as you expand each table)

    For instance, notice the INVNUM table (the main source document header table for all sales-, Purchase Orders, Inventory Invoices, GRV's etc)

    6. In addition, you'll notice the Primary and Foreign keys on all the fields.

    Please confirm if this will help you to better understand the table structure of an Evolution company as well as the relationships between the DB tables.

    Kind Regards

    Bennie 

     

  • 0 in reply to Bennie Pienaar

    Thank you so much Bennie, I was able to retrieve the evDict.exe software and see indeed the relations between tables, which was a great help.

    The only thing I would like to ask is this : Is there any existing Power BI report, which uses the SQL Server as datasource, that could help me kickstart my own Power BI reporting ? As said earlier I'd like to focus on SaleOrders and Inventory. I've seen in the official Sage online resources that Sage offers a Power BI report (a PBI template to be exact, named Sage 200 Customers Report.pbit, that you can see here : desktophelp.sage.co.uk/.../PowerBI Create reports.htm), but our Nigerian Sage partner answered us that he could not get access to it as it was UK only resource...

    So is there any way you could share that PBI report, or any PBI report that could help me kickstart the project (basiccally something that would show me the SaleOrders and/or Inventory tables to query).

    Thank you again !

  • 0 in reply to YassineT

    Hi Yassinet

    I can provide the following advice:

    There is a difference between Sage 200 Evolution and Sage 200.

    In fact, the latter mentioned 'Sage 200' is available in one of 2 options:

    • Sage 200  Standard
    • Sage 200 Professional

    I noted the Power BI report available for Sage 200, but I an confirm we don't have a standard Power BI for Sage 200 Evolution.

    However, please send me your e-mail address in a private chat in Sage City and I'll then advise other BP's that may perhaps be available to assist you in developing it.

    Kind Regards

    Bennie 

  • 0 in reply to Bennie Pienaar

    Ok, I understand. The Power BI report I showed was only made for Sage 200, and not Sage 200 Evolution. Subtle difference that is not obvious for Sage newcomers ^^

    Thank you for still trying to help, I will defintely get in touch with you via DM.

    Regards,

    YT