Vineyard Business Intelligence

THIS BLOG IS CLOSED

After a couple of years of faithful service, this blog retires.
The writer keeps writing on

Click on the link above if you are not automatically redirected

How to make good use of the paper your accountant gives to you (part 2)

In the previous post we discussed the margins as health indicators for your business. They're the simpler and most basic of all the indicators that you can get from the three sheets prepared by your accountant: the income statement, the balance sheet and the cash flow.
Now we focus on some other financial indicators that can tell you something insightful about how is your company doing.

From the balance sheet you can get the Current Ratio defined as

(current assets)/(current liabilities)

It is the ratio of what you collect in the short term to what you have to pay in the short term (that's the meaning of "current"). To make a long story short:

Current Assets = Cash +Bank + Debtors + Bills Receivable + Short Term Investment + Inventory + Prepaid Expenses

and

Current Liabilities= Accounts payable+Accrued expenses+Income tax payable+Short-term notes payable+Portion of long-term debt payable

This ratio measures if a company can withstand its obligation relying on its ordinary operations, in simpler words, if you can pay what you need to keep the company running without raising capital. This ratio must be higher than 1, because current liabilities should pay also for long term liabilities, if any. Keep an eye on this ratio and keep it higher than one.

Also be careful to check how much the term "inventory" weights in the total of Current Assets. In many businesses the inventory has a high value that can't be turned into cash quickly (because what you need to create your product has a very long delivery lead time and you must stock in advance enough supplies to meet the demand, for example). If it's the case, you can write the term off to calculate the ratio; it's a cautious assumption.

Somewhat related to the Current Ratio is the Quick Ratio, which is defined as

(cash + accounts receivable)/(accounts payable)

This is faster to be calculated and can be checked weekly or daily. Its meaning is not much different than the Current Ratio but is more tied to the day to day operations. Once again, keeping this ratio well above 1 is a good, albeit not absolute, shield against a cash crisis.

In the next post, we'll dig deeper and we'll learn about the so called "defensive numbers".

Take Care

Shadow Systems

The term Shadow System recalls to my mind James Bond movies' SPECTRE organization. The truth is much more trivial. Shadow systems are those fantastic gothic cathedrals built with Excel which keep so many businesses running. The IT would happily use a flamethrower on them but they would make the worst mistake possible. Uncovering a shadow system and bringing it to light is an incredible luck, both for IT and the company. I tell you why.

When Alessandro, an old friend of mine, was first promoted to lead the minuscule private label division of the clothing company he used to work for, he faced a hard task. The small division has been operating at loss for more than a year, with a part time manager who had issues far more critical to the company to be tackled. Alessandro dove nose deep to the task and identified the root cause of the problem. That kind of business could become interesting for the customers only with some services attached. For example, a famous motor company had a sophisticated sport dress side business, but did not want to deal with distribution, business expansion, show rooms etc. Luckily, this was exactly Alessandro's specialty. So he contacted the fashion retailers and persuaded them, placed the items on display in every motor related event he could find and sales soared. Customers begun to flock and Alessandro designed comparable services for all of them. While doing all of this, he soon realized that his company was totally unprepared to cope with this specific business. It's main customers were the large generalist retailers for the lower quality segment so, basically, what they could do was take large orders, produce them and ship containers laden with uninspiring panties across Europe. Alessandro required to manage small orders, initial orders and replenishment orders; he had a more sophisticated pricing and had to keep track of all the promotional and business expansion initiatives to price the services correctly. He was doing a modern commercial job with tools focused on the industrial aspect of the business.

At that point he reverted to me to help him fill the gaps, someway. My first instinct was to tell him to ask for money for new systems, but the entrepreneur liked that the division was taking off but did not have any budget for such a funny thing like computer software. Alessandro made clear that we had to go in stealth mode and work with what we already had or could get for free. No use to say, we fired up Excel, downloaded the free SQL server edition, and started designing the system. I'm not going into details here; it wasn't terribly difficult and was one of the projects which placed a brick in the foundations of Viney@rd. In few months, working at weekends and, sometimes, moonlighting, we had something that could be interfaced (with some manual work) with the "official" systems and met perfectly Alessandro's requirements. To tell it with Alessandro's words, it laid perfectly onto the business. His control requirements were entirely met.

This was the typical shadow system, that worked perfectly for a couple of years or so till when the IT migrated from XP to Vista. When they discovered on a secretary's desktop the MSDE installation they went mad. The CIO harshly criticized Alessandro for making use of an unauthorized software, wiped the installation, hunted for database backups and macro enabled sheets on the other boxes and deleted all those too to make sure that nobody could recover the fiendish shadow system. People were also demoted from PC administrators to worthless users.

Alessandro scaled the issue to the entrepreneur; after all, he got results and fulfilled his requirements for free (he was a friend who needed help and the only compensation I had was a gigantic, truffle based, dinner, which he paid himself) and the CIO obsessive-compulsive behavior had no justification whatsoever. Actually, the CIO had better walking the (short) extra mile and doing the few improvements required to fully integrate the shadow system with the rest of the IT structure. After all, I built a corporate grade system, far better designed than a user incrementally created castle; they needed only to open the hatch and have a walk under the deck to realize it.

The entrepreneur's reply was that Alessandro was unable to keep up with the rules, that he could not understand his obsession for computers, and, anyway, he did not like to have him travelling to sell someone else's clothes; he had better staying at the office and taking care of production.
No use to say, Alessandro left in few months, taking the bulk of the customers with him.

What's the ultimate lesson of this story? You can get a perfectly functioning controlling system for a dish of truffles, but you can't have a CIO swallow it.

DISCLAIMER: the picture above is probably copyrighted but I can't attribute it correctly. I'm more than willing to do that or to remove the picture, just contact me about!

How to make good use of the paper your accountant gives to you (part 1)

Every company has an accountant. They are often seen as gray men, with round glasses, with little social skills. Many think that their existence is justified by bookkeeping, which, in turn, is a law enforced requirement, right?

Wrong.

They can do a lot for your business, even preventing critical failures that could potentially put you out of business.
They periodically produce three sheets of paper, the balance sheet, the income statement (or Profit & Loss statement, P&L) and the cash flow statement.

The balance sheet is a summary document which gives a very aggregated view of assets and liabilities, that is, of strengths and weaknesses of the company. It is principally aimed to the external observer.
The income statement summarizes revenues, expenses and other costs and shows you the profit (loss) over a period of time.
The cash flow statement tracks the actual amount of money cashed and spent, and helps you explain why, even if you are selling good, you don't have much money in bank.

Building and analyzing those sheets is a science by itself and requires specific knowledge. Nonetheless, there are some ratios which are simple to analyze and give an insight of how the company is going.

The first numbers to focus on are the margins which come from the income statement.
The gross margin is defined as revenues minus direct costs. The revenues must include only the revenues deriving from the current activity (i.e. do not include, for example, the sale of a warehouse building, that is an extraordinary revenue). The costs must be the sum of direct costs, that is, all the costs that can be directly associated with an item of what you sold (the cost pf raw material to manufacture an item is a direct cost of the item, phone bills are not directly associated, the phone is used for other than selling that specific product).

If your gross margin is negative, you are in huge troubles. Actually, the more you sell, the more you lose money; you should stop what you're doing and reconsider, it's no use keeping throwing money away. This is the worst condition for your company to be in.

The net margin (also known as "the bottom line") is defined as revenues minus all the cost in a given period. If this margin is negative, is no good news because it obviously means that the company is losing money, but it might not be due to bad operations but to other financial aspects. For example, debt interests might be too high. The net margin mixes costs of all natures, both financials and operations-related.

That's why, sometimes, there's another margin taken into consideration, the EBITDA that stands for "earnings before interest, taxes, depreciation and amortization". The idea is to leave out everything that smells financial in nature to measure the operations of the company as whole.

These margins can be calculated for the whole company (this is what accountants do routinely) or for company subsets. Slicing the income statement may become a very difficult exercise. While it is rather easy to slice the revenues for customers and products, as you go down the income statement the figures become more and more aggregated and, sometimes, splitting them is a totally arbitrary decision.
In theory, you can have a net margin by customer or by product. This is the holy grail of VP of sales and Comptrollers, but, as I said before, it may be largely the result of guesswork.

Good comptrollers, though, can reach a very high detail level up to the gross margin, and it's enough to take some good business decisions.

We just scratched the surface of the topic. There's more to say about margins and there are many other numbers to control, but I save the topic for the next post.

Back to Basics II: what�s the difference among dimensions, measures and an attributes?

I keep going back to the basics because I was asked (thanks Mary!) to explain in the same way the difference among dimensions, measures an attributes. Actually this was not from a BI professional but from the comptroller of a medium sized company which is setting up its first organized BI system. She's been told about but she's not sure of having a clear picture. So I'm trying to help.

To understand the difference among dimensions and measures I tell a very short story.
Let's suppose that Mary's boss drops by her office and says "Mary, I need to know the sales amount". Mary is a bit surprised and replies "The sales amount of what? And in which period?". "The sales amount" is a measure.

"what" and "period" are the dimensions.

The sales amount alone is a lousy concept, it does not mean anything, if you do not better qualify it with a period, a group of customers, a group of products etc. The measure is a number which tells us something about the company, the dimension qualifies it and "slices" it according to relevant business entities. Measures can be summarized or used for math calculations, dimension cannot.

I can hear the objections "What? My area sales are the sum of all the salesmen working in the area! I sum the salesmen!". No, you do not sum salesmen, you sum the sales amount of each salesman. You still sum a measure.

The attribute is a tag associated to the dimension. It is something you would never use to slice data. For example, the customer's receptionist name. Would you ever slice your sales to know how much you sold to companies whose receptionist is named Charlene? Receptionist's name is a useful piece of information to keep at hand, but it's not relevant for analysis.

From a technical perspective, it is more convenient labeling it as an attribute rather than adding a useless dimension.

I hope this simple explanation is clear, but, as usual, I'm open to even clearer explanations. A vous!

Measures, Metrics and KPIs ?

I had the occasion to discuss the topic recently and I realized that there's no "consensual truth" about it. They are basic Business Intelligence concepts but I realized They are still debated. Nobody ever cares about the obvious so basic concepts like these give the rise to complex discussions.
What I find amazing is the amount of "it is kinda like�" "it's more focused on� " "it depends�" definitions I've heard. Maybe I'm too naive, but the idea is clear and simple, in my opinion.

A measure is a quantity attached to a fact. The quantity and the value of an invoice row are measures. The number of active customers is a measure. A single transaction value on the ledger accounts is a measure. The tag price of a product in line of purchase is a measure. The type of aggregation and the summary level of the related facts is not relevant for the definition and may vary. Please note that running or plain totals are simply an aggregation method and they're not relevant either.
A derived measure is simply the result of a mathematical operation on simple measures.

A metric is a measure sampled over time. In other words a measure presented by showing how its value changes over time is a metric. There is the typical display of gauges with a slider which shows the metric over time but an excel table with weeks and the invoiced value per week is a good metric representation. A graph with quantities on the y axis and time on the x axis is a metric visualization either. Often derived measures are used as metrics, but it's not a rule at all.
While the measure has no privileged dimension for analysis, a metric is relevant over time first, and over other dimensions in the second place.
Some might think that, from a strictly technical point of view there's no substantial difference in presenting a measure or a metric and the transition between the two is seamless; they're right.

A KPI (Key Performance Indicator) is a metric which is included in a performance management system. That is, a metric with associated targets or oscillation boundaries or any other sort of constraint is a KPI. In a BI system there may be thousands of measures and metrics, but few tenths of KPIs.
The "Key" part of the term refers to the particular relevancy of the metric for the health of the organization. The "Performance" part of the term refers to the associated values that tell us if the metric value and/or trend are good or bad. The "Indicator" part of the name stresses that the metric chosen should be influenced by other metrics and measures and should give an overall view of an organizational segment.
Choosing the KPIs for a performance management system, for example according to the Balanced Scorecard model, is not a technical issue, but a large business project involving the management.

I hope I've been clear enough. Let me know what you think of it and, if you come up with a clearer definition, I need to know!

Enjoy

The Magazination

I just realized that there's more to say about the "magazination" of the web consuming. This form of content organization might be mirrored by BI presentation layers. I usually do not focus too much on presentation; it is overhyped by too many tools because it cheaply attracts users attention. While some forms of analytics are empowered by good visualization, some others simply generate a perceptive overload. The "magazine" form, though, is different

A common presentation format in modern BI is the dashboard. It may include current data presented in some kpi format or organized in tables. It may also include data pushed on event by some sort of event engine. In some ERP systems, a complex messaging an notification system is inherited by integrated dashboards. More detailed and "numerically intensive" information is available through reports or other analytics, accessed from the dashboard itself.

On magazines or newspapers the headlines are usually reserved to information pushed on the event, while internal pages host stock listings, classifieds etc. Often there's a recall to these detailed data on the first page.

In other terms, dashboards and newspapers may appear different but they both share a common perceptive model. Ideas are organized in this way because people think in this way.

How the average, not so technical, manager, acts upon the information provided by systems? Probably she reads the mail first, in search of news, than she'll get the daily reports or will glance to the dashboard. The average manager hardly, or ever, drills down or filters data to investigate on them. She will ask an analyst (maybe that's not the job description but the business role is such) or, worse, will ask the IT for raw data to crunch in Excel (just to complain later that the figures on Excel are very different from the dashboards).

