Matt Felzke
Useful Business Intelligence Add-on Products for Dynamics GP

Ever wondered why third party products are necessary with all the features and functions in Microsoft Dynamics GP for data analysis and management?  This article will explore the concept of best-in-breed software for corporate performance management.

Recently, I saw a user on LinkedIn pose a question, asking fellow Microsoft Dynamics GP users if they had some documentation regarding why the Enterprise Resource Planning (ERP) system needs Independent Software Vendor (ISV) products.  It was an interesting discussion, perhaps a challenge to ISVs, and a legitimate question for Microsoft fans.  Several people chimed in, and a couple recurring comments seemed to organically surface.

First of all, a few people mentioned that a software manufacturer cannot create a product that speaks to every single company need.  Much like Apple iPhones allow the user to download Google Maps, even though they offer their own mapping application, so does any modern, flexible ERP system, like Microsoft Dynamics GP.  And as each company who is using Dynamics GP has unique needs, specific budgeting requirements, regulations, and goals, the product’s strengths and weaknesses will vary from company to company.  ISVs allow companies to personalize their data management and analysis needs.

Another trending comment referred to the term, “best-of-breed.”  PC Magazine defines best-of-breed as “the best product of its type.”  The best product of its type is arguably impossible to create in all functional areas as a manufacturer, so “organizations often purchase software from different vendors in order to obtain the best-of-breed functionality for certain key business areas.  While ERP vendors provide a wealth of applications for the enterprise and tout their integrated system as the superior solution, every module may not be best-of-breed.  It is difficult to excel in every niche.”  In other words, even as loyal fans of Microsoft Dynamics GP, it is still okay to invest in an ISV product to accelerate the ERP to meet your specific company goals.  But what is out there to take you to the next level?

If your loyalty to GP runs deep, you honestly might not even know what is out there to take your data storage, analysis, consolidations, budgeting, and dashboards into the modern era of dynamic, powerful, and intuitive Business Intelligence (BI).  That’s where this blog comes into the picture.  This article will highlight the different areas where ISVs are providing enhanced offerings for Microsoft Dynamics.

Perhaps the most historically relevant ISV offering has been reporting capabilities.  I have blogged about Dynamics GP Reporting Tools before, and their relevance will not be fading anytime soon.  Dynamics GP’s built-in Report Writer pales in comparison to the accelerated offerings on the market today.  In 2014, there are products offering accounting logic, sub-ledger reporting, reusable report templates, and live integrations to Dynamics GP and other data sources for easier to use, more comprehensive analytics.  Some are Excel-based, some are web-based, and some have both capabilities – for mobility and accessibility.  And speaking of mobility, a few ISVs offer reporting mobile applications.  Similar to reporting, budgeting is also a popular offering that budget managers are utilizing to build a best-of-breed software approach to meet corporate performance management goals.

When it comes to budgeting and forecasting, there’s no point denying that this process can be a real pain.  A lot of back-and-forth e-mails, concerns about privacy, the distribution of spreadsheets, manual, homegrown budget building in Excel – there’s no wonder people would prefer to avoid the task altogether.  However, this important planning process is simplified by ISVs offering easy-to-use, Excel and/or web-based, accounting logic-powered solutions.  There are plenty of software options that allow for streamlining and ownership, while also focusing on secure distribution.  However, if Dynamics GP users are asking why they need to acquire ISV products to enhance the ERP, they are probably wondering why the trusted Excel needs any supplementary products as well.

As popular as Excel is, lots of ISVs are manufacturing Excel add-ins that not only accelerate Dynamics GP, but also adds features and functionalities into Excel with a ribbon at the top – and without having to learn a whole new set of formulas and formatting.  In terms of reusable templates and accounting logic that streamlines the process of building a report and/or budget (among several other features), Excel is not built to be that high functioning.  You can generate graphs, charts, and other data visualizations in Excel, and yet, Dashboards are the most sought after BI tool for CFOs on the market today.

