Background
Some of you may be familiar with transaction F.01 or trial balance in ECC. At its simplest, it's the sum of all accounting debits and credits broken down by chart of accounts, general ledger account, company code, and some additional fields or dimensions. In other words, it is the sum of all financial accounting that ECC has recorded in your production instance general ledger that serves as a basis for financial statements.
The basic process of accounting and bookkeeping hasn't changed for hundreds of years and during financial monthend close involves a large effort in the accounting department. Or maybe, it had until HANA has arrived on the scene.
Think of this. What if we were to put the most important subtotals (ending balances, flows, etc.) into a Hana cube to see how quickly we can navigate the dimensions through one of the simplest reports available or the out-of-the-box Excel Pivot Table? The current Excel row count limitation of 1,048,576 rows is not affecting us provided we stay within the manageable number of subtotals and try not to get everything at once from the Hana cube.
What I have done is to provide the 7 steps to achieve ECC F.01 reporting with some drilldown capabilities tested on a cube built out of 84 spreadsheets with about 60 million financial records. The screenshots below are limited to show only 7 records, but the steps themselves don't change even with 7 orders of magnitude with performance still acceptable, especially when compared to standard ECC queries. And one such query is how the seven steps begin.
Seven Steps
1. Select relevant fields from the ECC BSEG table: Fiscal Year, Company Code, Accounting Document Number, G/L Account Number, Amount in Document Currency, and Debit/Credit indicator.
2. Run a SQ01 query to limit output to around 1,000,000 records per CSV file (here only a sample of 7 records is shown).
Standard ECC steps of tranfering ABAP list into a spreadsheet are shown next.
The final download in the CSV format and the file's directory are shown here:
The CSV file when opened in Excel puts values in separate columns. Since BSEG shows only absolute values a simple formula allows a quick quick conversion of 'H' values into negative amounts.
3. Download the CSV files into a directory from which HANA can read. The details will depend on your landscape details and should be provided by your Basis administrators.
4. Run a simple SQL script to create a column table that will be a foundation for our cube. Left hand side shows all the files needed for creating the column store table, directory for the script and data processed by the script. The right-hand side shows all the detail required for the upload to succeed.
The table is created and is ready for data input.
5. Populate the HANA table with records from your CSV files.
Successful upload results with black message with runtime details.
Records as stored in ECC with one modification are now entered in the HANA table.
6. Create the cube in the analytical content section. All the attributes are fields other than the amounts with the amount going to the Measures folder. This is effected by simple field dragging from the table into the folders of Private Attributes and Measures.
The cube needs to be validated and activated, sometimes more than once to make sure the Excel MDX engine can read the values coming from HANA.
7. Create the cube connection in Excel to bring the subtotals into Excel pivot table.
This is standard Excel with HANA connection in the background.
At the end of connection an Excel worksheet cell needs to be selected for the OLAP Pivot Table to summarize the values into.
This is the simplest and most aggregated subtotal calculated based on the origincal ECC BSEG record extract. From this point on, slicing and dicing by dragging the fields within Excel is possible through the MDX integration with HANA.
Your Pivot may be different depending on Excel version or whether you are using Attributes or not. Here the final Trial Balance is displayed.
Conclusion or the main benefits of doing HANA reporting in Excel Pivot:
1. No need to create custom reports at any intermediate step listed above.
2. Use simple pivot tool which comes with each excel installation "for free".
3. Leverage column storage of HANA with ease of use of Excel to make millions of auditable records available to end user.
4. End users will never ask you to write another report after seeing this but it's up to you if this is a benefit to you and to your customers.
1 Comment