In this scenario, how could the "magazination" of the interfaces be of help? We have seen that some paradigms are shared so where's the difference?

Words.

The terrific difference is that magazines and newspapers express their ideas in words. Think to the economic press: few graphics, few tables, some infographic and lot of words to describe stock variations or the health of a company. If we could create an analytic engine which could describe the figures also in plain English, I'm rather sure BI will get a tremendous boost in credibility among the upper management.

Newspaper organize the news according to their perceived importance, is any BI tool capable of doing the same? No, and this is one of the reasons why BI is perceived as complex by business users.
News are often accompanied with background information which tell the whole story to the casual reader. Are we able to do the same with BI tools? Aren't these the information we are often accused not to provide promptly and clearly?

What do you think? Am I totally fool? (I'm sober, I can assure you) Do I have something here? Please, let me know!

Pads, slates, tablets and Business Intelligence

There is hype everywhere for what is perceived as an entirely new device class because of the Apple iPad launch. They did exist before (Microsoft has had tablet editions of its operating systems for years) with a limited audience, but now they have the attention of the general public. So many consumer tech players have started creating their own version that it appears that those devices are here to stay, at least for a while. So, what's in for BI?

The key points of these devices is mobility, so their challenge has similarities to what is already going on with mobile BI. I'm not focused, as usual, on the technical challenge of bringing nice looking analytics on a screen too small, but on the business options it enables.
On mobile phones, today, we have few crucial figures which are required on the go. With such a device, if connected, we can have much more data. A decent screen is a decent place for decent dashboards and reports.

So it appears that these devices are a step toward an ubiquitous BI. Software developers should ensure compatibility, and likely make choices among them because, in this early stage, there are going to be many different standards.

I can hear your yawning from here. So, the same old stuff, just more mobile and more portable. Did you really need a blog post for that? No, and yes.
Everything started with an iPad application named Flipboard which turns your social media experience in a magazine. Links are organized as in an online newspaper, pages can be browsed and content can be grabbed to be consumed inside the application. There are web equivalents like the amazing Paper.li on which I publish the equally amazing :o) Business Intelligence Daily.