Dashboards or data visualizations take data and illustrate it in interactive, easy-to-read charts, graphs, and scorecards.  While you can build dashboards in Excel from data in Dynamics GP, that is not the focus of either software, so it is not very advanced.  ISVs have stepped in to fill that void – and it has paid off for companies who have developed vibrant, powerful, intuitive, business user friendly dashboards.  Gartner reported that Dashboards are the #1 BI tool for buyers today.  As data continues to grow, we’ll see that people are not only focused on how to visualize trends and trajectories of company data, but there is also an emphasis on how to store it.

Whether it is an Online Analytical Processing (OLAP) Cube, Data Mart, or a Data Warehouse, ISVs are responding to the need for companies around the world needing to store the massive amount of data for record-keeping, analysis, and any regulations regarding their industry.  OLAP cubes are not an open, relational SQL Server platform, so the product requires someone with OLAP cube knowledge to manage it.  They are relatively prevalent, so it appears that there are enough people in the workforce to meet this need.  On the other hand, some ISVs have taken the historically development team oriented project of data warehouse designs and made it into a fully built, configurable SQL server product for companies to implement.  Data warehouses can store more than just financial data – and data can be replicated to the warehouse on a regular basis or with the push of information.  Running reports from data storage sources can mean a higher performance experience, especially as multiple users are not slowing down Dynamics GP’s server by running live reports.  When it comes to ISV products and their rightful place in the marketplace, it is all about identification of businesses’ specific needs to collaborate in a streamlined, powerful, yet business user friendly way.

There are lots of products on the market – and this article did not exhaust the list of useful offerings by ISVs, but it hopefully added to the conversation regarding why Dynamics GP can be enhanced through BI software.  You will have to decide what niche functionalities are specifically important to your company and research the options to make your company more efficient, more effective, and ultimately, more successful.  Solver, Inc. is happy to answer questions and generally review BI360’s easy-to-use Excel, web, and mobile BI tools with both real-time or data warehouse integrated analysis, budgeting and collaboration as a way to accelerate company performance management beyond Microsoft Dynamics GP.

Matt Felzke
Financial Consolidations for Microsoft Dynamics and Other ERPs

A financial consolidations tool is essential for any parent company that manages subsidiaries and wants unified reports to analyze overall company health.  This article will explore the key features and functions of today’s financial consolidation software.

There are plenty of Enterprise Resource Planning (ERP) system users that are managing multiple companies or subsidiaries under a parent company.  Making sense of data from different entities, divisions, and sometimes, with different currencies can be a logistical nightmare without a professional financial consolidation and reporting tool.  As data becomes a bigger and bigger part of corporate decision-making, CEOs and CFOs of corporations that own multiple companies are looking for a Business Intelligence (BI) solution that includes a robust and business user friendly consolidation module.  For professionals in this boat, it can be frustrating, but specifically for the typical ERP users, there are not too many choices that combine power and ease of use.

 

In terms of accounting, financial consolidation can be defined as the aggregation of financial data from disparate entities into consolidated financial statements.  These statements connect subsidiary data into a clear, straightforward summary with conversions for currency differences and eliminations for any inter-company transactions, among other adjustments that, without consolidation functionality, requires manual corroboration to make sense of the health of the parent company and its entities.  In addition to wanting to avoid manual spreadsheet consolidations, there are several reasons finance teams are looking for a tool to combine more than one company into a single financial statement.

Depending on the company’s specific needs, accountants are looking for a modern, automated consolidation solution for reasons that run the gamut.   Some might be wanting to get away from older reporting and consolidation tools, such as FRx or Enterprise Reporting, that are lacking features and functionalities that speak to modern business demands.  Others are looking to get away from the elderly tools that are too complex for the financial end user, like Hyperion or Cognos TM1, and require full-time IT personnel management.

 

