In this blog post, I describe a challenge we’re having with SSAS cubes. In this case, the challenge isn’t caused by technology. It’s caused by data.
Here’s the situation: Our client has used SSAS to build several cubes for reporting, which they’re using to replace a no longer supported version of Business Objects. We’ve been asked to review the Business Objects reports and create new reports using SSRS with these cubes as a data source.
While my client has some technical challenges and room for optimization, my technical folks are handling that aspect of the project well. But the project also has a larger challenge: The client has two different SSAS cubes, one built from sales data from the order processing system and one built from general ledger data. And the two cubes don’t always agree. This is a problem. Only the sales cube can give information by product. And only the financial cube can give true profitability.
This isn’t a technical problem. It’s a problem common to every ERP system: What an order processing system considers “profit” isn’t what gets reported on the financial statement. There can be multiple reasons for this:
1. Shipping Goods vs. Recognizing Revenue
Just because you’ve shipped goods DOES NOT mean you can recognize revenue. This company is a manufacturer. In general, manufacturers and wholesalers don’t have complex revenue recognition issues, so when the order system ships something, the company can recognize revenue.
But sometimes a manufacturer, because of supply chain issues, needs to ship something before the customer wants it. And per GAAP, you can’t recognize revenue for something that someone doesn’t want. So, you need to make an adjustment. And often, this adjustment is made only in the general ledger so the order system knows nothing about it.
2. Allocating Costs to Inventory
A regular order processing system can only report profit by product based on costs allocated to inventory. But for a whole series of reasons, not all costs get allocated back to inventory within the sub-ledger but still hit the general ledger.
Let’s take freight for example. Depending on how the inventory was originally “made,” all freight costs may not have hit inventory. This may be acceptable if freight is fairly consistent over the range of products sold. However, if freight varies, and it’s not properly added to inventory, then you can have a big discrepancy between what comes out of the order system and the general ledger.
3. Costs Beyond COGS
Even if your order processing system has good inventory costs and you can report accurate gross profit, you can be sure that’s not everything you’re spending on every product. Marketing costs, whether consumer coupons or special promotions to wholesalers, are often assigned to specific products. But even if you code this data to a product, brand or line of business, it’s probably only in the AP system and, therefore, not in either of the cubes!
Fortunately, you can solve these problems. Sometimes, you can tie ledger transactions back to the subsystem, so you can at least reconcile profit coming from the order system with what is based on ledger-only adjustments. Other times, you can find ways to code product or customer information in the general ledger.
But whenever you’re building a new data mart, you need to consider these issues before you start. You don’t want to be surprised that the numbers don’t agree after you’ve built everything.