This is an entirely new content aggregation model which is MUCH more natural than any dashboard, gauge, tables etc. The human brain is up to one topic, than up to another etc. We also need a brief summary to decide to dig deeper or not. If this summary is expressed in words it is much more impressive. The magazine form let you think that what's important is pushed to your attention; does a dashboard include all the relevant measures? Etc.

Today, at my best knowledge, there's no BI software which can organize information is this way but we should all start thinking about it. I'm impressed by the familiarity of use conveyed by this software. I do not know if it's a matter of consuming content from disparate application directly providing content in this form, but I think we have a small revolution ahead.

What do you think about? Do you agree or not? Let's discuss!

disclaimer: all the images an trademarks are property of their owners.

What is Business Intelligence

The new season is gearing up after the summer break and, while 1.2.1 is undergoing an extensive test, I go back to the fundamentals.
As you know, I'm not prone to deep, philosophical, discussions and I tend to be practical. You also know that I always thought that Business Intelligence is not an IT topic but, primarily a business topic. I've said it before: a BI project is not a technical project; it is a business project with a strong technical side.

The end user is always the analyst, the controller or a manager. They have requirements, problems to solve, pains to ease. Usually they do not know anything about the technicalities and do not care. In my career I've seen many times users perfectly happy with horrible technical solutions. When a sound architecture was deployed, satisfaction actually decreased.

So every definition of the BI scope should not pertain to the technical environment but to the business area. Business Intelligence is a business area like accounting or procurement. It is undoubtedly a support function and surely is not a step in the value chain model but it supplies value, indeed.

My Business Intelligence definition relies on a very simple company model illustrated in the picture below.

Each single activity in the company pertains to two very big areas: operations and control. Operations is "what" is done, control is "knowing how and why" it is done.

Business Intelligence is the sum of all the things (applications, data, activities) required to support control. If BI should be opposed to ERP, we could say that ERPs support operations.

In my opinion is all that simple, there's no particular need to dig deeper because, to an IT manager this difference should clarify the boundaries. Limited overlaps may occur when, usually with limited amount of data, the ERP can supply global figures or BI technologies are used to feed operational data. These cases may be decided upon as needed.

I hope this short definition might be of use when describing BI to the unaware or while organizing internal resources.

Enjoy.

What�s new in version 1.2.1

It's almost time to unveil Viney@rd version 1.2.1. It includes a couple of new features which were true open points for those who work with Excel. Our target is to make their life easier an easier and this is the compass we follow for improving Viney@rd.

The most important feature is the subtotal feature. It builds on the corresponding Excel feature (remember, you already bought it, I do not want to sell it again to you!) to let you define a subtotal level and an aggregation function directly on the query form. The query result set will be broken like you selected it and applied the subtotals yourself directly in Excel. Even the outline will be there, to close and open details.

The second feature I implemented is assigning a name to the area occupied by the query result on the worksheet. These names appear in the upper left drop down command and let you select the areas where your queries have been placed. So queries output can be identified more easily within the workbook. Now it's not actually a feature, but I also used a new set of better looking icons in place of the older ones, which had problems with the transparent background.

As usual, version 1.2.1 too will be available for testing before the official release, so stay tuned!

In the meanwhile, it's not a bad idea playing around with the current version, download it here It's fully functional and free for 90 days!

Inverted Occam Razor Principle

You know me. I usually do not lean toward deep philosophical thoughts; I'm rather the average Joe type. This time, on the contrary, I'm going to start from two Jeffrey Pfeffer's deep and insightful articles from the Harvard Business Review to derive what your grandpa likely told you many years ago. My contribution will be dropping in a smart idea or two.

Jeffrey Pfeffer in this piece about power in the company environment and in this other on caring about own career, makes some hard points about life in a company.

The corporate world (and the world itself) is fundamentally not just.

The quality of your career depends fundamentally on your political skills.

If you do not take care of yourself, nobody will.

If you do not defend yourself, you'll be attacked.

In other terms, the entire idea of "do good and you'll be rewarded" is fundamentally wrong.

As a European, probably, I'm more prone to accept those points as facts of life and not to ruminate about them.
I started long ago to successfully apply my personal "Inverted Occam Razor" principle: if a fact has two equally viable explanations, the wicked one is true.

If these principles are true, there's an unintended consequence: the better your career is, the stronger your political skills are.
Unluckily, those at the top should be those who better understand a business and have clear ideas on how to make it prosper. The system, from this point of view, is fundamentally flawed, giving power to who has skills which are not related at all with the good, wise and cautious business management.

As in all human things, there are exceptions. Maybe they're not so few but I believe that Pfeffer depicts the "standard scenario" of the western corporate world.

So: what you, a small crank in a huge machine, can do to make your work life better?

Simply get out of there.

There are a lot of ways to make a living without a job in a "Darwinian on steroids", soulless, company. They're often a good place to learn how business can be run (from the lower layers, of course), to specialize in a field and to acquire experience of the world, but you didn't marry it.
After some years in a company, usually a good move is going solo. Just use what you learned to create your own business and start moving at your pace, according to your rules. Many did that, and there's surely an alternative for everyone pursuing it. Personally, I'm not there yet, but, as you know, I'm well on my way.

No use to say, if you're the kind of person who values corporate career above everything, please, move on and you'll be welcomed by the community of your peers.

This takes us back to the initial point. I'm pretty sure your grandpa, once, put his hands on your shoulders and said "My son, do not waste all of your life working for someone else like I did."

My one did it, and he was right.

Sneak Peek at the new web site

This is a very very primitive idea for the new site. Please, feel free to express your opinion! I'd love if the site were the outcome of shared ideas!

Slicing and dicing the income statement (4), costs

We have seen all the main issues regarding the high section of the P&L statement. We have learned that each and every line should be analyzed according to responsibility criteria. We have also seen how measures should mimic the sales events.
Now it's time to go below the net sales value and see what to do with costs.

There are some different approaches on how to structure costs in a P&L and, once again, they depend on the company business model. The implementer is given a list of measures in a specific sequence, but what's the rationale behind that? Usually the cost structure follows two guidelines: identify some levers the management can pull and spot external events which are not under management control but influence the margins.
We'll be discussing costs for many posts, because a large part of the managerial accounting doctrine is about cost control. Rather than doing a long theoretical introduction, we'll see some commonly used costing models.

