Ratios and Formulas in Customer Financial Analysis

Ratios and Formulas in Customer Financial Analysis
Financial statement analysis is a judgmental process. One of the primary objectives is identification of major changes in trends, and relationships and the investigation of the reasons underlying those changes. The judgment process can be improved by experience and the use of analytical tools. Probably the most widely used financial analysis technique is ratio analysis, the analysis of relationships between two or more line items on the financial statement. Financial ratios are usually expressed in percentage or times. Generally, financial ratios are calculated for the purpose of evaluating aspects of a company's operations and fall into the following categories:

• liquidity ratios measure a firm's ability to meet its current obligations.
• profitability ratios measure management's ability to control expenses and to earn a return on the resources committed to the business.
• leverage ratios measure the degree of protection of suppliers of long-term funds and can also aid in judging a firm's ability to raise additional debt and its capacity to pay its liabilities on time.
• efficiency, activity or turnover ratios provide information about management's ability to control expenses and to earn a return on the resources committed to the business.

A ratio can be computed from any pair of numbers. Given the large quantity of variables included in financial statements, a very long list of meaningful ratios can be derived. A standard list of ratios or standard computation of them does not exist. The following ratio presentation includes ratios that are most often used when evaluating the credit worthiness of a customer. Ratio analysis becomes a very personal or company driven procedure. Analysts are drawn to and use the ones they are comfortable with and understand.
Liquidity Ratios
Working Capital
Working capital compares current assets to current liabilities, and serves as the liquid reserve available to satisfy contingencies and uncertainties. A high working capital balance is mandated if the entity is unable to borrow on short notice. The ratio indicates the short-term solvency of a business and in determining if a firm can pay its current liabilities when due.

Formula
Current Assets
- Current LiabilitiesAcid Test or Quick Ratio
A measurement of the liquidity position of the business. The quick ratio compares the cash plus cash equivalents and accounts receivable to the current liabilities. The primary difference between the current ratio and the quick ratio is the quick ratio does not include inventory and prepaid expenses in the calculation. Consequently, a business's quick ratio will be lower than its current ratio. It is a stringent test of liquidity.

Formula
Cash + Marketable Securities + Accounts Receivable
Current LiabilitiesCurrent Ratio
Provides an indication of the liquidity of the business by comparing the amount of current assets to current liabilities. A business's current assets generally consist of cash, marketable securities, accounts receivable, and inventories. Current liabilities include accounts payable, current maturities of long-term debt, accrued income taxes, and other accrued expenses that are due within one year. In general, businesses prefer to have at least one dollar of current assets for every dollar of current liabilities. However, the normal current ratio fluctuates from industry to industry. A current ratio significantly higher than the industry average could indicate the existence of redundant assets. Conversely, a current ratio significantly lower than the industry average could indicate a lack of liquidity.

Formula
Current Assets
Current LiabilitiesCash Ratio
Indicates a conservative view of liquidity such as when a company has pledged its receivables and its inventory, or the analyst suspects severe liquidity problems with inventory and receivables.

Formula
Cash Equivalents + Marketable Securities
Current LiabilitiesProfitability Ratios
Net Profit Margin (Return on Sales)
A measure of net income dollars generated by each dollar of sales.

Formula
Net Income *
Net Sales* Refinements to the net income figure can make it more accurate than this ratio computation. They could include removal of equity earnings from investments, "other income" and "other expense" items as well as minority share of earnings and nonrecuring items.
Return on Assets
Measures the company's ability to utilize its assets to create profits.

Formula
Net Income *
(Beginning + Ending Total Assets) / 2Operating Income Margin
A measure of the operating income generated by each dollar of sales.

Formula
Operating Income
Net SalesReturn on Investment
Measures the income earned on the invested capital.

Formula
Net Income *
Long-term Liabilities + EquityReturn on Equity
Measures the income earned on the shareholder's investment in the business.

Formula
Net Income *
EquityDu Pont Return on Assets
A combination of financial ratios in a series to evaluate investment return. The benefit of the method is that it provides an understanding of how the company generates its return.

Formula

Net Income * Sales

x

Sales Assets

x

Assets Equity

Gross Profit Margin
Indicates the relationship between net sales revenue and the cost of goods sold. This ratio should be compared with industry data as it may indicate insufficient volume and excessive purchasing or labor costs.

Formula
Gross Profit
Net SalesFinancial Leverage Ratio
Total Debts to Assets
Provides information about the company's ability to absorb asset reductions arising from losses without jeopardizing the interest of creditors.

Formula
Total Liabilities
Total AssetsCapitalization Ratio
Indicates long-term debt usage.

Formula
Long-Term Debt
Long-Term Debt + Owners' EquityDebt to Equity
Indicates how well creditors are protected in case of the company's insolvency.

Formula
Total Debt
Total EquityInterest Coverage Ratio (Times Interest Earned)
Indicates a company's capacity to meet interest payments. Uses EBIT (Earnings Before Interest and Taxes)

Formula
EBIT
Interest ExpenseLong-term Debt to Net Working Capital
Provides insight into the ability to pay long term debt from current assets after paying current liabilities.

Formula
Long-term Debt
Current Assets - Current LiabilitiesEfficiency Ratios
Cash Turnover
Measures how effective a company is utilizing its cash.

Formula
Net Sales
CashSales to Working Capital (Net Working Capital Turnover)
Indicates the turnover in working capital per year. A low ratio indicates inefficiency, while a high level implies that the company's working capital is working too hard.

Formula
Net Sales
Average Working CapitalTotal Asset Turnover
Measures the activity of the assets and the ability of the business to generate sales through the use of the assets.

Formula
Net Sales
Average Total AssetsFixed Asset Turnover
Measures the capacity utilization and the quality of fixed assets.