One CFO I spoke to had companies in multiple countries, so there are more specific, diverse national requirements and currency conversions (a prime example would be entering International Financial Reporting Standards to Generally Accepted Accounting Principles adjustments for international accounting compliance – or IFRS to GAAP).  Additionally, there are tools that are more versatile than traditional consolidation options, that can go beyond the general ledger – and are positioned within a comprehensive BI suite that includes budgeting, forecasting, modeling, ad-hoc reporting, dashboards, and data warehousing.  This article will zoom in on the intricacies of the consolidation functionality for Microsoft Dynamics and other ERP solutions – and will discuss what you need to know when moving your BI analytics into the 21st century.

 

As a company, you will want to think about whether you plan to run reports live from the ERP, or if you plan on integrating from a data warehouse.  I’ve written about this topic before, but the decision comes down to real-time data with potential sluggishness, depending on the number of users and size of query on the ERP server versus high performance data integration that is not up-to-the-minute because it requires replication of the data from the ERP to the warehouse.

 

Live reporting and consolidations within the ERP is usually preferable for companies with less complex entity combinations.  Live consolidations are better for those companies that require no (or very simple) currency conversion beyond what the ERP is already doing, that have fewer companies to consolidate, and that are not concerned about the ERP’s server performance when heavy reports are queried.  Furthermore, if you have no interest in bringing in data from other sources and no need to post elimination entries or other consolidation adjustments beyond what can be performed by the ERP, consolidating within the ERP is ideal.  On the other hand, data warehouse or online analytics processing (OLAP) cubes can also host the consolidation process, meeting other companies’ needs more adequately.

 

Replicating data to a data storage source like a data warehouse or an OLAP cube can be a simple manual push of information or a scheduled, routine refresh.  Data from the ERP will duplicate, usually through Microsoft SQL Server Integration Services (SSIS), to the storage space where consolidations can be configured at a higher performance because of its stability.  If you have moderate to advanced currency conversions, a higher number of companies to consolidate, and concerns about how slow the ERP will run based on the reports you are generating, data warehouse or OLAP is the preferable to perform consolidations.  In addition to integration options, you will want to evaluate which consolidation features are most important to meet your company’s needs.

 

Some other factors to consider when assessing what you need from a consolidation function include intercompany eliminations, currency conversion, and consolidation adjustments.  If you are consolidating company data from two subsidiaries that have bought and sold goods from each other, the respective expenses and revenue cancel each other out.  Other elimination areas are found on the balance sheet.  Eliminations solve these statement “falsehoods” that are driven by intercompany interactions, either within the ERP, within a data warehouse or OLAP cube through the BI tool you select to use.  Some BI tools offer fully customizable Excel input forms that can be used for manual elimination and adjustment entries.  Currency conversion is pretty straightforward – multinational companies combine into one report with one parent company reporting currency through this feature.  As for adjustments, whether it is IFRS to GAAP (or vice versa) adjustments to comply with domestic and international accounting rules, inventory updates, or temporary correction of incomplete already submitted subsidiary data, you have options to perform these features, depending on which way you are required to submit statutory reports.  And that’s not all – when consolidating multi-company data, there are still more functions that will enhance your processes.

 

Reconciliation, allocations, and modeling organizational changes are additional characteristics of the consolidation process.  If going the data storage integration option, once the data has been loaded to the source, many organizations prefer to have either the parent or the subsidiary staff reconcile the imported data to ensure everything is correctly input, which can be done within the ERP, the BI front-end tool, or in the BI data storage source itself.  Some companies have to associate all expenses or revenue to divisions, departments, and/or subsidiaries.  These allocations can be performed within the ERP system, as a part of loading the data to the BI tool, or some products will allow you to design allocation reports of varied complexity as part of the data warehouse or OLAP cube.  In terms of modeling organizational changes, if you need to see the impact of acquisitions, divestitures, or internal reorganization, some data storage sources will allow you to copy and change an unlimited number of corporate hierarchies or trees to help model the to-be roll-ups.  This modeling functionality allows you to decide what projected outcomes might be for changes within the subsidiary portfolio.  So much to consider in terms of features, but all are relevant to ensuring you get the most out of your investment.

 