The easiest costing schema is listing direct costs first and indirect costs down the row. Direct costs are those which can be directly traced to a product. The cost of raw materials used to manufacture an item, the cost of the people who manufactured it is a direct cost. These are usually very easy to calculate. If they're not given, a simple division will get the result. The cost engine of many ERPs does the job easily. Indirect costs are all those costs that can't be traced directly to the single product. For example, the plant director's salary can't be traced directly to a product. To associate them to a product, a process called "Allocation" must be applied; we'll talk about that in later post.

Actually a cost can be defined as direct if it can be traced to a "cost object", that might be a product, a unit of service or even an employee. The same plant director's salary becomes a direct cost if we refer to the employee.
So, why are we talking direct costs to product? Because the product usually defines the lowest detail level in the orders or invoices which aggregate into the P&L. As said before, the ultimate purpose of a P&L is to evaluate a customer's or a product's or whatever's profitability. Attach a cost to the product and you'll attach a cost to every invoice row. It will easily roll-up into aggregated costs.
Note that the role of the product may be taken by whatever takes its role at the lowest sale level. It may be a working hour for a specific professional figure, a 30 seconds clip on the local radio and so on. You should always have somewhere in the company systems a table that says "sold n items of the x thing", and x is the object to refer the costs to.

What are the most widely used direct costs? As usual, it depends on the business model, but let's try to do a list with the usual manufacturing company in mind (we'll have different examples in the future).

Commissions: they're often calculated as a percentage of the sale, so it's easy to associate it with the product. There are more complex environments but, ultimately, they're linked to "what" is sold, that is, the product.

Cost of goods sold: these are the costs related to manufacture (or buy) what is (re)sold. They are usually split into: Materials: raw or semi-finished purchased to manufacture the product. Given the product components, these can be precisely defined. Usually they're calculated by the ERP cost engine. Machines: the cost of running the machines for a single product item. These are harder to calculate but they're usually pretty linear with the production. A limited cost accounting process may be necessary to define them but often few divisions are enough. Manpower: these are the salaries of the people who are directly involved in manufacturing the product. Given the time spent to produce a single item, those costs are easily calculated from salaries.

Freight: it's a direct cost because it refers to a specific transport, but it may be difficult to calculate because of the freighter pricing. Usually, some simplifications offer a good approximation.

These costs are often used because they're the easiest to be calculated, and they're already somewhere in an accessible format. These are also the old school costs because they relate to levers easy to pull.
Is the workforce cost too high? Let's pay less those dawdlers or make them work faster!
Are components costs too high? Let's grab suppliers' ties and pull!
Are machines too expensive? Let's keep working with the old steamers!
I could go on but I imagine that the concept is clear.

See you next time!

A Bucolic Parenthesis

Today I leave aside Excel, BI and other technicalities; I feel like talking about interactions on the internet.
Did you ever notice how, on the web, everyone is so kind? Comment on a blog you'll be acknowledged. Retweet a link and many will politely say thank you.

On the web I came in contact and interacted with people who, in person, I'd never even try to speak to, like Gary Vainerchuck, Robert Scooble, Seth Godin or Claudia Imhoff. They all spent some time discussing with me about different issues on social media or by e-mail.

This is exactly the point, why people, who are usually overworked, who are somehow a star in their field, can be so friendly through the Internet? Why do they bother?

I think I have a good, albeit not exhaustive, reply to this question.

The Internet lowers the physical barrier. All the nonverbal part is left aside and the naked content is exposed. One can immediately realize who's talking to from many nonverbal signals, from the context and the behavior. The communication tone is set from there and, sometimes, there's no communication at all.
On the internet, on the contrary, only content matters. I'm replied or not depending on the level of the insight I put into my communication. I'm replied or not depending on the quality of what I have to say. In real life, I may have an essential point, but my voice is lost into the crowd.

So, if you think that I wrote some quality content, let me know your opinion!

Excel School

I recently learnt that the famous Excel Blogger Chandoo has a new initiative. He set up an Excel School.
http://chandoo.org/wp/excel-school/
This guy is an Excel MVP but his style is what makes the difference. He's light, simple clear and to the point.
If I needed some Excel training, chandoo.org is a place I'd consider.

I'm not affiliated with Chandoo.org, this is simply my opinion, based on what I read on the site.

The Income Statement (3) the sales events.

At the top of P&L tower sales. This is the starting point for each and every analysis. Then you subtract the costs and get the margin, right? Wrong. What about the other sales events?
Better, what is a sales event? There are four main sales events, the orders, the returns , the complimentary offers and the giveaways.

The orders, or better, all the documents that start from quotations to invoices are the main event, these provide the actual revenues and are quite commonplace. I'm sure you already know many ways to implement them in a datamart. I'm not going to teach you how to work with them.

Returns are technically related to orders or invoices, as they're often implemented like an invoice or shipment with negative quantities and values. They're also a cost because there's no profit associated with them and money will likely be returned to the purchaser. Processing returns is also an extra cost, unforeseen at the beginning. What's the correct value for returns? What does managers expect on that figure?
The simplest way to calculate returns value is just getting the associated document values. It is a direct subtraction from revenues, so it perfectly makes sense. A more sophisticated approach might include the related returns handling and freight costs, if they can be identified. They're direct costs but, if lumped together with the equivalent costs for orders (we will meet them in a coming post), may cause indicators and averages to drift.
A totally different game is about, which returns should be included in the P&L and which shouldn't. A return caused by product defects should not weight on the sales manager shoulders. A return caused by a mistaken shipment should be accounted to whom made the mistake. The P&L for the general manager should include all the returns. As you can easily imagine the same responsibility principle seen in previous posts apply here. Depending on who consumes the data, the model must be different.

Complimentary offers are indeed a sales event, usually they produce orders whose lines are zeroed or the sum is discounted by 100%. They must be subtracted because they're a cost directly applied to the customer which is presented with something free. The point is: what value should I place there? If you have, somewhere on the originating document, the price value of the free item, the temptation to use it is irresistible. Unluckily, it's almost always wrong. These are not lost sales to be subtracted from the total amount, these are costs (or investments?) incurred to "please" the customer, help it selling your stuff better or any other promotional purpose. Likely, there was no chance to sell these items in the first place, they are not "fake" sales. So the right way to give a value to complimentary offers is at cost. That is, the cost of having those orders through the door must be compensated by actual sales.

Giveaways may look like the same as above, but they're not. They're something that you give away because the customer already purchased something else. While they still can be considered as a sort of investment to keep the customer happy, they are sales intentionally missed. That's why they can be set at price value.

This schema is not universal and depends heavily on the specific market. The key concepts of responsibility, cost and price valuations, freebies to make sales and freebies because of sales made, though, can be applied in every market.

There are some other minor events that should be taken in consideration: financial credit notes and, very rare, other financial documents. These two occur when there's a money exchange not related directly with an exchange of goods and services. They cover various events and, sometimes, their amount is proportionally high enough to be taken into consideration in the P&L. Like for returns, the reason why the note has been issued guides the model implementation.

This is it for now. See you at the next post of the Business Analysis storyline.

photo courtesy wadem

Is your company balanced?

This is a leap forward along the Business Analysis storyline, but I recently wrote about it elsewhere and I realized that this simple model can be of great help for startups. I'm going to talk about your company balances, but not in the terms you're thinking.

