Considering we have already touched base on Financial Reporting previously, we would like to mention a couple of issues that you may face when trying to open a report designer, especially if it is your first time:
- There may be times when report designer does not start when you select “new” or “edit”;
- A user who is using report designer hasn’t been assigned the required permissions to use financial reporting;
- A user is able to proceed past the ClickOnce Report Designer sign-in page but he/she is unable to complete sign-in within the Report Designer.
Even though they can be resolved pretty easily following these steps provided by the MS team, we still highly recommend contacting your Dynamics partner in case you feel uncomfortable playing with the settings.
Things that Can Affect Performance
Then, let’s have a look at reporting performance. When running reports in financial reporting you have great flexibility and capability to run reports down to the transaction details. But with great power comes great responsibility – running large reports with many rows and columns can sometimes cause delays in performance or slow running reports. Hence, we’d love to highlight some examples of what may cause these delays so that you are aware of them when you are designing your reports.
Adding a large number of dimensions. Always select dimensions that you need to report on. For example, if you are doing segment reporting by a business unit you want to make sure to include that. But, if you have a dimension for the location that you may not need, you would want to exclude that. Furthermore, adding attributes from accounts payable and accounts receivable to a single report can also slow down the report.
Historical currency translations. We usually recommend using this only for retained earnings, equities, and long-term liabilities. Historical currency translation causes the slowdown because what the system is doing is going back and forth looking at every single day, its currency exchange rate for each transaction, and calculating it. As a consequence, you get a little delay.
Tree design. When you create a report with many levels in a tree (let’s say, 20 columns, 50 rows, 20 units) – then you are actually creating a report with a thousand of fields, some of which may be calculated 20 times – which eventually takes a lot of time. Hence, consider performance affection in case you are running a tree with a lot of levels.
Running reports in transaction detail. There may be some accounts in your chart of accounts that have many transactions and might take a little bit longer to process. Hence, be aware that when you are choosing to run a report in transaction detail, and you include those specific accounts that may have lots of in and outs, or lots of transactions – you may face delays as well.
Data Mart
There are two main services for financial reporting. We have the application service and the process service. The application service is interactive and it responds when you are making requests from either the ClickOne Report Designer or when you are interacting with the financial reports on the web pages. This service stores and retrieves data from the financial reporting database for your use.
Financial reporting process service, on the other hand, is not interactive and acts sort of like a batch – it processes the jobs for report generation or for data integration. It brings metadata from the AOS and financial data from the operational database into the data mark for use in financial reporting.
Financial Reporting Data Mart
Data Mart is utilized to generate reports using data that is synced from the general ledger and the financial tables into the Data Mart. We basically run the reports against this Data Mart in order to avoid excess load on the online database.
It is also shall be mentioned that change tracking is used to improve the performance of the sync through incremental loads. We make use of a system account in order to gather metadata from the AOS service. So, particularly, the FRServiceUser account is only used to communicate between financial reporting and the AOS services.
Data Mart Reset
Since the Data Mart is a copy of the operations data, it is possible to reset the Data Mart if it becomes out of sync. Reset is going to clear all the data that came from operations but it will maintain the things that you created in the Report Designer, such as your report designs, user groups, port schedules.
Generally speaking, a Data Mart reset is not going to be needed to be done manually. All the automated processes that you would start from LCS will include the reset if it is needed. For example, when you refresh databases from production to sandbox for testing purposes, the two databases are copied in sync, so there is no need for a Data Mart reset. Or, if you are importing a database alone, then the reset will be automatically performed as part of the LCS process.
An exception to this might be if you are working with the development environment where you are doing some of these operations manually. In that case, you might need to do a reset to keep things in sync.