||Microsoft's BI stack has brought Gartner's 2010 report highlighting the trend of 'data discovery
tool architecture' to fruition.
Director of Business Intelligence
The business required reports on product sales by week, month, or year for customers or groups of customers. Any combination of these needed to be reported and every customer could be different. These reports had to be used off-line when visiting customers
and still be able to slice data in the reports.
Rebate percentages had to be allocated to customers to work out a more accurate cost of goods sold.
Tracking inventory movement between branches and locations was also required, as well as tracking purchases by a vendor, period and financial year.
All these reports had to consist of dashboards, slice-and-dice functionality, graphs and charts, pivot tables in various cube views. The way in which the ERP system stored dates in the database in a text date format also provided a challenge for the users.
It was vital for users to be able to design these reports from scratch and for tool had to be user friendly.
The solution provided gives true self-service business intelligence reporting whereby the users can create reports, slice-and-dice pivot tables and charts. Power Pivot also enables the users to work with large amount of data without compromising the processing
time and speed. Due to the fact that it is an Excel add-in and most functionalities work more or less the same way as Excel, it is very easy to use.
SQL views were created to combine all the sales information from the ERP system to enable users and ensure the integrity of the data reported upon. More SQL views were also created to cover inventory movement and purchases. Any simulations of the data cubes
are now available through pivot tables and pivot charts and more measures can be added for data can be analysed further using Excel's analysis tools. By using relations within Power Pivot, tables can be added for further statistical analysis and also to resolve
problems with date formats and sorting of fiscal periods.
Additional functionality was provide during the project through... ...a link to QuoteCenter powered by Reuters. This is a trading system that provides live share prices and future prices. It feeds information like Symbol, commodity, last trade, bid/offer,
high/low, percentage change, 52 week high/low and more. QuoteCenter links directly to Excel and feeds the data in real time.
The partner, AccTech Systems, deployed this advanced solution using Microsoft's full BI stack consisting of Office 2010, Excel 2010 with Power Pivot and Microsoft SQL Server 2008 R2, empowering the farm with a true self-service BI solution.
“Microsoft's BI stack has brought Gartner's 2010 report highlighting the trend of 'data discovery tool architecture' to fruition,” said Andre Cloete, Director of Business Intelligence at AccTech Systems.
“The theory behind self-service BI is based on the ability to make business critical data available to non-technical users, without the dependency on their IT division, or on resource intensive hardware. “The solution we provided is a true self-service business
intelligence reporting solution. Users can now create reports, slice-and-dice pivot tables and charts according to their individual business unit's requirements without the need to ask the IT division, or a software VAR (value added reseller) for assistance,”
“By adding Power Pivot to the solution, we've empowered our business users to pull up to the minute reports on all their product sales by various reporting periods and for any customer or a group of customers,” commented Fanus Erlank, the organisation's
Financial Manager. “Due to the fact that Power Pivot is an Excel add-in and most of the functionalities work more or less the same as Excel, it was very easy for use to adapt to Power Pivot.”
“We've created SQL views to combine all the sales information from the company's SQL-based ERP system to enable users to analyse financial and retail sales data and ensure the integrity of the data,” went on Cloete. “More SQL views were also created to cover
inventory movement and purchases, allowing the customer to predict future sales and stock movements accurately.”