Have you ever thought about what can kill your business? I'm sure you did many times. Actually there are 3 easy concepts that can help you assuring the long term wealth of your company. These are 3, rather simple, checks you can do to verify the chances your business have to grow and prosper.

A business is financially balanced (in financial equilibrium) if it can pay all the bills when they are due. You should be able to pay for whatever you need to keep the company up and running (including debt) with your ordinary activities revenues. Any imbalance means that money must be harvested, from banks, investors etc, to keep the company running. Protracted imbalances may lead to a cash crisis that can actually kill the company. To be sure not to incur into financial imbalances, you have to forecast and constantly update your cash flow statement for the months to come. How to build a cash flow is beyond the scope of this article but we'll see it in the future.

A business is economically balanced (economic equilibrium) if there's a margin after every cost has been accounted for. This is not the same as before because you buy raw materials today, use them after one month, sell after two and cash in after three; this is often referred as the monetary cycle. All these activities, albeit distributed in time, are all related to a single unit of activity. In other words, disregarding the actual payments, we can make an estimate if every company activity is profitable or not, if it produces a positive margin or not. Your company as a whole, for each month of activity, must produce a positive margin. If your margin is red, cash flowing in from previous periods can temporarily compensate, but you risk to fall back on the case above.

Note that you can have a continuous row of positive margins but hit a cash crisis, because an unforeseen expense has lowered your cash or an important customer has delayed its payments. You can do everything well and still have cash problems. This is why the financial balance is more important than the economic balance and must be carefully safeguarded.

The third and final balance is the equity balance. A company has been founded upon an amount of money. In the medium/long term, the company must repay this equity in terms of dividends. The overall interest rate on the equity should also be higher than other investments to remain a viable option for the investors. This measures if the company can be profitable in the medium/long term. If every income cent is invested back in the company to make it grow or for R&D, nothing is left to pay the investors which, in turn, invested in a view to gain money. For a small, family owned, company, keeping it running is enough to make a living but, for larger companies, this may be a crucial, long term parameter.

So, when reviewing figures, start thinking in terms of the three balances, and let me know if you discover something new.

Take care

Slicing and dicing the income statement (2), Sales

I'm sure that you often had to do with sales data marts. I'm sure you designed, implemented and reported against it many times. Actually, sales and margins are often the first business areas to be covered by a BI implementation. Sometimes accounting leads the pack, but usually sales are the first subject on the list.
Implementing sales is somewhat easier than other datamarts because the granularity is almost invariably defined by the sales events. That is, each line in your fact table will likely be an order/invoice line.

Defining what dimensions to add is rather simple. We'll have customers, products or activities, sales force and many dates; plus a bunch of minor or degenerated dimensions.
Conversely, defining dimension structures is not trivial at all as their layout greatly vary with the specific business. A shop order company works differently than a consumer packaged goods company, which, in turn, is different from a consulting shop or a resort business. There's no point in describing all the possible attributes for every business, but there are few criteria which should always be met because your business users will likely need them.

The company will have various level of commercial responsibilities: there are going to be product managers who take care of a product line and area managers who deal with a group of customers or channel managers who deal with a sales channel etc. Sales must be sliceable according to this group of responsibilities to adhere to the business vision the company has adopted. Each level of responsibility must be provided with its own data.
This is not as easy as it may seem. First, not all the aggregation levels required are necessarily defined into source systems. While each customer is assigned to a salesman, maybe a dummy one, areas may not be defined as well. Sales force may be an unbalanced hierarchy, thus adding a further complexity level. This is when external, manual, data must be added to the datawarehouse.

In some cases, some very complex responsibility schemas are implemented. A single position might control sales of a product group for an area, but all the products for some customers. That is, data might have to be sliced through different dimensions to comply to sales responsibility. Often BI clients can accommodate this, but defining an appropriate, mixed, dimension can also be of help, especially in defining specific KPIs.

I'm not done with the highest rows of the P&L, there's another topic about sales structure: how to organize the sales events. But I save this for the next post.

A Video is Worth 10000 Words

It' s incredible the amount of feedback a video can have.

I've always been reluctant to resort to videos because they always imply oversimplification.
More, they are complex to build, especially if you do not have the money for a decent video editing sw, a professional speaker and you are not a native English speaker.

Anyway, I've been asked about it by a few people and, at last, I made it.

First I made the configuration video because many non techies users demanded for more clarity about. Now Viney@rd setup places a link to a specific web page that describes the configuration process in detail and hosts the video.
Then I made the query basics video that shows an appropriate, though partial, Viney@rd scenario and I placed it on the site home page.

What's amazing is not the number of visits, few tenths, but the amount of feedback I got from it.
Many Tweeps and FB friends just spent few minutes of their time actually watching it and commenting about. I actually asked for a couple of opinions or three, but many commented spontaneously. Ladies and Gentlemen, thank you for your attention.