Solver, Inc. is happy to answer questions and generally review BI360’s easy-to-use, Excel-powered consolidation tool with both real-time or data warehouse integrated analysis, comprehensive reporting and collaboration as a way to accelerate company performance management.

Matt Felzke
F9 and Other Excel Add-in Report Writers

After decades of providing a simple Excel-based report writer, F9 might be seeing a resurgence of sorts.  But before you rush out to snag this product to replace FRx or Management Reporter, let’s put it to a basic features test.

Originally released in the late 1980s, F9 was initially developed as a DOS add-in and was soon built for Microsoft Windows.  It is a simple Excel add-in financial report writer, and I have heard from a handful of partners that its popularity might be picking up lately.  If you go to their web site like I did, you will see that they position Excel’s popularity as their key sales pitch.  And they’re right on the money.

Excel has arguably been the most popular program from finance teams around the globe for decades.  Part of that has to do with Microsoft’s ubiquity and consistent success, but another significant part of Excel’s popularity is directly related to the easy-to-use interface, formulas, and formatting.  Excel has continued to evolve in ways that continue to gain fans of the veteran program.  Meanwhile, financial teams around the world have been using Excel to track and calculate transactional data, produce homegrown budgets and forecasts, and organize organizational figures for analysis.  Now in the age of Business Intelligence (BI), analytics, and big data, Excel continues to remain relevant as Independent Software Vendors (ISVs) build BI tools as add-ins to the spreadsheet platform.

For the past couple of decades, one of those products has been F9.  There have been mentions of a product resurgence, despite its maturity and related antiquated simplicity.  The reason for this popularity spike is rumored to be a direct result of Management Reporter’s (MR) underperformance.  I have written about disappointment in Microsoft’s FRx replacement before, specifically in regard to alternatives to MR.  In this article, I am going to discuss the strengths and weaknesses of F9 in the context of what to look for in financial report writers.

In terms of what to seek in a reporting tool, I usually employ a simple feature differentiation test.  If you are going to the trouble of looking for an alternative to Management Reporter, it should be an upgrade.  If you have moved from FRx to MR, you have already jumped through the hoops of migrating to a new system, despite how disappointingly similar they are.  The feature test I would suggest asking of an Excel add-in product involves the following features: is it live on the ERP database or does it report off of a warehouse or OLAP cube; does it have a web portal and a mobile application; does it offer a fully built, configurable data warehouse that can gather data from various data sources; and is it positioned within a full BI suite?  Let’s put F9 to the test.

We already know that F9 is an Excel add-in that does not require a manual export of data from the ERP because it is linked directly.  In terms of ease of use, Excel formulas, formatting and generally functionalities are common to most finance teams, starting back in college usually.  However, F9 is a first generation Excel add-in, meaning that it is older and more static in nature, without the capability to utilize dynamic coding of rows and columns.  An example of the latter would be the ability to list twenty-four month across columns in the report with a wimple range formula in ONE column, without actually have to hard code all the twenty-four columns in Excel.  Later report writer generations employ business and accounting logic with dynamic rows and columns, can report on both GL and sub-ledger data, and drill-through to detail everywhere in the report.  More modern reporting tools allow you to get right up close to your data for easy analysis, and that relates to data integration as well.

Data integrations, live or from a data source, can make a big difference in how reports are generated.  F9 integrates live to the Enterprise Resource Planning (ERP) system, reporting on the GL.  Running live on the ERP means that you will get real-time analysis of data input into the ERP your company is using.  However, depending on the number of users querying the ERP for data and the size of the query, the ERP server can slow down substantially, perhaps defeating the purpose of a reporting tool that is supposed to make things faster and easier to manage.  On the flip side, data warehouse-based reporting requires replication of information to the source before the user can run reports from the BI database.  Integrating to a data warehouse is great for stability and high performance, but it is better for things like daily, weekly, monthly or scheduled analysis.  However, while there are pros and cons to both, F9 only offers GL reporting and a simple GL reporting data mart – and some products offer GL and sub-ledger reporting as well as reporting on full data warehouse databases.

