5️⃣ Working with Analytics & Visualization Tools: Microsoft Power BI and Power Query with TMVGate JSON or XML Output

Microsoft Power BI and Power Query with TMVGate JSON or XML Output

The TMVGate PSV, JSON or XML output can be used as a data source for Microsoft Power BI and Power Query

Power BI with TMVGate JSON Output

  1. Open a new Microsoft Power BI Data Source, choose ‘Web’

image.png

  1. In the “From Web” dialog box, enter the TMVGate URL generated using JSON format and click OK

image.png

  1. The screen should look like the following image if Power BI is able to interpret the JSON records. Click on ‘List’ as highlighted

image.png

  1. On the next display, Right Click on the header "List" and choose "To Table"

image.png

  1. Click OK on the To Table dialog box

image.png

  1. Notice that there is a small icon next to the Column header as shown. Click on the icon, you can now select the fields that you need as well as the prefix option. Click on OK to use all columns

image.png

  1. A preview of records from TMVGate output will be displayed. You should now indicate to Power BI all the numeric fields as numbers or decimals, this is done by Right Clicking on the column header and choosing ‘Change Type’

image.png

  1. Click on ‘Close and Apply’ when all definitions are completed

image.png

  1. Power BI will refresh the data and you are ready to start with your report or visualization creation. Whenever you do a Refresh, Power BI will initiate a TMVGate request to refresh the data from TM1 in real time

Power BI with TMVGate XML Output

  1. Open a new Microsoft Power BI Data Source, choose ‘Web’

image.png

  1. In the “From Web” dialog box, enter the TMVGate URL generated using JSON format and click OK

image.png

  1. The screen should look like the following image if Power BI is able to interpret the JSON records. Click on ‘List’ as highlighted

image.png

  1. On the next display, Right Click on the header ‘List’ and choose ‘To Table’

image.png

  1. Click OK on the To Table dialog box

image.png

  1. Notice that there is a small icon next to the Column header as shown. Click on the icon, you can now select the fields that you need as well as the prefix option. Click on OK to use all columns

image.png

  1. A preview of records from TMVGate output will be displayed. You should now indicate to Power BI all the numeric fields as numbers or decimals, this is done by Right Clicking on the column header and choosing ‘Change Type’

image.png

  1. Click on ‘Close and Apply’ when all definitions are completed

image.png

  1. Power BI will refresh the data and you are ready to start with your report or visualization creation. Whenever you do a Refresh, Power BI will initiate a TMVGate request to refresh the data from TM1 in real time

Power BI with TMVGate XML Output

  1. Open a new Microsoft Power BI Data Source, choose ‘Web’

image.png

  1. In the From Web dialog box, enter the TMVGate URL generated using XML format and click OK

image.png

  1. The screen should look like the following image if Power BI is able to interpret the XML records, click on the icon beside the Table. You can now select the fields that you need as well as the prefix option. Click on OK to use all columns

image.png

  1. The remainder of the steps will be the same as the JSON format in which you can change the numeric field type. Proceed with the loading and start creating your reports and visualization

image.png

Power BI with TMVGate PSV or TXT Output

  1. Open a new Microsoft Power BI Data Source, choose ‘Web’

image.png

  1. In the From Web dialog box, enter the TMVGate URL generated using PSV or TXT format and click OK

image.png

  1. The screen should look like the following image if Power BI is able to interpret the PSV or TXT records, click on Load to start creating your reports and visualization

image.png

Made with ProductLift