Actually almost everybody complained about the sound (I know, but apparently I can't find a budget but decent microphone, I already tested four) and few about the overall impression. Many others liked the "authenticity" of me speaking.

At this point, I'm bought. I'm going to do a better demo asap and keep working on video tutorials and manuals.

A video is really worth 10000 words, but I said much fewer!

Four Reasons Why People Love Excel and IT Departments Hate it.

I love Excel, I think it's pretty obvious. I'm not alone, millions and millions of people around the world use it too and, more or less, love it. The vast majority of these people use it to work on, manipulate and display data of all kind.

What's strange about Excel is that the very reasons because it is loved by people are the same reasons why it is execrated by IT professionals.

At the grassroots of MS Excel diffusion is the idea of control. The user has data on grids and worksheets; these can be manipulated down to the single cell, organized at will, formatted as they prefer. Total freedom. Maybe only a fraction of this freedom is actually used, but having it is reassuring.
The IT has mixed feelings about total control. It may be an escape hatch when there's no time or possibility to do the job but, on the contrary, who knows what users can do with all that freedom? How can be assured that those data are "right?".

The other pillar of Excel success is ownership. The user owns her data, once they are on the file. She can do everything, and she's the sole responsible for that. No one must be contacted before rounding figures or cutting corners.
As before, the IT professionals are concerned of users making "unapproved changes" with no previous notice, or creating "unofficial versions" and spreading them all over the company.

The third element is collaboration. I'm not talking of Groove or other similar tool, I'm talking the ability to pack all your work in a file and e-mail it. You can broadcast your work that easily, with a system that everybody is familiar with and, from the user perspective, is extremely manageable.
The IT, of course, has concerns about data version proliferation, security threats or even attachments size.

The fourth element is the "Swiss army knife" attitude. Excel extreme freedom sometimes (or often) gives rise to complex environments to make the job of a specific application. These may be complex and prone to break but, at last, they are tailored exactly on user requirements and are user controlled.
These creations are sometimes kept secret, because the IT would merrily use a flamethrower on them.

So, there's a clash of visions around Excel that, ultimately, have roots in different visions. For business users, the whole damn computer thing is a matter of tools to do the job and Excel, for the knowledge worker, is a powerful tool. For the IT department, everything stays inside a process, one of the many which live inside a company.

Is there an equilibrium somewhere between these two contrasting visions? Yes of course! Or, maybe, not. Excel is here to stay; even the Gartner Group, recently, gave up to that, in a certain way. Currently software connects to Excel to export data and, sometimes, to import from it. This approach draws a borderline between the orderly and well organized world of business applications and the wild territories of personal computing. Usually the IT stays entrenched on the border and reluctantly raids the unknown territory outside.
Users happily work with their Excel files but, sometimes, feel that they need something more automated and more organized. It is on this requirement that a cautious encounter may take place. What we need is a new generation of applications which interact with Excel and tackle or ease the issues mentioned above. With Viney@rd I'm giving a contribution in this direction, there are some others who are working along the same lines. The future appears to be interesting.

What do you think about?

Photo courtesy of recycledstardust

Slicing and dicing the income statement (1), organization

This is the fourth post dedicated to business analysis for BI consultants. The series start here.

In the previous post we saw that the Income Statement may have multiple forms (and even multiple names, because it is sometimes called Profit & Loss statement, P&L). An Income statement suited for management purposes shows costs related to the company operations, usually splitting them among direct and indirect costs. So far, we have designed a companywide Income Statement while management often needs data at a lower level. So you'll be asked to build a sliceable income statement. I'm sure many are familiar with the idea; from a technical perspective is "just another dimension", but what are the business perspectives tied to those dimensions?

Considering the company as a whole, two kind of dimensions come into play: business line and business unit. The business line mirrors a specific internal organization upon different business. Consider a company which manufactures bulldozers. It runs two entirely different businesses: the dozers business (with salesmen, expositions, advertising, large customers and small owners etc.) and the spare parts business (with country repairers, inventory turns, urgent calls etc.). No use to say, these are very different organizations with different people with different operations and mindset. Nonetheless, the two lines use some common services, like accounting, human resources etc.

The business unit is the radicalization of this concept or the result of an aggregation of more than one company. Business units are practically companies of their own. In fact, a business unit is often defined such by having its own P&L statement whose format can be different from other business units. What's important to understand, in our perspective, is that two business lines or two business units may greatly differ for the others dimensions. Just think to the bulldozers example, they indeed have a different sales force and an entire machine is likely classified rather differently than a spare part.

There's a third concept that many "Db Wizards" do not even suspect it's there. We talk about business and sales, hearth movers sales, spare parts sales an old machinery sold as second-hand toolsets. What distinguish the first two items from the latter? They are all sales, aren't they? Yes, they're sales but getting rid of old machinery is not part of the company everyday operations. A managerial P&L tracks only the ordinary activity, the company income statement tracks everything. One of the requisites for the company to thrive is to have a positive bottom line for the ordinary activity.

So far we haven't reached even the first line of the P&L statement, usually sales. In the next post we'll discuss how to slice and dice sales.

Why you should get an MBA

I break the Business analysis for BI consultants series for a post that has been inspired by a discussion on the well known Joel on Software forum. This post is a slightly developed version of my reply to the thread titled: Why you should unlearn your MBA

The thread starts recalling a David Heineimeier Hansson speech at Stanford University. Being the father of Ruby on Rails and 37 Signals, DHH makes a point in telling that what you learn from a business school is no use in creating a startup, at least a technology startup. Actually, it may be harmful; so it is better to unlearn it.

Along these lines, Guy Kawasaki, the famous venture capitalist and internet marketer, gives a speech on how only the real world experience can give the ability to manage; so attending a management school is of no utility .

I know that DHH and GK are well respected and I would normally listen carefully to their opinions. Those two guys really built something and they deserve a lot of respect when talking about technology business. Actually I see their points and I admit that there's an internal logic.

BUT,

in my opinion, this thing that getting an MBA is useless is a bad mistake. Better, all that chatter about you not using what you learn or that you can't learn things on books is plainly

BULLSHIT

I explain why.

1) Culture is not a tool, culture sharpens you perception of the world, culture creates a better you. So do not ask if a notion can be applied to your job or not, just learn it for the time you'll use it to understand a deeper layer of truth.

2) The amount of logical and abstract thinking required to fully grasp some of the ideas you'll learn at school is so high to improve your applied thought. I'm an aerospace engineer, I'm happy of having studied the turbulent boundary layer; it does not have anything to do with my current job, but it helps me in being better at it. I do not regret a minute of the days and nights spent on strange mathematics because it gave me the ability to extract the meaningful data from a pile of rubble.

3) It's not true that an MBA is useless in a small IT consulting firm, because the best way to relate with business line managers is talking the same language. The raw material of their decisions is often something explained at school. I talked extensively about it here.

4) It's not true that an MBA is useless in a small software shop either, because, if you target the B2B market, only the consultancy large vision gives you the exact feeling of how a software should be made. I started a company doing a piece of software that I wish I had in my projects.

5) If you are successful, your company will not stay small forever. If you do not necessarily need an MBA to run a small shop, that kind of knowledge becomes more and more important as your company grows. Financials, control or investments are not subjects where common sense provides a guidance good enough.

I could go on telling war stories about the damage ignorant entrepreneurs cause to companies, but I think this post is already long enough. So, if you chose not to get an MBA, make sure you'll learn the same things another way, but never stop learning about business!

A nice P&L statement

Here you can find a nice example of P&L statement for a software company, Qlik Tech, which is going IPO.

http://www.kellblog.com/wp-content/uploads/2010/04/qliktech-financials4.png

What's interesting here is the revenue vs costs breakdown. Each revenue item is a business line and each business line shows its own costs. These are not properly business units because there are a lot of common costs in the lower segment. The inner structure of those costs�deal are poeple costs, but their�internal breakdown may be a lot different. �

This pic comes from Dave Kellog's blog which draws an excellent picture of Qlik market and financial position.

How Managers Use the Income Statement

In the previous post we saw the classic, but I'd rather say the "accountant's" form of the Income Statement. Income Statements do not come always in this forms, there are many other forms, each one focuses on different company facts.

For example, in a manufacturing company it might look like this:

A manufacturing company focuses on issues like the manufacturing costs or the finished goods warehouse (it has impact on taxation and crystallizes money into something that sits on a shelf, but this is an entirely different matter).
Once more, this form gives an informative view on the company as a whole but does not tell a lot about what a manager can directly influence, that is, the operations.

Actually one of the first things that often go implemented in a BI project is a data-mart that gives you back something like this.

This statement appears to mix various things but, at a closer look, you'll find some interesting points.

First, costs are split between direct, that is costs which can be associated directly to the product sold and Indirect, which can't. This method is named "Direct Costing", we'll learn more later about that.

Second, note that each row is closely related to a specific function. In J&B computers there's someone who's in charge for selling HW, someone who sells consulting services, someone who checks that travel expenses do not balloon unexpectedly and so on.
This point is very important, the managerial income statement is split according to a principle of accountability. There should be someone who's in charge of the figures in a row. It's not the only principle, but the most relevant to understand why a specific cost structure is chosen.

Third, it ends with Margin and not with Income. Margin is what remains after all the costs and expenditures have gone into effect to produce the sales. Is it the same as the Income? Absolutely not, they're "cousins" but they are calculated in different ways. The margin tells you that the major ongoing operations produced more money than the amount burnt to produce the sales.
If you have a global negative margin, sooner or later you'll have to borrow money. If the margin keeps being negative, you're toast.

Be aware that the rows may vary, depending on what you need to keep an eye on. A manufacturing company will likely have a detailed production cost breakdown, even with some cost allocations. Others, which heavily use coupons and special prices may have those as a cost. Designing a managerial income statement suited to the company is one of the tasks those management consultants paid twice than you are appointed to do but that can be done just asking the right questions to management .