F9 can only integrate live to the ERP, and with around 150 integrations, it is no surprise to me that analysis is exclusively for the GL.  With that breadth of ERP integrations, it would be a timely endeavor to go deeper than just the GL.  More specifically, F9 only focuses on financial reporting, without an ad hoc query functionality.  There are few Excel add-in products that can offer both types of functionality, but again, depending on your reporting and analysis needs, why not truly upgrade to both methods of data reporting?  Why not seek out the solution that has the capability to report beyond the GL – to the sub-ledger and other data sources?  F9 is a fine tool, but there’s more to consider nowadays.

The next two features are perfect examples of today’s key features.  A web portal and a mobile application are two excellent responses to the around-the-clock, global nature of the business world these days.  With a web portal, the finance team can analyze company data anywhere they can connect to the internet.  Similarly, with a mobile application, analytics can be run anywhere that you carry your smartphone.  F9 does offer GL reporting in a web reporting portal, with an emphasis on data visualizations, and it does appear to be Excel-powered, but not with the full, live F9 report writer.  They have yet to add a mobile application, which is limiting for the road warrior executive, but perhaps they will in the future, as this feature will become increasingly more important to the business world.

The final two aspects are interrelated: a fully built, configurable data warehouse and positioning within a full BI suite.  In terms of data storage, most reporting tools integrate from an Online Analytical Processing (OLAP) cube, which requires personnel with the IT skills to manage the cube.  On the other hand, data warehouses used to be development projects, but are now being offered as a complete product.  F9 only integrates live and with a simple GL reporting database, so data warehousing with multiple data sources is not even addressed, but it should be in this age of big data, which brings us to the concept of a full BI suite.  F9 offers GL reporting and simple budgeting at the GL level, but they are missing features you will likely need –different integration methodologies, beyond-the-GL reporting and budgeting, a data warehouse, and a mobile application.

There’s a lot to consider when choosing an Excel add-in BI tool, but I recommend employing my features test.  F9 is a simple tool that offers some basic features, but it appears to be a little too simple and it was designed in the 1990’s as a first generation Excel add-in without dynamic ranges in rows and columns.   If you are looking to replace FRx or MR with a true upgrade, Solver would be happy to answer questions and generally review BI360’s easy-to-use Excel add-in reporting solution positioned within a full suite of BI tools for collaborative, streamlined decision-making capabilities.

Nils Rasmussen
Better Reports = Better Decisions

If you took a poll to find out which financial reports businesses most frequently use to monitor their performance and decision-making, you’d probably get hundreds of answers. But if we drilled deeper into the questioning, we would probably start seeing the 80/20 rule take effect. In other words, 20% of a company’s reports are used for 80% of performance monitoring and decision-making.

P&L Report

So, which reports would you say are the top 4-5 that truly help you get the FULL picture (albeit at 10,000 foot level of what is going on with the company)?

1. Profit & Loss Report with all the typical columns (current month actuals, last month, last month prior year, budget, year-to-date, etc.)?

2. Balance Sheet?

3. Cash Flow?

4. Narrative management summary report?

Narrative Report

5. KPI/Scorecard report for the top metrics?

6. Sales report showing e.g. the top 50 sales transactions for the month?

7. Payables report showing e.g. the top 50 payments made this month?

8. Receivables report showing e.g. the top 50 aging receivables?

9. Graphical trend reports or dashboards?

10. Other reports?

Aging Report

Assuming you have a set of “favorite” reports that you and your managers consistently use to help manage the company, here are the next questions:

•  Do you have the technology to produce all of these reports as part of an automated process, or is it a manual process to pull everything together and to deliver it to your management team?
• Do you have a process to capture the discussions once managers have reviewed the reports?
•  Do you have agreed upon thresholds in your report package that can trigger management ACTIONS based on the thresholds (for example, if free cash flow passes a certain level, a manager can make a certain investment without delaying the decision with approvals and meetings)?

Revenue Analysis


Hopefully this could spark ideas for anyone trying to put together the ”ultimate” management report package.

Nils Rasmussen
Big Data Analysis and Online Marketing Costs

Have you ever wondered what the cost is for your company’s web marketing efforts? In the following paragraphs we will look at an example where we analyze web site traffic and mix this data with the online marketing expenses tracked in the accounting system.

The first thing you would do is to download web site traffic data from your Web Analytics service provider. For example, this can be Google Analytics. If you plan to do this very often, you would connect to Google Analytics using a web service. If not, you can also download the data to e.g. an Excel file, and then import it to your data warehouse where you combine it with the general ledger data, where marketing expenses are tracked.

If you own BI360, you would typically use the BI360 data warehouse as the data store where you load the web statistics data and the general ledger data used in the examples in this blog.

Once you have the data in the data warehouse, you can use the BI360 report writer to create reports that combine the web statistics and marketing expenses to calculate metrics such as Average Marketing Costs per Web Visitor, and then display the result as a formatted report or a dashboard.

Web Statistics – Report

Referral Site Analysis

Nils Rasmussen
Reporting: Start by Replacing FRx

Yes, replacing FRx is going to become a timely matter. As Microsoft continues to phase out FRx—and the upgrades and enhancements that come with it—you’ll soon find yourself looking for a user-friendly, excel-based reporting solution for leading ERPs.

When considering an FRx replacement, look for a solution like BI360 that’s completely Excel-based, with predefined templates and user-defined reports that run live on Microsoft Dynamics AX, GP, NAV, SL, CRM, Sage MAS 500 and X3, Epicor Prophet 21, and SAP Business One databases. BI360’s out-of-the-box integration and ease of use gives IT and financial professionals a seamless replacement for FRx that delivers more on every level:

These are just some of the reasons BI360’s report writer is a great replacement for FRx. For more reasons to replace FRx, join us for a webinar October 11th and see how easy Solver will make it for you to replace FRx.

Thu, Oct 11, 2012 10:00 AM – 11:00 AM PDT

(To see a complete listing of all BI360 webinars click here)

Corey Barak
Live ERP Reporting versus Data Warehouse Reporting

With BI360 organizations can report live from their Enterprise Resource Planning (ERP) or from the BI360 Data Warehouse (DW).  The question is which one is best for your organization.  Each organization needs to determine which approach is best or whether a hybrid approach is best.  This article will give the benefits of using each option.

There are three main benefits of reporting live from the ERP:

  1. Live Reporting: there is a direct connection from many ERP’s directly into BI360, which allows for live reporting on thousands of fields.  This means that once a transaction is entered into the ERP, then it is available for reporting.  There is not a delay or a process that a user needs to wait for.
  2. Cost of Configuration: BI360 is already pre-configured for thousands of fields in many ERP; therefore, once the software is installed a person can start building reports.
  3. Metadata: as mentioned, there are already multiple modules and thousands of fields already available for many ERP’s, such as all Microsoft Dynamics products, Sage MAS500 and X3, and Epicor Prophet 21.  Standard modules and fields are constantly being added at no cost.

The BI360 Data Warehouse offers many advantages as well:

  1. Speed: using the DW enables organizations to perform many tasks to speed up reports, such as indexing, using a different server which is recommended, and using the flexibility of the DW to enhance report building.
  2. Report Design: Building reports directly from ERP requires hard-coding many times, such as accounts.  For example, cash would be a specific account range and then accounts receivable would be set up as another range.  The DW allows an organization to create trees or attributes so that the report design can point to current assets and the report will dynamically expand.  This will limit the number of groupings, which will greatly enhance speed. Please refer to the White Paper BI360 White Paper – Using Attributes instead of Account Ranges.
  3. Consolidation: consolidating multiple companies from the ERP requires the multi-company tool.  The DW allows companies to build hierarchy trees and report on any level of the tree.
  4. Combine Multiple Sources: gives the ability to combine multiple data sources and report from them using one connection.

