Microsoft Power BI的Exact Online驱动程序的安装步骤

当前有许多步骤，可以通过* .msi文件进行设置。这些步骤也适用于其他平台，例如Freshdesk，Microsoft Dynamics CRM，Twinfield，Teradata，XML文件等：

• 运行Invantive ADO.net提供程序的MSI，例如可下载的测试版本ADO.net提供程序：
• 如果您只想连接到一个提供程序，请使用下面在Code Exact Online上列出的示例。
• 当您要连接到多个物理数据库时：安装Invantive Query Tool并运行发现，或者settings.xml使用Exact Online从另一台PC复制要在其中使用的国家/地区的现有数据库。
• 要在没有本地安装的情况下使用或与Power BI Web或数据流一起使用，请使用Invantive Bridge Online（Invantive Cloud的一部分）中的OData4连接器。
• 安装并启动Microsoft Power BI桌面。
• 转到获取数据->空白查询：

• 选择高级编辑器：

• 在查询编辑器中粘贴下面列出的代码。
• 然后在消息“运行此本机数据库查询需要权限”上单击“编辑权限”。
• 然后点击“运行”。
• When requested enter user name and password as you use with Exact Online:

• Two phase authentication is not yet supported, so you will need to use a separate log on code when you need two phase authentication for your normal account.
• From here on use the results as a normal SQL Server or alike query:

• Or graphically:

Code Exact Online

let
    Source = AdoDotNet.Query
             ( "Invantive"
             , "provider=ExactOnlineAll;apiUrl=https://start.exactonline.nl;"
             , "select * from PayablesListOverdue"
             )
in
    Source

Please note that the text within the AdoDotNet.Query does not support multiline statements, so the easiest way is to put the queries somewhere else and then copy/paste them here while replacing line feeds by space.

The SQL resembles Microsoft SQL Server or Oracle syntax, but is actually Invantive SQL (Invantive SQL Grammar). The table names and connector attributes are available online in the documentation.

Please note that the queries can be created using the Online SQL Editor or one of the query products. Exact Online has something like 500 APIs available. Not everything, for instance item average cost price is NOT available.

Please note that Power BI retrieves a lot of information from Exact Online. When running across multiple companies, it can retrieve them in parallel. However, when you for instance download all financial transactions of a company, that can take a long time. Also, the information is normally stored within the pbix file, so that one can become very large. As always, apply proven Power BI design guide lines for optimal results.

Code XML Audit Files

You can use the same approach for XML audit files. Use the following query for XML Audit File Cash Registers 3.0 (xaa 3.0):

let
    Source = AdoDotNet.Query
             ( "Invantive"
             , "provider=Xaa30;"
             , "select * from LocationCashRegisterCashTransactionLines"
             )
in
    Source

Please note that especially with detailed processing of cash register audit files you will get a LOT of cash register transaction and large Power BI sets and Power BI has some problems handling large volumes. Cash register or financial audit files larger than 1 GB will run slow and require a lot of physical memory to be present.

Please note that with audit files, Power BI insists that you enter a user. Just enter 'x' or something. It is ignored.

Sample result:

Code Twinfield ERP (obsoleted with new versions)

You can use the same approach for Twinfield. But, Twinfield also requires you to specify a company or environment code on log on as third field, so you need to specify that one too.

Option 1 - Twinfield environment code to Power BI

let
    Source = AdoDotNet.Query
             ( "Invantive"
             , "provider=Twinfield;environment=YOUR_ENVIRONMENT_CODE"
             , "select * from Twinfield.Twinfield.Periods"
             )
in
    Source

Option 2 - Twinfield environment code to Power BI through connection string

您也可以在Power BI的单独窗口中指定连接字符串上的属性值。这样可以使代码清除与用户关联的内容：