So now you see a report, and I know many among you already know how to implement it by their favorite BI tool. Before closing the post I just want to point out that this report sucks. It sucks because no number has a meaning if it is not compared to another number. It may be the budget or the previous year same period, but your report need to look like this.

Here 2009 and 2008 are side by side. For each year, each row shows the percentage respect to Total Sales. We already said that this is a scalar report, so a manager reads it to learn how many point are "eroded" by a particular row. This makes comparison non dimensional thus showing efficiency gains or losses. The comparison between the two years is made on the percentage points gained or lost.
For example the warehouse and logistics which handle the HW have slightly lost efficiency, having lost .2 point in 2009.
Administrative expenses have been well polished, because they are down a point out of 5 and a half in 2009.
If you have to choose an area to work on to improve margins, just work in obtaining better discounts from HW vendors because HW purchases make 40 points alone and they're up 3 point from 2008. This is the kind of analysis a manager does.
Now watch consulting labor, it is down 2.61 points, which sounds good, but consulting is down by 5.6 points, so you actually lost 3 points of efficiency! All of this, while total consulting value grew!
Anyway, everything summed up, your margin is up 3.53 points and the world is beautiful.

I hope I have given an idea of what managers do with your income statement. In the next post we'll see how to implement it and how to add dimensions to the analysis.

Stay tuned and, please, tweet this post for your friends and coworkers

If you want to be part of the Viney@rd 1.2 private beta program go here

If you want to try version 1.1 go here

If you want to learn something more about Viney@rd, please visit http://www.straysoft.com

Income: What Companies Live and Die for, or the Income Statement.

One of the most common user request is "How much am I earning?" This question is second only to "How much am I selling?". Often projects start just to have an efficient reply to these two questions.
Notice that the two questions are only superficially similar but an ocean lays in the middle . Let's start.

I'm sure you've been asked many times to create sales reports. They're important, but they describe only a fraction of reality. A company general manager is much more interested in different figures, that is the money left after paying what must be paid and collecting the credit.
As you probably know, the broad definition of this figure is:

Sales - Cost = Income

Reports usually do not have one row (albeit some widgets from famous BI players provide what's exactly one row of data), but are drawn in the form of the income statement. A traditional income statement, for a commercial company, may look like this:

Probably you've already seen this or something similar around or in a book. This is the classical income statement that the company CFO prepares at the end of the year. For example, find here the Microsoft Income Statement. You will likely not be asked to create this because the accounting systems already produces it and it is carefully adjusted by hand on Excel before being validated by the board. For a "no frills" detailed description you can go here.

Before moving on, nonetheless, there are two key aspects I want you to notice.

This is a scalar report, that is, starts from a value, subtracts other values, has intermediate totals and ends with a much lower value. The business meaning of this is that the Revenues are "consumed" by costs and expenses and what's left is the "money" (it is not exactly true but I skip by now) available to be pocketed or reinvested.

This statement refers to a whole year, that is, summarizes Facts which took place in 2009 or, somehow, pertain to 2009. The word "pertain", hides the accounting accrual concept. I've read very long chapters about it but the point you need to get is very simple: costs pertain to the period when the associate resource has been consumed.
Simpler: the cost of goods sold is accounted in the same period as the sale.

An income statement like this depicts company business in little detail. It's ok, more or less, for Wall Street financial analysts and it says that, yes, somehow, in 2009 you should end up with more money than in 2008; but does not tell so much about how actually running the business. In the next chapter we'll see what a chameleon the Income Statement is.

Stay tuned and, please, tweet this post for your friends and coworkers

If you want to be part of the Viney@rd 1.2 private beta program go here

If you want to try version 1.1 go here

If you want to learn something more about Viney@rd, please visit http://www.straysoft.com

Putting the Business back in BI

We've been talking too much about technologies, myself included.

There's a focus in BI community on the emerging trends of cloud computing, in memory processing, columnar stores etc. but the link with business requirements is weak.

In my experience of a life spent in small and midsize BI projects I've never found a business manager or an entrepreneur really caring about the technicalities of a solution. They see it as a black box where to pour an amount of money in to get back some capabilities, possibly to be translated into ROI.
The general mood is "do what you need with that box, I need the result". Usually it is hard to explain to a business user why she can't see the data formatted like he wants, if all those data are available; the usual reaction is "why you can't do something to get it?".

So, during the years, I've found myself developing a business management culture and getting more and more business analysis skills. Today, with few exceptions, my project documents are always business talks, focusing on business issues and describing systems to cope with it or to keep an eye on. The technical part is for the CIO who is often happy to let the consultant do all the dirty job of defining requirements and targets.
Someone says that the eye candy of modern platforms can make the difference and win a contract; I despise this approach, it's for the professional liar. I prefer showing the business people that I actually understand them and I can cater actual solutions. I seldom give demos, I prefer to sit and listen. I seldom write 300 pages of specs, I write 10 pages of business analysis and they're usually enough.

I do not have the experience of some of the people hanging around my blog, but I found this approach to be highly effective.
So I think that I should help BI consultants to better understand their customers. Understanding your customers let you build a better link between the tech requirement and the business requirement, let you serve them better and let you ask for higher fares.
Do not underestimate the amount of business knowledge required to set up a coherent management control system; for you, as a technician, some aspects are just reduced to "one more field" or "one more table", but they have deep business implications.

This is the introductory post of a series dedicated to business analysis for BI consultants.

The most correct choice is to describe the business environment in the academic order, like a university course in business analysis. Being practical, I'm going to start covering the topics in an order in which they are implemented or are important to know.

I assume that the reader has already an average knowledge of relational and olap databases and is familiar with the BI terminology and with the basic datawarehouse concepts.

The work is not completely unbiased and it will reflect my personal career. Examples will cover many economy sectors but not banking, insurance and investments in general because I have only a marginal experience on those. A different type of bias also comes from my experience on the Italian market, albeit often with internationalized companies.

Let me know if there's a topic you'd like to see covered.

I hope you'll enjoy the posts so I can keep doing the very clever professor who puffs his pipe and sneaks Viney@rd in!

In the next post, we'll start with what companies live and die for, income.

See you soon and, please, tweet this post for your friends and coworkers

If you want to be a part of the Viney@rd 1.2 private beta program visit http://www.straysoft.com/beta12.html

If you want to try version 1.1 go here

If you want to learn something more about Viney@rd, please visit http://www.straysoft.com.

Why things are like they are

Last week I had a discussion on UK Business Forums about Viney@rd. You can find it here .

Garyk gave me the opportunity to explain why some things are like they are in Viney@rd. This is an interesting discussion and I post it here, slightly edited

Garyk: "�so this actually builds the cubes in SSAS in the background? �"

Stray__Cat: "No, it does not build cubes in background, it works with the relational engine. This is done purposely for two reasons:
1) to support the free express editions
2) because the relational engine is known by far more people, consultants and end-users as well.

Garyk "OK so how will it do aggregates on dimensions if I say have 500,000 transactions spread over two years and I need periodic and yearly totals by several dimensions? Its alot of number crunching which is why the big BI solutions use cubes populated overnight or is it aimed purely at the SME market?"

Stray__Cat: This question requires an articulated reply.