Formula
Net Sales
Net Fixed AssetsDays' Sales in Receivables
Indicates the average time in days, that receivables are outstanding (DSO). It helps determine if a change in receivables is due to a change in sales, or to another factor such as a change in selling terms. An analyst might compare the days' sales in receivables with the company's credit terms as an indication of how efficiently the company manages its receivables.

Formula
Gross Receivables
Annual Net Sales / 365Accounts Receivable Turnover
Indicates the liquidity of the company's receivables.

Formula
Net Sales
Average Gross ReceivablesAccounts Receivable Turnover in Days
Indicates the liquidity of the company's receivables in days.

Formula
Average Gross Receivables
Annual Net Sales / 365Days' Sales in Inventory
Indicates the length of time that it will take to use up the inventory through sales.

Formula
Ending Inventory
Cost of Goods Sold / 365Inventory Turnover
Indicates the liquidity of the inventory.

Formula
Cost of Goods Sold
Average InventoryInventory Turnover in Days
Indicates the liquidity of the inventory in days.

Formula
Average Inventory
Cost of Goods Sold / 365Operating Cycle
Indicates the time between the acquisition of inventory and the realization of cash from sales of inventory. For most companies the operating cycle is less than one year, but in some industries it is longer.

Formula
Accounts Receivable Turnover in Days
+ Inventory Turnover in DayDays' Payables Outstanding
Indicates how the firm handles obligations of its suppliers.

Formula
Ending Accounts Payable
Purchases / 365Payables Turnover
Indicates the liquidity of the firm's payables.

Formula
Purchases
Average Accounts PayablePayables Turnover in Days
Indicates the liquidity of the firm's payables in days.

Formula
Average Accounts Payable
Purchases / 365Additional Ratios
Altman Z-Score
The Z-score model is a quantitative model developed in 1968 by Edward Altman to predict bankruptcy (financial distress) of a business, using a blend of the traditional financial ratios and a statistical method known as multiple discriminant analysis.
The Z-score is known to be about 90% accurate in forecasting business failure one year into the future and about 80% accurate in forecasting it two years into the future.

Formula

Z =

1.2 +1.4 +0.6 +0.999 +3.3

x x x x x

(Working Capital / Total Assets) (Retained Earnings / Total Assets) (Market Value of Equity / Book Value of Debt) (Sales / Total Assets) (EBIT / Total Assets)

---

Z-score	Probability of Failure
less than 1.8 greater than 1.81 but less than 2.99 greater than 3.0	Very High Not Sure Unlikely

Bad-Debt to Accounts Receivable Ratio
Bad-debt to Accounts Receivable ratio measures expected uncollectibility on credit sales. An increase in bad debts is a negative sign, since it indicates greater realization risk in accounts receivable and possible future write-offs.

Formula
Bad Debts
Accounts ReceivableBad-Debt to Sales Ratio
Bad-debt ratios measure expected uncollectibility on credit sales. An increase in bad debts is a negative sign, since it indicates greater realization risk in accounts receivable and possible future write-offs.

Formula
Bad Debts
SalesBook Value per Common Share
Book value per common share is the net assets available to common stockholders divided by the shares outstanding, where net assets represent stockholders' equity less preferred stock. Book value per share tells what each share is worth per the books based on historical cost.