There are many advantages to using either solution, but we have many clients that use a hybrid solution.  The recommendation would be to clearly define what reports would be built connecting to the ERP vs. the DW, so that there is no confusion within the organization.  An example of a hybrid approach would be using the ERP for subledgers and the DW for budgeting and financial/operational reporting.

 

Seiji Naganuma
Take Advantage of Building Your Reports within Excel – Steps to Create Tree Navigation

In organizations with many business units (or many products or projects or any other business dimension) it is desirable to create a report output where the first sheet is a summary (“consolidated”) sheet and then the next sheets may be divisional summaries and then eventually the subsidiary or department detail.

Make reports more efficient and take advantage of building your reports within Excel. Learn more about how to create a workbook like the one described above, including an automatically generated “Tree Menu” on the first sheet in the workbook to help users navigate to any report they want in a split second. In the example screenshot below you can see what it could look like when it finished. Example: If you click on the “DivA” hyperlink in the menu it will take you to the “DivA” worksheet.

Note: In this example we are going to simulate a Profit & Loss report that should be executed for a consolidated level, a division level (here: Div A and Div B) as well as a subsidiary level.

To access this document in its entirety, log in to the Customer Portal and download the white paper today!

Solver University> Whitepapers> Reporting> Multi-level Reporting Books with Tree Navigation- BI360 Whitepaper

Nils Rasmussen
Solving the Classic Division vs HQ BI conflict with Multi-level Data Warehousing

In most mid-sized and larger organizations with multiple divisions, classic BI tools have never been good at satisfying both local division needs (detailed reporting, local budget models, etc.) as well as corporate HQ needs. In the screenshot below, you can see an interesting methodology where a company can install the BI360 data warehouse (DW) at each division as well as at corporate HQ and then transfer whatever interesting detail/summary they want from the local DWs to the central DW. Historically, this would often not be a feasible model when companies had to build homegrown, custom data warehouses from scratch, because it could get very expensive. With a pre-configured data warehouse like the BI360 DW however, it is very fast and low cost to install it anywhere in the organization where a data mart or data warehouse is needed. There are many other benefits with this multi-level DW architecture, including the ability to then use the same report writer, budgeting and dashboard (like BI360) technology everywhere, thus reducing training cost, license cost and more.

Seiji Naganuma
How To Improve Reporting Performance

One of the most common issues with reports containing a large volume of data is the report run time can take a longer time than expected. BI360 Version 3.5 has a new feature that has been added to enable parallel queries so that the SQL Server can be utilized to its maximum capabilities. Once enabled, your reports will run as fast and efficient as possible and deliver improved productivity.

By default, Report Designer, Player, and Composer do not perform parallel queries. Thus, the queries are processed one by one.  However, users can enable this by setting the Maximum Concurrent Queries to a value other than 0 in the Administration Tool (under System Settings).

Executing queries in parallel can optimize performance when generating a report in the BI360 Reporting applications. The optimal number of parallel queries depends on a number of variables such as memory on the server running the SQL server, the number of processors on the server, and other SQL Server related options. In parallel processing, each query can utilize a processor without degrading the performance of the SQL Server. Thus, if there is a Quad Processor, the initial maximum value should be set to four.

A high number does not automatically imply the best performance. Dependent on the different variables mentioned above, a peak will be reached at a certain value. The peak could also be dependent on the nature of the report since the number of generated SQL Statements play an important role. A simple report that would generate only one query would therefore not experience any performance improvements by using this option. The best way to find the peak is to simply test different values and different reports.

For certain customers, they have experienced 80% – 90% increase in efficiency when generating their reports.