Yes, you are right when you state that there's a step somewhere , depending on HW, data volume ,query complexity etc. which gets too high to jump over by the relational engine. And, Yes, Viney@rd is aimed to SMBs or to niche sectors in larger enterprises.

So, I do not want to compete with the many stellar "data cooking" solutions that are out there, I can't and they already did a fantastic job. At the end of the day, I do not run exactly a one man show but a close approximation of that.

I just want to address a couple of issues which rise in workgroups who make an heavy Excel usage.

First, files proliferate. They become confused and their relations become soon intricate. External references can't be reached because a file was moved, the format you got is not exactly the expected one etc.

Viney@rd enables you, without leaving Excel, to save your data in a central repository and work from there. Nothing prevents to build something custom that feeds data automatically (the db structure is very easy to understand and it is open) but even the average user, with Viney@rd, can adjust data as she needs they are. Often Excel sheets are used to collect data, and consolidating them requires an ad-hoc app; with Viney@rd this job is greatly eased, not to say already done. If you have to classify dimension values differently, to introduce a slicing level which does not exist on your system, simply add an attribute to the dimension and compile it on Excel. In other words, arrange your data how you need and make it yourself.

Second, your report layout, is not exactly the layout you need. Too often high level reporting is done by Excel because it can address a single cell while the usual tabular report layout can't. With Viney@rd you can tie a cell to a certain figure coming out from the repository. You can limit the numbers of rows returned, sync two queries' outputs or have the result of a calculation to be part of the query where condition. You have control on query results down to the single worksheet cell. So Viney@rd can refresh data even in the most complex layout without disrupting it.

If you need to analyze data, Viney@rd provides data connections to the models to place a pivot on top of them or load data into the new PowerPivot AddOn. They work far better than I could ever do, and you have already paid for those capabilities, so you must not buy them twice! Should the queries become too slow, by Viney@rd data transformations you can add aggregated models and query from them. The automation of the process is still weak, but is one of the scheduled improvements in the near future.

If you want to learn something more about please visit http://www.straysoft.com. If you want to be part of the Viney@rd 1.2 private beta contact me at vineyard@straysoft.com If you want to try version 1.1 go here http://www.straysoft.com/Try.html

Thank you for your questions, Garyk; Take care.

My Business Intelligence links

As I promised quite a long time ago, I make a list of the Business Intelligence sites I regularly read.
I'm not always on the topics being discussed, but I learnt a lot for sure from them. I'll never thank enough those people who share their knowledge online. This is by no mean a complete or a popularity based list, and is grounded only in my personal taste.

BeyeNETWORK is the battleship of BI sites. Many of the most famous BI stars write here or their blog is hosted here or is aggregated here. Bill Inmon, Jill Dyche, Claudia Imhof and many others. Articles cover BI at 360 from high level philosophy discussion to low level implementation, from market news to change management in BI projects.
Being a rather cynical guy, the one defect I find that it is a little biased toward large, enterprise-size analysis.

TDWI is just an inch back from the previous site. It is not a totally informational site as it provides education and consulting, and it is better enjoyed with membership.

If I could, I'd marry this girl: Inside The Biz by Jill Dyche is one of the most insightful sites I have ever came across. She deals with a variety of topics, not all BI related but each and every article is worth reading.

For those who can read French, Le Grand BI is an immense enjoyment. Their prominent feature is a cynical and disillusioned approach to all the hype and buzzwords that sometimes blind us. I often refer to them to have a second opinion.

As I work for a large part in Microsoft ecosystem I use and like the Microsoft Resource Center . What's outstanding here is that you are taken by hand and brought to the highest level seamlessly by a host of written and filmed footage. Donald Farmer articles are a must read for all those who work with MS products.

Last but not least Oz-Analytics provides an always interesting point of view. Starting from other articles or real life experience, I've never read a trivial point of view from this guy.

As you can easily notice I leave out all those bloggers who deal mainly with the technical side of the job. Actually there are a lot of them worth reading, some fundamentals. This a good topic for another post, I'd say.

Enjoy.

In defense of the Natural Keys

Whenever I use the term "Natural key" in a tweet or on Facebook, someone always DM me to remind me how natural keys are worthless. I should use the brave and muscular surrogate keys for each and every task. Maybe is a frame of mind, but whenever there are strong technical debates I feel naturally inclined to the minority.
In this case my point is that natural keys are perfectly adequate for many Business Intelligence tasks, and should be replaced by surrogate keys only when necessary.

If you are still with me, let's go.

A bar counter definition of natural key is: "the code which uniquely identifies your record". Every transactional system assigns codes to its entities, customers, salesmen, products etc. The policy used to assign this code, is up to the transactional system and I could not care less.

Given that natural keys can a) change over time b) have a business meaning that can change over time as well c) not be the ideal field format for a join, often a surrogate key is introduced. A surrogate key is a key, usually numeric, unrelated with the natural, that uniquely identifies the record. That is, it does exactly the job already done by the natural key. (if you want a less mundane description of this subject you can find a good article here).

This consideration alone should drive to the conclusion that the widespread use of surrogate keys in a datawarehouse is often overkill.

I made literally tenths of systems with no surrogate keys which worked fine for years. Often we think to DWs as multi terabytes systems which integrate data from hundredths of systems, but these are only a fraction of the total. There's also a midsized business market which needs the bulk of the abilities provided by a datawarehouse with far less data and far less systems to be integrated. In these cases, a simple approach, based on natural keys, may be perfectly suited.

Building surrogate keys, is often a rather complex task, which slows development, hinders the long and delicate subsequent trimming work and slows loadings. More subtly, they're an entirely technical subject, hard to be understood by non technical project sponsors thus hard to justify on a project schedule.

Surrogate Keys should be introduced when they are necessary and there are, indeed, cases where surrogate keys are highly recommended.

In those multi terabytes DWs, where the smallest fact table has half billion records, performance becomes such an issue that the use of small numeric keys is practically mandatory. If many systems are integrated together in the DW, chances are that codes overlap, so an unrelated key is a good solution to that. Surrogate keys also shield DWs from changes of the naturals, whose propagation in a very large DW is an awful job. Also Surrogate Keys are a good artifact to factor in slowly changing dimensions. Etc. Etc. etc.

So, the next time I tweet the term "Natural Key", please do not flood my account with "Surrogate Keys"!

Vineyard Business Intelligence

THIS BLOG IS CLOSED

How to make good use of the paper your accountant gives to you (part 2)

Shadow Systems

How to make good use of the paper your accountant gives to you (part 1)

Back to Basics II: what�s the difference among dimensions, measures and an attributes?

Measures, Metrics and KPIs ?

The Magazination

Pads, slates, tablets and Business Intelligence

What is Business Intelligence

What�s new in version 1.2.1

Inverted Occam Razor Principle

Sneak Peek at the new web site

Slicing and dicing the income statement (4), costs

A Bucolic Parenthesis

Excel School

The Income Statement (3) the sales events.

Is your company balanced?

Slicing and dicing the income statement (2), Sales

A Video is Worth 10000 Words

Four Reasons Why People Love Excel and IT Departments Hate it.

Slicing and dicing the income statement (1), organization

Why you should get an MBA

A nice P&L statement

Recommended Readings

How Managers Use the Income Statement

Income: What Companies Live and Die for, or the Income Statement.

Putting the Business back in BI

Why things are like they are

My Business Intelligence links

In defense of the Natural Keys