Formula
(Total Stockholders' Equity - Liquidation Value of Preferred Stocks - Preferred Dividends in Arrears)
Common Shares OutstandingCommon Size Analysis
In vertical analysis of financial statements, an item is used as a base value and all other accounts in the financial statement are compared to this base value.
On the balance sheet, total assets equal 100% and each asset is stated as a percentage of total assets. Similarly, total liabilities and stockholder's equity are assigned 100%, with a given liability or equity account stated as a percentage of total liabilities and stockholder's equity.
On the income statement, 100% is assigned to net sales, with all revenue and expense accounts then related to it.
Cost of Credit
The cost of credit is the cost of not taking credit terms extended for a business transaction. Credit terms usually express the amount of the cash discount, the date of its expiration, and the due date. A typical credit term is 2 / 10, net / 30. If payment is made within 10 days, a 2 percent cash discount is allowed: otherwise, the entire amount is due in 30 days. The cost of not taking the cash discount can be substantial.

Formula

% Discount 100 - % Discount

x

360 Credit Period - Discount Period

Example
On a $1,000 invoice with terms of 2 /10 net 30, the customer can either pay at the end of the 10 day discount period or wait for the full 30 days and pay the full amount. By waiting the full 30 days, the customer effectively borrows the discounted amount for 20 days.

$1,000 x (1 - .02) = $980

This gives the amount paid in interest as:

$1,000 - 980 = $20

This information can be used to compute the credit cost of borrowing this money.

% Discount 100 - % Discount	x		360 Credit Period - Discount Period
=    2       98	x	360 20	=    .3673

As this example illustrates, the annual percentage cost of offering a 2/10, net/30 trade discount is almost 37%.
Current-Liability Ratios
Current-liability ratios indicate the degree to which current debt payments will be required within the year. Understanding a company's liability is critical, since if it is unable to meet current debt, a liquidity crisis looms. The following ratios are compared to industry norms.

Formulas

Current to Non-current	=	Current Liabilities Non-current Liabilities
Current to Total	=	Current Liabilities Total Liabilities

Rule of 72
A rule of thumb method used to calculate the number of years it takes to double an investment.

Formula
72
Rate of ReturnExample
Paul bought securities yielding an annual return of 9.25%. This investment will double in less than eight years because,

72 9.25

= 7.78 years

Tableau offers several capabilities for connections to Hadoop Hive that are not available for other data sources.

XML processing

While many traditional databases provide XML support, the XML content must first be loaded into the database. Because Hive tables can be linked to a collection of XML files or document fragments stored in the Hadoop file system, Hadoop is much more flexible in analyzing XML content.
Tableau provides a number of functions for processing XML data, which allows you to extract content, perform analysis or computation, and filter the XML data. These functions leverage XPath, a web standard used by Hive and described in more detail in the Hive XPath documentation: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+XPathUDF.

Web and text processing

In addition to XPath operators, the Hive query language offers several ways to work with common web and text data. Tableau exposes the following functions that you can use in calculated fields:

• JSON Objects: GET_JSON_OBJECT retrieves data elements from strings containing JSON objects.
• URLs: Tableau offers PARSE_URL to extract the components of a URL such as the protocol type or the host name. Additionally, PARSE_URL_QUERY can retrieve the value associated with a given query key in a key/value parameter list.
• Text Data: The regular expression find and replace functions in Hive are available in Tableau for complex text processing.

For more information on these functions, refer to the Hive documentation: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF.

On-the-fly ETL

Custom SQL gives you the flexibility of using arbitrary queries for your connection, which allows complex join conditions, pre-filtering, pre-aggregation and more. Traditional databases rely heavily on optimizers, but they can struggle with complex Custom SQL and lead to unexpected performance degradation as you build views. The batch-oriented nature of Hadoop allows it to handle layers of analytical queries on top of complex Custom SQL with only incremental increases to query time.
Because Custom SQL is a natural fit for the complex layers of data transformations seen in ETL, a Tableau connection to Hive based on Custom SQL is essentially on-the-fly ETL. Refer to the Hadoop and Tableau Demo on the Tableau blog to see how you can use Custom SQL to unpivot nested XML data directly in the Custom SQL connection, yielding views built from the unpivoted data.

Initial SQL

Tableau supports initial SQL for Hadoop Hive connections, which allows you to define a collection of SQL statements to perform immediately after the connection is established. For example, you can set Hive and Hadoop configuration variables for a given connection from Tableau to tune performance characteristics. Refer to the Designing for Performance article for more information. You can also register custom UDFs as scripts, JAR files, etc., that reside on the Hadoop cluster. Registering these allows you, other developers, and analysts to collaborate on developing custom data processing logic and quickly incorporating that into Tableau views.
Because initial SQL supports arbitrary Hive query statements, you can use Hive to accomplish a variety of interesting tasks when connecting to Tableau.

Custom analysis with UDFs and Map/Reduce

Although Hive offers additional UDFs that Tableau does not yet support as functions for you to use in calculated fields, Tableau does offer "Pass Through" functions for using UDFs, UDAFs (for aggregation) and arbitrary SQL expressions in the SELECT list. For example, to determine the co-variance between two fields 'f1' and 'f2', the following Tableau calculated field takes advantage of a UDAF in Hive: RAWSQLAGG_REAL("covar_pop(%1, %2)", [f1], [f2])
Similarly, Tableau allows you to take advantage of custom UDFs and UDAFs built by the Hadoop community or by your own development team. Often these are built as JAR files that Hadoop can easily copy across the cluster to support distributed computation. To take advantage of JAR files or scripts, inform Hive of the location of these files and Hive will take care of the rest.

Note: You can also do this with Initial SQLwith one or more SQL statements separated by semicolons:

add JAR /usr/lib/hive/lib/hive-contrib-0.7.1-cdh3u1.jar;
add FILE /mnt/hive_backlink_mapper.py;

For more information, refer to the Hive language manual section on CLI: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Cli#LanguageManualCli-HiveResources.

Hive supports explicit control over how to perform the Map/Reduce operations. While Tableau allows you to perform sophisticated analysis without having to learn the Hive query language, as an advanced Hive and Hadoop user, you can take full advantage of this knowledge in Tableau. Using Custom SQL, you can define arbitrary Hive query expressions, including the MAP, REDUCE, and TRANSFORM operators described in the Hive language manual. As with custom UDFs, using custom transform scripts may require you to register the location of those scripts using Initial SQL.

Refer to the following blog for an interesting example of using custom scripts and explicit Map/Reduce transforms: http://www.cloudera.com/blog/2009/09/grouping-related-trends-with-hadoop.

Tableau JavaScript API Benefits/Uses

Tableau JavaScript API helps us to create custom views as per the requirements of different users

Once Tableau developer published worksheets/views/dashboards/story/workbooks onto the Server. We can generate javascript API in the sever by clicking on a specific view (share button/Link of that specific view). And that view can be customized using javascript functions as per the requirements.

Tableau providing us specific functions to handle/manage the views

The following are the different events that we can handle using tableau javascript APIs and Live tutorials 

Tutorials

http://onlinehelp.tableau.com/samples/en-us/js_api/tutorial.htm

Videos to understand Tableau JavaScript API

http://www.tableau.com/learn/tutorials/on-demand/javascript-api-utility-functions

Strange Inheritances

Late last night I found myself channel surfing in my hotel room. Between infomercials, old Walton’s reruns and the cooking channel, I stumbled upon a new program called

“Strange Inheritance.” The premise of the show is something like this: Great Uncle Ralph dies and leaves his favorite niece a roadside Alligator Farm in rural Florida. The recipient of this wondrous bit of good fortune suddenly finds themselves in a predicament. Pardon the pun, but it’s the old “knee deep in alligator thing” except the person struggling didn’t create the mess. Instead they inherited it.

Distributors often find themselves in a similar situation. Examples are many. The last guy hired some doofus to work in the south territory and now you are left with a mess. The computer is loaded with 20 years of bad data and sales metrics won’t work until all the garbage is scrubbed and corrected. Or, territories were laid out inefficiently with salespeople crisscrossing the state in a haphazard manner racking up massive sales expenses.

Experience with dozens of distributors indicates the most common strange inheritance comes in the form of a poorly managed pricing process. Unlike the story of weird Uncle Ralph, this mess is often created by none other than our own previous decisions. The result is poor gross margin performance, sluggish bottom lines and, worse yet, customer issues. Let’s work through this short list in reverse order.

How can poor pricing process impact customers?
Nobody complains about your prices being too low, correct? Maybe. However, they will question your integrity when they get different prices from your company depending on who they call. And, when quantity breaks are poorly administered, they will wonder what the real price should be.

Further, if you hold prices for a very long time, absorbing price increases from suppliers over several years, eventually price levels will need to be adjusted. Typically, this results in extraordinary emergency price increases; the kind that come after you realize you have been losing money for the past year. Distributors often report seven or eight percent increases became necessary just to stay in a break even mode. This practice nearly always puts your whole book of business at risk of the customer.

Price process impacts profitability and gross margin
Everyone understands the connection between gross margin and profitability from a macro level: more margin equals greater profits. What they fail to understand is the relationship in an industry with very thin bottom line returns. For the typical distributor (regardless of line of trade,) the bottom line is razor thin; hovering in the 2-4 percent range. Allow me to break this down into the simplest of terms: for every $100 dollars in sales, the distributor keeps just over a couple of bucks. When the gross margin falls by even a percentage, the two dollars can become something far less. Conversely, increasing the gross margin by two POINTS, rather than percentage, has a major impact on the bottom line.

Each time a salesperson decides to offer a special discount of just a couple percent, profitability is hammered. Holding the line on price increases just to avoid hard conversations with the customer equates to a direct hit to the bottom line.

You’ve inherited a mess, now what?
Whether you inherited the issue of poor pricing policy from others or your evil twin took control of your body and created the whole situation, rest easy, you can fix the situation. It will take some time and a little planning, but you will see steady progress along the way. Join me as we review six time tested procedures to improve your pricing situation:

1. Make good use of manufacturers’ price increases.
Periodically, every manufacturer publishes price increases. In many industries the increases come near the end of the calendar year. Others come irregularly in relation to commodity price movements; things like copper, steel, oil, plastic or other raw materials. When these happen it’s not unusual for the company to publish some kind of justification but rarely is an actual increase percentage noted. We like to see distributors add a little extra for the “home team.” For instance, a manufacturer’s price increase comes in at two percent and the distributor provides the customer with a three percent price increase.

If you want to get advanced and have the proper discipline, prices for items not purchased in the past six months might be increased by an even larger percentage. Again referring to our two percent increase from the supplier, a two percent price increase might become a four percent increase on items not purchased for six months or more. Items never before purchased should be set at the “normal market price,” which will be discussed later.

2. Review all special pricing agreements.
Customers, especially those with professional purchasing types, negotiate prices based on high volume purchases and future growth. Somehow the promised volumes don’t happen. When this occurs, distributors should put the opportunity to good use by reopening the negotiations. Price needs to play a part in the negotiation. If the customer pushes to maintain the old price, this presents the perfect opportunity for a quid pro quo trade for additional purchases. The new products should be fairly priced and hopefully at an improved margin.

3. Narrow the scope of future special pricing agreement.

Distributors often err in setting the price for entire families of products. As a simplified example, imagine you are selling Wylie Coyote’s ACME Anvil line. ACME produces anvils in sizes ranging from a few pounds all the way to the giant 5,000 pound roadrunner smasher. OEMs apply the anvils to their products, but rarely is does any OEM purchase more than a half dozen sizes.

Typically distributors extended their best prices to the entire line up of ACME products. This point limits the opportunity to make additional revenues in those infrequent times when something outside the normal range was being purchased. Narrowing the scope of special pricing allows for margin improvement.

4. Tie pricing to payment terms.
It’s one thing to provide low products at low gross margin when the customer pays like clockwork and something else again to offer low prices and extended terms. Most companies publish standard terms; typically 30 days net in most industries. Conversely, it has become the custom of many large companies to pay in 60, 90 and even 105 days. Review all quotations for statements of payment, terms, and again, open up customer discussions on payment outside your expected terms.

5. Establish and enforce reasonable and real quantity pricing structure.
Candid conversations with sellers and their customer service counterparts indicate customer facing staff often stress over knowing exactly where to provide a quantity discount. For the customer who regularly purchases three parts at a time, an order of a half dozen may include an inquiry on quantity pricing breaks. Unless a policy is established and well documented, the decision becomes an uncomfortable judgment call.

I recommend building quantity pricing around numbers that make sense. For example, full case quantities might be justified over broken case quantities. The same goes with bundles, bags and all kinds packaging right on up to pallet loads. Secondly, quantity pricing should contain “stretch numbers” if a customer’s ongoing needs justify it, they should be able to push up to the next number. Otherwise, they pay a higher margin.

6. Ask manufacturers for additional discounts when you provide extra customer service on their behalf.
The practice of pushing back to supply partners for extra margin has been used, abused, bent, folded and otherwise mutilated. It’s not something I typically recommend. As a matter of fact, I almost decided to not mention this point. However, there are instances when distributors find themselves providing extra services for their supply partners and these instances demand an increase in margin. Examples from the field include, incoming inspections after quality issues, excessive warranty processing, maintenance of just-in-case inventory due to factory delivery issues and a number of other services which extend far beyond the distributor’s normal responsibility. Ask for additional margins in all of these cases.

An ounce of prevention is worth a pound of cure…
By now you’ve no doubt surmised, fixing inherited pricing issues takes more effort than just establishing a process today. Any real pricing process must contain the following: documentation, metrics and measures, and management tools. If you miss any of these three pieces, the process is doomed to failure.

Looking back at the whole inherited pricing problem, most would wonder how something like this could even happen in an age of high powered ERP systems and computerization. But it does. Most of these same folks have been schooled in matrix pricing and have heard industry experts talk about the importance of margin improvement. Yet, the problem still exists.

Distributor pricing is not hard. However, it is massive. Consider a distributor with 3,000 accounts and a line card containing 10,000 SKUs. This creates over 30 Million price permutations. Balancing that many data points is not humanly possible, regardless of your skills with spreadsheet. Clearly, some kind of additional resources are in order.

David Bauders and his Strategic Pricing Associates team has developed a real process for pricing. They attack the pricing problem from three directions:
• Scientific analysis of data from your own customer history and invoices – this allows better understanding of the true market price and price sensitivity by each customer.
• A well-documented plan implementation including training and ongoing employee education – customer facing employees learn how to avoid pitfalls and poor pricing habits. Procedures and best practices are laid out.
• Metrics and Measures are provided for coaching and managing the process – recalling Pearson’s law: "When performance is measured, performance improves. When performance is measured and reported back, the rate of improvement accelerates." Coaching and managing with real data can drive behavior.

The real proof of the process comes from the over 350 distributors who have applied the Strategic Pricing Associates process. Distributors report a typical margin improvement of two full points. Imagine how two points could impact your bottom line. Why wait till that dark gloomy day when you find you have inherited something?

What are the key differences in snowflake and star schema? Where should they be applied?

What are the key differences in snowflake and star schema? Where should they be applied?

The Star schema vs Snowflake schema comparison brings forth four fundamental differences to the fore:

1. Data optimization: 

Snowflake model uses normalized data, i.e. the data is organized inside the database in order to eliminate redundancy and thus helps to reduce the amount of data. The hierarchy of the business and its dimensions are preserved in the data model through referential integrity.

Figure 1 – Snow flake model

Star model on the other hand uses de-normalized data. In the star model, dimensions directly refer to fact table and business hierarchy is not implemented via referential integrity between dimensions.

Figure 2 – Star model

2. Business model:

Primary key is a single unique key (data attribute) that is selected for a particular data. In the previous ‘advertiser’ example, the Advertiser_ID will be the primary key (business key) of a dimension table. The foreign key (referential attribute) is just a field in one table that matches a primary key of another dimension table. In our example, the Advertiser_ID could be a foreign key in Account_dimension.

In the snowflake model, the business hierarchy of data model is represented in a primary key – Foreign key relationship between the various dimension tables.

In the star model all required dimension-tables have only foreign keys in the fact tables.

3. Performance:

The third differentiator in this Star schema vs Snowflake schema face off is the performance of these models. The Snowflake model has higher number of joins between dimension table and then again the fact table and hence the performance is slower. For instance, if you want to know the Advertiser details, this model will ask for a lot of information such as the Advertiser Name, ID and address for which advertiser and account table needs to be joined with each other and then joined with fact table.

The Star model on the other hand has lesser joins between dimension tables and the facts table. In this model if you need information on the advertiser you will just have to join Advertiser dimension table with fact table.

Star schema explained

Star schema provides fast response to queries and forms the ideal source for cube structures. 

4. ETL

Snowflake model loads the data marts and hence the ETL job is more complex in design and cannot be parallelized as dependency model restricts it.

The Star model loads dimension table without dependency between dimensions and hence the ETL job is simpler and can achieve higher parallelism.

This brings us to the end of the Star schema vs Snowflake schema debate. But where exactly do these approaches make sense?

Where do the two methods fit in?

With the snowflake model, dimension analysis is easier. For example, ‘how many accounts or campaigns are online for a given Advertiser?’

The star schema model is useful for Metrics analysis, such as – ‘What is the revenue for a given customer?’

Tableau - Tips,Tricks,Best Practices

    If you are working with large dataset and the performance is an issue, try stopping Auto Update Worksheet (you can keep the auto update quick filter if it will not affect the performance).

    You can also add filters first to limit the number of records shown in the visualization; remove the filters after completing the visualization.

    Turn off the Auto Update Dashboard will also stop the auto update worksheet for the worksheets on the dashboard

    A trick I find useful is to mimic you visualization with a small sample dataset (the superstore data source is simple but very useful). After you are done with your design, you can create the visualization in the same format with your real datasets.

    If one data connections is large and takes a long time to query, using a join to link this table with another table can increase query time dramatically. A better approach is to use data blending to blend only the summary of the larger table with the smaller table.

    For example, you can set up data blending on the order year rather than the order date, or on the product category instead of the product name to reduce the size of data returned from the second data source. If possible, be sure to set the smaller and faster table as the primary data source and use data blending to pull a summary from the larger table.

    Performance tips: use extract, use filter/set/aggregate to reduce data, use WINDOW_SUM instead of Total

    Use context filter to improve performance http://kb.tableausoftware.com/articles/knowledgebase/using-context-filters-improve-performance

        Using a single context filter that significantly reduces the size of the data set is much better than applying many context filters.

        Complete all of your data modeling before creating a context. Changes in the data model such as converting dimensions to measures require recomputing the context.

        Set the necessary filters for the context and create the context before adding fields to other shelves. Doing this work first makes the queries that are run when you drop fields on other shelves much faster.

        If you want to set a context filter on a date you can use a continuous date. However, using date bins like YEAR(date) or context filters on discrete dates are very effective.

    If your data set is heavily indexed, context filters may not provide performance improvement and may actually cause slower query performance.

    http://kb.tableausoftware.com/articles/knowledgebase/using-context-filters-improve-performance

    Context filters can adversely affect any query performance improvements when using the Include joined tables only when referenced option in the Tables dialog box. See Adding Tables to the Data window

    When you have table calcs and dates that are of the DATE or DATETIME data type, and are addressing along the dates, Tableau starts padding the domain (i.e. generating a row for every combination of dimensions and all of the dates, not just the dates that go with the dimension) and this can have a significant impact on performance. A workaround for this is to create a string field for the date, use that in the view in place of the date, and set the compute using to that.

    Performance recording http://onlinehelp.tableausoftware.com/current/pro/online/en-us/help.htm#perf_record_create_desktop.html

    Performance Tips

    http://onlinehelp.tableausoftware.com/current/pro/online/windows/en-us/help.htm#performance_tips.html

    Simplify your calculations : Performance -Boolean > Number > Date > String. Eg, use SUM(Sales) >0 instead of If SUM(Sales) >0 Then “True” Else “False” End

    Avoid using too many parameters, too many views in a dashboard, too many worksheets in a workbook, too many filters

    IF (FIRST()==0) THEN

    WINDOW_SUM(SUM([Measure]), 0, IIF(FIRST()==0, LAST(), 0)) END only calculate the window_sum once, save resources http://community.tableausoftware.com/message/197038 http://www.clearlyandsimply.com/clearly_and_simply/2011/01/another-look-at-site-catchment-analysis-with-tableau-6-part-3.html

Embedding a Tableau View into SharePoint 2010

This article is an outcome of the research that we did for one of our clients, where we evaluated the options to embed/render Tableau reports in SharePoint 2010/2013.  Even though there were some help content available in Tableau, they were all scattered and any one new to the subject would be required to spend sometime to identify the options before embracing SharePoint as a delivery mechanism for rendering Tableau Views.
In short, Tableau reports can be rendered in SharePoint in the following ways:

1. Using TableauEmbeddedView WebPart
2. Using Page Viewer web part (available in Media and Content categories) and providing the URL to the view name in the Tableau report.
3. Using Tableau JavaScript API’s and inducing the IFrame into a HTML page.

Note: This article assumes that the Tableau Server is already configured in your local environment with Windows Authentication.  In addition, Tableau supports Local and Trusted authentication which works for scenarios where integrated windows / SSPI wont be the ideal choice for authentication purposes.  More details on this topic can be found here.
The significant difference between windows and local authentication mode is that the TableauEmbeddedView WebPart and the PageViewer Web Part will pick the currently logged in windows credentials for authenticating against Tableau, however in local authentication it will just need the logged in user name in SharePoint and doesn’t care about the local logged user credentials in the client machine.
The following are the step by step instructions to access a tableau report in SharePoint:

1. Install and configure the Tableau WebPart by performing the following steps:
   1. Locate the TableauEmbeddedView.dll and TableauEmbeddedView.wsp files that install with Tableau Server: C:\Program Files\Tableau\Tableau Server\8.1\extras\embedding\sharepoint\
   2. Open a command prompt as an administrator.
   3. Navigate to the C:\Program Files (x86)\Common Files\Microsoft Shared\Web Server Extensions\14\BIN
   4. Run the following command to add the .wsp file:
      stsadm -o addsolution -filename “C:\Inetpub\wwwroot\wss\VirtualDirectories\80\bin\TableauEmbeddedView.wsp”
   5. Next, run the following command to deploy it. In the command, http://<your SharePoint site>/ should be the root directory of your SharePoint site, such as http://mySharePoint/.
      stsadm -o deploysolution -name TableauEmbeddedView.wsp  -url http://<your SharePoint Site>/ -local -force -allowgacdeployment
   6. Finally, activate the Web Part feature by running the following command:
      stsadm -o activatefeature -name TableauEmbeddedView_Feature1 -url http://<your SharePoint Site>/
2.  Login as site administrator in SharePoint and create/navigate to the page where you want to embed the tableau view.
3. In the Edit Page Mode, Select “Insert Web Part” and select “Custom” category.
4. Now select the “TableauEmbeddedView” and click on “Add”
5. Once the web part is added, navigate to the Edit Properties option and provide the following values in “Tableau View Settings”
   1. Under Tableau Server Name, provide the name of the server (e.g.,) TableauServ1
   2. Under View Path, provide the name of the view (e.g.,) /views/Sales/2013SalesGrowth
6. Click Ok to see the tableau view appearing in the site.

• If its not appearing, check the URL to the view and make sure that its copied exactly as is into the web part properties.  The above steps also apply for the “Page Viewer” web part, except that it would be included in place of the TableauEmbeddedView.

SharePoint 2010 or 2013
As per Tableau, SharePoint 2013 is needed when you use “Trusted Authentication” mode (in Tableau) as it better conforms to the security standards set by it.  However, when I tried in 2010, even the Trusted authentication worked without any issues, but needed some tweaks in the Tableau WebPart, which is beyond the scope of this article.

Embed Views into SharePoint (Local Authentication)

You can embed a Tableau Server view in a SharePoint page. If Tableau Server is using Local Authentication for user authentication, there are some extra steps you need to take before you start embedding views.
This topic describes how to complete the following steps:

• Edit the security permissions for the TableauEmbeddedView.dll file.
• Install and deploy the TableauEmbeddedView.wsp file.
  
• Verify the web part’s deployment.
  
• Embed a view in SharePoint using the Tableau web part.
  

Note: If your Tableau Server installation is using Active Directory for user authentication, you can start embedding views right away. For more information, see Embed Views into SharePoint (Microsoft SSPI).

Requirements

Users: To access an embedded view, users must be licensed Tableau Server users and their user name on SharePoint must be the same as their user name on Tableau Server.
SharePoint version: Starting with Tableau Server 8.1, you must use SharePoint 2013 to embed Tableau Server views in SharePoint pages. SharePoint 2013 uses Microsoft .NET Framework version 4.5, which meets Tableau Server's security requirements.

Edit Security Permissions for TableauEmbeddedView.dll

Edit the security permissions for TableauEmbeddedView.dll so that all users of the operating system can use it.

1. Locate the TableauEmbeddedView.dll and TableauEmbeddedView.wsp files that install with Tableau Server. If Tableau Server is installed on drive C, the files will be in the following directory:
   C:\Program Files\Tableau\Tableau Server\9.0\extras\embedding\sharepoint\
   
2. Copy the files to the root directory of your SharePoint server. The root directory is usually located at C:\Inetpub\wwwroot\wss\VirtualDirectories\<port>\bin, for example:
   

   C:\Inetpub\wwwroot\wss\VirtualDirectories\80\bin

3. To edit the security permissions on TableauEmbeddedView.dll, right-click TableauEmbedded.dll and then select Properties > Security.
   
4. Under Group or user names, select Everyone, and then click Edit.
   
   
5. Under Permissions for Everyone, for the Full control permission, select Allow.
   
   
6. Click OK.  
   

Install and Deploy TableauEmbeddedView.wsp

The TableauEmbeddedView.wsp file gives SharePoint more information about what to do with the .dll file. You copied the TableauEmbeddedView.wsp file to the SharePoint root directory in the previous procedure. To install and deploy the .wsp file, follow these steps:

1. Open SharePoint 2013 Management Shell and enter the following command:
   Add-SPSolution -LiteralPath "C:\Inetpub\wwwroot\wss\VirtualDirectories\80\bin\TableauEmbeddedView.wsp"
   
2. On the SharePoint Central Administration home page, click System Settings.
   
3. In the Farm Management section, click Manage farm solutions.
   
4. On the Solution Management page, click the solution that you want to deploy.
   
5. On the Solution Properties page, click Deploy Solution.
   
6. On the Deploy Solution page, in the Deploy When section, select one of the following options:
   
   • Now
     
   • At a specified time. Specify a time by using the date and time boxes.
     
7. In the Deploy To? section, in the A specific web application list, click All web applications or select a specific Web application, and then click OK.
   
8. Open your SharePoint site. Click the settings icon, and then select Site settings.
   
   
9. Under Site Collection Administration, click Site collection features.
   
10. Scroll to the TableauEmbeddedView feature and then click Activate to activate the feature.
    

Verify the Web Part's Deployment

In the following procedure, you will verify that the Tableau web part is installed.

1. Open your SharePoint site in a web browser.
   It may take a few moments for the site to appear.
   
2. Click the settings icon, and then select Site settings.
   
3. Under Web Designer Galleries, click Web parts.
   
   
4. Confirm that TableauEmbeddedView.webpart is listed.
   

Embed a View Using the Tableau Web Part

You can embed the Tableau web part in a new or existing SharePoint page.

1. Open the page where you want to embed a view and switch to edit mode.
   
2. In the section of the page where you want to embed the view, on the Insert tab, click Web Part.
   
3. Under Categories, in the Custom (or Miscellaneous) folder, select TableauEmbeddedView, and then click Add in the lower-right corner.
   
   
4. Select the TableauEmbeddedView web part, click the drop-down arrow, and then select Edit Web Part.
   
   
5. On the right side of the page, you can specify the attributes of the TableauEmbeddedView web part.
   
   • In Tableau Server Name, enter the name of your Tableau Server. You do not need to enter "http://" before the Tableau Server name.
   • In View Path, enter the path to the view you want to embed.
   • Specify whether you want to show the toolbar, use Trusted Authentication, use SSL, or if you want to embed the view as an image instead of as an interactive view.
     
   • In the Appearance section you can specify a Title for the web part, the Height, Width, Chrome State, and Chrome Type. In general you should specify a fixed height (for example, 700 Pixels) and adjust the width to fit the zone.
     
     
6. Click OK to apply the changes and exit edit mode.
   

Now the view is embedded in the page and users who access it will be automatically signed in based on their user name and password for SharePoint.

This is an example of embedding views into SharePoint using the provided .dll file. You can also embed views into other types of web applications. See JavaScript API for more information.

Tableau Authentication

Authentication

Authentication establishes a user's identity. Tableau Server has its own user identity and authentication system that lets you determine who can sign in to Tableau Server and who can publish content to the server. This system also allows a personalized user experience for users who access your instance of Tableau Server.
Users sign in to Tableau Server by opening a browser and entering the name or IP address of the server. They are then prompted to enter their username and password:

User identity in Tableau Server

Any user who signs in and works with content in Tableau Server must have a user identity in the Tableau Server repository and must be assigned a site role. User identities can be added to Tableau Server in the server UI, using tabcmd Commands, or using the REST API.
If the server is configured to use local authentication, when you add a user identity, you specify a username, a password and a site role. In that case, the Tableau Server repository is used exclusively to authenticate the user.
If the server is configured to use Active Directory authentication, the username and password is managed in Active Directory. In that case, when users sign in to the server, their username and password is verified using Active Directory.
For more information, see Users.

Single sign-on options for Tableau Server

Tableau Server supports several types of single sign-on (SSO). With SSO, users don't have to explicitly sign in to Tableau Server. Instead, the credentials they've used to authenticate already (for example, by signing in to your corporate network) are used to authenticate them to Tableau Server, and they can skip the step of entering a username and password to access Tableau Server. With SSO, the user's identity as established externally is mapped to a user identity defined in the Tableau Server repository.
Tableau Server supports these types of SSO:

• SAML.You can configure Tableau Server to use SAML (security assertion markup language) for SSO. With SAML, an external identity provider (IdP) authenticates the user's credentials, and then sends a security assertion to Tableau Server that provides information about the user's identity. For more information, see SAML.
• Kerberos. If Kerberos is enabled in your environment and if the server is configured to use Active Directory authentication, you can provide users with access to Tableau Server based on their Windows identities. For more information, see Kerberos.
• Trusted Authentication. Trusted authentication lets you set up a trusted relationship between Tableau Server and one or more web servers. When Tableau Server receives requests from a trusted web server, it assumes that the web server has already handled whatever authentication is necessary. Tableau Server receives the request with a redeemable token or ticket and presents the user with a personalized view which takes into consideration the user’s role and permissions. For more information, see Trusted Authentication.

Authentication for the REST API

The REST API lets you manage and change Tableau Server resources programmatically, via HTTP. In order to make requests to the server, you must programmatically sign in to the server. The server sends an authentication token that you then add to subsequent requests. For more information, see Signing In and Out (Authentication) in hte REST API documentation.

How to Publish Workbooks to a Tableau Server, Scheduling and Authentication

After you create a workbook, you can publish to Tableau Server or Tableau Online by following the steps below.

1. Select Server > Publish Workbook.
   
   If you are not already signed in to Tableau Server, you will see the Tableau Server Sign In dialog box.
   
2. Enter the server name or URL (for example, sales_server, or https://sales_server).
   To sign in to Tableau Online, enter https://online.tableau.com.
   Click Connect.
   
   If Tableau Server is Kerberos-enabled and you are on a machine with valid Active Directory credentials, you will connect automatically to the server. In this case, you can skip step 3. If Kerberos authentication fails, you will be prompted to provide a user name and password for authentication. If you need to change the user you're signed in as, see Switch Kerberos User.
   
3. Next, enter your user name for Tableau Server on-premise or email address for Tableau Online, enter your password, and then click Sign In.
   
   If Tableau Server on-premise is configured to use Active Directory, enter your Windows user name (the domain is not required—except in multi-domain environments where the user is not in the default domain); otherwise, enter your Tableau Server user name.
   If Tableau Server is configured to use SAML for user authentication, you won't see the above dialog box. Instead, you'll see a sign in prompt from an external identity provider.
   
4. In the Publish Workbook to Tableau Server dialog box, specify the following:
   
   • Project: A project is like a folder that can contain workbooks and data sources. The default project on Tableau Server is named Default. All workbooks must be published into a project.
     
   • Name: Provide a name for the workbook in the Name text box.
     You can use the drop-down list to select an existing workbook on the server. In this case, the workbook on the server is overwritten. To do this, you must have the Write permission for the workbook on the server.
     
   • Add Tags: You can enter one or more keywords that describe the workbook into the Tags text box. Tags help users find related workbooks when browsing the server.
     Multiple tags should be separated using either a comma or a space. If any of the tags contains a space, use quotations (e.g., “Sales Quotes”).
     
     
5. You can specify permissions to allow or deny access to the workbook on the server.
   By default all users can interact with the workbook and you, as the publisher, have all permissions. See Setting Permissions for details, and see Permissions for information on the different permissions you can assign to users and groups.
   
6. Under Views to Share, select the sheets you want to share on Tableau Server. Any sheets that are not selected are hidden on the server.
   Showing and hiding worksheets is useful when you want to publish a dashboard or story without publishing the worksheets that were used to create that dashboard or story. For example, when you publish a workbook that has several worksheets and a summary dashboard, you can select to hide the individual worksheets and only show the dashboard. Remember though, that anyone allowed the Download/Web Save As permission can download the workbook from the server and then access the hidden worksheets. So hiding worksheets is not a secure way of concealing information.
   Hidden sheets can still be accessed when a workbook is opened from the server using Tableau Professional. Users must be given the Download/Web Save As permission to open the workbook from the server. Hidden sheets can also be used by users with appropriate credentials from edit mode . See Edit and Create Views in the Tableau Server help.
   
7. Select whether to Show Sheets as Tabs.
   When this option is selected, all workbook sheets are displayed on a row of tabs in each individual view. Users can then click to see the other views in the workbook.
   
   When you select this option, all the sheets are shown regardless of any sheet level permissions you may have already set up. If you want to enforce sheet level security, you should make sure the Show Sheets as Tabs option is cleared.
8. Select whether to Show Selections. When this option is selected, any selections you’ve made in the workbook will be published to the server.
   
9. Select whether to Include External Files. When you include external files, a copy of any referenced external file data sources or background images are published along with the workbook. External files include Excel, Text, Data Extract, and image files. If you don’t include these files, others may not be able to see the worksheets online.
   If you are publishing to an on-premise Tableau Server, and the workbook references data sources or images on a mapped drive, be sure to check Include External Files. If you do not want to publish the external files to the server, for example because you want to preserve a live connection to a file-based data source after publishing to Tableau Server, change the connection information so that the workbook references a full UNC path. For example rather than connecting to D:\datasource.xls, you would connect to \\filesrv\datasource.xls.
   If you are publishing to Tableau Online, and the workbook connects to a data source other than Google BigQuery or Amazon Redshift, you will need to include external files.
   
10. Optionally, click Scheduling and Authentication. Decide whether to embed user names and passwords, so that server users won’t have to have an account on the database to see the views. For information about embedding passwords, see Embedding Passwords and Authentication.
    If the workbook uses Extract connections you can optionally add the workbook to a refresh schedule.
    
11. Click Publish.
    

Note: When publishing a single worksheet in a workbook containing multiple data sources, each data source, regardless of whether or not it is active in the worksheet, may require authentication.

Switch Kerberos User

If you're in a Kerberos environment and you need to change the user you're signed in as on Tableau Server (typically for testing purposes), on the Server menu, click Switch User.

Note: If the Switch User option is not displayed on the server menu, the Tableau Server you are connected to may not be configured for Kerberos single sign-on (SSO).

Selecting Switch User opens the Tableau Server Sign In dialog box. You can sign in with a different user name and password. The Tableau Desktop status bar will reflect the user name change.
If you sign in as a different user and then need to sign back in as yourself, on the Server menu, click Switch to Self.