Tableau K-Means Clustering Analysis w/ NBA Data

Interact with this visualization on Tableau Public.

In this video we will explore the Tableau K-Means Clustering algorithm. K-Means Clustering is an effective way to segment your data points into groups when those data points have not explicitly been assigned to groups within your population. Analysts can use clustering to assign customers to different groups for marketing campaigns, or to group transaction items together in order to predict credit card fraud.

In this analysis, we’ll take a look at the NBA point guard and center positions. Our aim is to determine if Tableau’s clustering algorithm is smart enough to categorize these two distinct positions based upon a player’s number of assists and blocks per game.

Nicola Jokic is a Statistical Unicorn

If you also watch the following video you’ll understand why 6 ft. 11 center Nikola Jokic is mistakenly categorized as a point guard by the algorithm. This big man can drop some dimes!

Subscribe to my youtube channel to learn more about Tableau and data analysis.

Advertisements

Ranking Banks by Number of Complaints

I recently downloaded a dataset from the Consumer Finance Protection Bureau (CFPB) in order to construct a handy visualization. The CFPB maintains a database that houses a collection of complaints on a range of consumer financial products and services that are sent to companies for a response.

Per the CFPB, “the database also includes information about the actions taken by the company in response to the complaint, such as, whether the company’s response was timely and how the company responded.”

Although the database is updated daily, I chose to visualize information from the complete year of 2017. In fairness to the financial institutions, company level information should be considered in context of company size and/or market share.

Financial institutions analyze this information frequently as a way of understanding and continuously improving their customer service.

I highly recommend “The Big Book of Dashboards” by Jeffrey Shaffer, Andy Cotgreave and Steve Wexler. The book contains a number of visualization examples that provide guidance on dashboard creation for any number of business use cases. In this Tableau Public dashboard I relied heavily on the visual guidance for their Complaints Dashboard as you can observe.

Screen Shot 2018-06-03 at 10.02.14 PM

Complaints Dashboard from “The Big Book of Dashboards”

Click on the picture link to view the dashboard on Tableau Public (not optimized for mobile).

Dashboard 1

Create Multiple KPI Donut Charts in Tableau

In honor of National Doughnut Day (June 1st), let’s devour this sweet Tableau tip without worrying about the calories. In this video I we will create a multiple donut chart visualization that will display the sum of profits by a region. Then we’ll use the donuts as a filter for a simple dashboard. Once you finish watching this video you’ll know how to create and use donut charts as a filter to other information on your dashboard.

I know that donuts are not considered best practice, (especially when negative numbers are involved) but they have their uses. Assuming you know that bar charts are a best practice, it never hurts to learn other techniques that add a little “flair” from the boring world of bar charts.

Have you ever looked at a Picasso painting? Obviously Picasso was well versed in painting best practices (understatement) but in some of his art, the people are not rendered in the best practice. Always learn the best practices, but know when to leave them behind and add a little flair! (In no way am I comparing myself to Picasso).

Three-Musicians-By-Pablo-Picasso

Three Musicians – Pablo Picasso

Three Musicians by Picasso is not best practice but it is a work of art!

Subscribe to my youtube channel to learn more about Tableau and data analysis.

How to Use Jittering in Tableau (Scattered Data Points)

 

In this video I will explain the concept of jittering and how to use it to scatter your data points in Tableau. In a normal box plot Tableau data points are stacked on top of each other which makes it more difficult to understand positioning. By using this simple tip combining a calculated field a parameter, you will be on your way to gaining a better understanding of your data points. We’re going to get our “Moneyball” on by analyzing average NBA player points per game in the 2016 season.

 

Basic Statistics in Tableau: Correlation

This is a guest post provided by Juturu Pavan, Prudhvi Sai Ram, Saneesh Veetil and Chaitanya Sagar of Perceptive Analytics.

Statistics in Tableau

Data in the right hands can be extremely powerful and can be a key element in decision making. American statistician, W. Edwards Deming quoted that, “In God we trust. Everyone else, bring data”. We can employ statistical measures to analyze data and make informed decisions. Tableau enables us to calculate multiple types of statistical measures like residuals, correlation, regression, covariance, trend lines and many more.

Today let’s discuss how people misunderstand causation and correlation using Tableau.

Correlation and Causation

Correlation is a statistical measure that describes the magnitude and direction of a relationship between two or more variables.

Causation shows that one event is a result of the occurrence of another event, which demonstrates a causal relationship between the two events. This is also known as cause and effect.

Types of correlation:

  1. 1 → Positive correlation.
  2. -1 → Negative Correlation.
  3. 0 → No correlation.

Why are correlation and causation important?

The objective of analysing data is to identify the extent by which a variable relates to another variable.

Examples of Correlation and Causation

  1. Vending machines and obesity in schools: people gain weight due to junk food. One important source of junk food in schools is vending machines. So if we remove vending machines from schools obesity must reduce, right? But it isn’t true. Research shows that children who move from schools without vending machines to schools with vending machines don’t gain weight. Here we can find a correlation between children who were overweight and eating junk food from vending machines. In actuality, the “causal” point (which is the removal of vending machines from schools) has a negligible effect on obesity.
  2. Ice cream sales and temperature: If we observe ice cream sales and temperature in the summer, we can determine that they are causally related; i.e. there is a strong correlation between them. As temperature increases, ice cream consumption also increases. Understanding correlation and causation allows people to understand data better.

Now let’s explore correlation using Tableau. We are going to use the orders table from the superstore dataset which comes default with Tableau.

Before going further let’s understand how to calculate the correlation coefficient ‘r’.

We can easily understand the above formula by breaking it into pieces.

In Tableau, we can represent the above formula as 1/SIZE() -1 where SIZE is function in Tableau.

We can use WINDOWSUM function for doing this summation in Tableau.

xi is the sum of profit and x-bar is the mean of profit, which is window average of sum of profit, and sx is standard deviation of profit. That means that we need to subtract mean from sum of profit and divide that by standard deviation.

(SUM([Profit])-WINDOW_AVG(SUM([Profit]))) / WINDOW_STDEV(SUM([Profit])))

This is similar to the formula above but we only need to swap profit with sales.

(SUM([Sales])-WINDOW_AVG(SUM([Sales]))) / WINDOW_STDEV(SUM([Sales])))

Now we have to join all these formulae to get the value of the correlation coefficient of r. Be careful while using parenthesis or you may face errors. Here is our final formula to calculate r.

1/(SIZE()-1) * WINDOW_SUM(( (SUM([Profit])-WINDOW_AVG(SUM([Profit]))) / WINDOW_STDEV(SUM([Profit]))) * (SUM([Sales])-WINDOW_AVG(SUM([Sales]))) / WINDOW_STDEV(SUM([Sales])))

Let’s implement this in Tableau to see how it works. Load superstore data into Tableau before getting started.

After loading the superstore excel file into Tableau, examine the data in the orders sheet. You can see that it contains store order details complete with sales and profits. We will use this data to find correlation between profit and sales.

Let’s get our hands dirty by making a visualization. Go to sheet1 to get started. I made a plot between profit and sales per category.

Now in order to find the correlation between profit and sales, we need to use our formula to make a calculated field which serves our purpose.

Now drag and drop our calculated field onto the colors card and make sure to compute using customer name as we are using it for detailing.

Here we can see the strength of the relationship between profits and sales of data per category; the darker the color, th he stronger the correlation.

Next we’ll add trend lines to determine the direction of forecasted sales.

These trend lines help demonstrate which type of correlation (positive, negative or zero correlation) there is in our data. You can explore some more and gain additional insights if you add different variables like region.

From this analysis we can understand how two or more variables are correlated with each other. We begin to understand how each region’s sales and profits are related.

Let’s see how a correlation matrix helps us represent the relationship between multiple variables.

A correlation matrix is used to understand the dependence between multiple variables at same time. Correlation matrices are very helpful in obtaining insights between the same variables or commodities. They are very useful in market basket analysis.

Let’s see how it works in Tableau. Download the “mtcars” dataset from this link. After downloading it, connect it to Tableau and explore the dataset.

The dataset has 35 variables where each row represents one model of car and each column represents an attribute of that car.

Variables present in dataset:

Mpg = Miles/gallon.

Cyl = Number of Cylinders.

Disp = Displacement (cubic inches)

Hp = Gross Horsepower

Drat = Rear axle ratio

Wt = Weight (lb/1000)

Qsec = ¼ mile time

Vs = V/Sec

Am = Transmission (0 = automatic, 1 = manual)

Gear = Number of forward gears

Carb =Number of Carburetors

Let’s use these variables to make our visualization. I made this amazing visualization showing correlation between models by referring to Bore Beran’s blog article, in which he explained how to make this visualization which helps us understand more about using Tableau to understand correlation.

Conclusion

We must keep in mind that if we want to measure the dependence between two variables, correlation is the best way to do it. A correlation value always lies between -1 and 1. The closer the value of the correlation coefficient is to 1, the stronger their relationship. We must remember that correlation is not causation and many people misunderstand this. There are many more relations and insights that can be unlocked from this dataset. Explore more by experimenting with this dataset using Tableau. Practice to be perfect.

Author Bio

This article was contributed by Perceptive Analytics. Juturu Pavan, Prudhvi Sai Ram, Saneesh Veetil and Chaitanya Sagar contributed to this article.

Perceptive Analytics provides Tableau Consulting, data analytics, business intelligence and reporting services to e-commerce, retail, healthcare and pharmaceutical industries. Our client roster includes Fortune 500 and NYSE listed companies in the USA and India.

How to Fix an Import Specification Error in Microsoft Access

There are certain aspects of Microsoft Access that can be downright frustrating and puzzling to debug. I want to share a tip with you that will hopefully save you hours of frustration. There is nothing more foundational than importing data into Microsoft Access so most likely you’ll appreciate the fix for this run-time error if you are attempting to use VBA.

If you encounter the following Microsoft Access Error:

“Run-Time error ‘3625’: The text file specification ‘My Saved Access Import Spec’ does not exist. You cannot import, export, or link using that specification.”

Most likely you have confused a saved set of “import steps” with a saved “Import/Export specification” while trying to use the Docmd.TransferText command; or at least I did.

Consider the following sample VBA code that uses the Docmd.TransferText command to import a delimited file (from a path stored in string variable strInputFileName) into a table named “tbl_Access_Import_Data” using an import specification.

Private Sub cmd_Import_Table_Click()

Dim strInputFileName As String
‘Set Path to Local CSV File. This file will be imported into an Access Table.
strInputFileName = “C:\Users\Desktop\Access Data\Access_Import_Data”

‘ Use a Macro to Import a delimited file
‘ “My Saved Access Import Spec” = Import Spec
‘ “tbl_Access_Import_Data” = Destination Access Table
‘ strInputFileName = hardcoded path to source csv file

DoCmd.TransferText acImportDelim, “My Saved Access Import Spec”, “tbl_Access_Import_Data”, strInputFileName

End Sub

4. Error 3625 Edited 2

Let me show you where I went off track. I saved “import steps” and then tried to reference the saved “import steps” with the Docmd.TransferText method. You cannot reference “import steps” with this method, only “Import/Export specifications”.

1. Import Text Wizard Edited

I used the Import Text Wizard to define and delimit the columns in a specified .csv file and indicated the table I desired to have that data imported into. Afterwards, I pressed the finish button.

2. Import Text Wizard Blurred

Once I hit “Finish”, on the very next screen I saved the “import steps” that I previously defined. Notice the verbiage next to step 1 (i.e. “Save import steps”).

3. Saved Fake Spec Blurred

As you can see above, I created a saved “import step” erroneously named “My Saved Access Import Spec”. This name was the value that I erroneously passed to the Docmd.TransferText method in code.

4. Error 3625 Edited 2

These actions result in ‘Run-time error 3625’ that we will fix.

5. Import data Secification Edited

In order to save a legitimate Import/Export specification that can be successfully referenced with the Docmd.TransferText method, make sure to hit the “Advanced” button before you hit “Finish” when you come to the last window of the Import Text Wizard.

Make sure to hit “Save As” (Step 2 above) on the right hand side of the window.

6. Capture Edited

At this point, name and then save your true Import/Export specification name and hit “OK”.

Now when you come to the same window again you can hit the “Specs…” button to observe the names of all of the saved Import/Export specifications.

7. Specs Button Edited

In the pic above I only have 1 Import/Export specification named “My Real Saved Access Import Spec”.

7.5 Import Complete Edited 2

Observe, once the true Import/Export specification is referenced in VBA code, the code executes as intended.

Additional Tips

I am not aware of how to edit Import/Export specifications. The best advice that I have is to recreate and then overwrite the existing specification or save the new revised specification with a different name.

If you place the following SQL code in a blank Select Query, you can view all the true specification names along with field names and respective field widths.

SELECT
MSysIMEXSpecs.SpecName,
MSysIMEXColumns.FieldName,
MSysIMEXColumns.Start,
MSysIMEXColumns.Width,
MSysIMEXColumns.SkipColumn
FROM MSysIMEXColumns
INNER JOIN MSysIMEXSpecs
ON MSysIMEXColumns.SpecID = MSysIMEXSpecs.SpecID

ORDER BY MSysIMEXSpecs.SpecName,
MSysIMEXColumns.Start,
MSysIMEXColumns.Width;

8. SQL Results Edited

The results of that query from my example database are shown above. All due credit goes to stackoverflow for this SQL tip.

https://stackoverflow.com/questions/34295360/the-text-file-specification-does-not-exist-when-importing-into-access

3. Saved Fake Spec Blurred

Furthermore, if you are intent on referencing saved import steps in VBA code (not to be confused with the aforementioned Import/Export specification), then use the Docmd.RunSavedImportExport method.

To execute the “import step” shown in the picture above using VBA, I would use the following command:

DoCmd.RunSavedImportExport “My Saved Access Import Spec”

I hope this helps solve your “how to fix Run-Time error 3625 in Microsoft Access” question. Good luck!

Add a “Reset All Filters” Button to Your Tableau Dashboard

Help users navigate your Tableau dashboard with less effort. In this video I will show you how to create a “Reset All Filters” button on a Tableau dashboard. We achieve the desired effect by using a Tableau action that runs on select of a mark.

The data I am using for illustration purposes is primarily sourced from Mockaroo.com and is loosely based upon data from an actual client of mine. All vendor names, dates, amounts and other data are changed substantially from original form. Feel free to contact me if you need an analysis of your Accounts Payable ERP data from PeopleSoft, JD Edwards or any other source!

Broadcom & Qualcomm: The Largest Deal in the History of Technology That Never Happened

One hundred and twenty one billion dollars; the number was staggering as it potentially represented the largest deal in the history of technology. While neither Broadcom nor Qualcomm are household names like Samsung or Intel, the combined company would have created the world’s third largest chip maker.

On November 2, 2017 Hock Tan, the chief executive of Broadcom, announced the company’s intent to redomicile its headquarters from Singapore to the United States. Mr. Tan made this announcement from the Oval Office while the current president of the United States looked on approvingly in the background.

Mr. Tan was effusive in his praise of America and its newly overhauled corporate tax rates. He preached that many of his direct managers and a majority of Broadcom’s board members and shareholders were also Americans. “America is once again the best place to lead a business with a global footprint” according to Mr. Tan. The ulterior motive of this patriotic show was revealed just four days later when Broadcom announced its intent to buy notably larger competitor Qualcomm for $105 billion dollars. The hostile takeover deal was eventually bumped up to $121 billion after being rebuffed by Qualcomm. Broadcom eventually retaliated by nominating its own directors to Qualcomm’s board to force a potential deal.

While calling the timing of the Qualcomm takeover announcement a “coincidence”, Broadcom was betting its chips (pun intended) that relocating to the U.S. would placate U.S. regulatory bodies and ingratiate itself with a protectionist American president who is averse to foreign competition. Furthermore, if Broadcom were classified as an American company, it could avoid the jurisdiction of the Committee on Foreign Investment in the United States (CFIS). CFIS has broad powers to review the national security implications of foreign investment in domestic companies.

Ultimately, the political maneuvering was all for naught as an executive order shut down the possibility of a merger on national security grounds. However, Broadcom has indicated that it still plans to redomicile to the U.S. from Singapore by April 2018; a move that could be advantageous in its hunt for future domestic acquisitions.

Broadcom’s Acquisition Mentality Starts at the Top

Broadcom’s CEO Hock Tan immigrated to the United States from Malaysia and eventually became a naturalized citizen in 1990. He earned both bachelor’s and master’s degrees in mechanical engineering from MIT and subsequently furthered his education with an MBA from Harvard’s much vaunted Business School.

It was in 2006 that he became the CEO of Broadcom’s predecessor Avago Technologies Ltd. which itself began as a division of Hewlett Packard. At the helm of Avago, Hock Tan has demonstrated remarkable expertise in deal making and value creation via acquisition. Mr. Tan (for better or worse) brings a private equity mentality to acquired companies. He targets businesses with high profit margins and then either significantly cuts or sells off the less successful parts of the business. Broadcom maintains a focus on its main customer bases of handset makers and data-center operators.

After bringing chip makers LSI and Emulex into the Avago portfolio, Tan’s crown jewel acquisition came about in 2015 when his company purchased the original Broadcom Corporation for $37 billion and renamed the merged company to Broadcom Limited. At the time of the merger, Broadcom Corporation’s revenues were twice the size of Avago’s.

“‘He ran through Broadcom with a machete,’ says Stacy Rasgon of Bernstein Research. According to Linley Gwennap of the Linley Group, a consultancy focused on semiconductors, Mr. Tan eliminated an entire layer of management at Broadcom and now has around 20 business units reporting directly to him.” – The Economist, Nov 9th 2017

Horizontal Integration Doesn’t Work, Until it Does

Although horizontal integration is frequently touted by firms as a way to create “synergies” and improve strategic positioning in their industries, I learned from noted Georgia Tech strategy Professor Frank Rothaermel that mergers and acquisitions, on average, destroy rather than create shareholder value as the anticipated synergies never materialize (see AOL-Time Warner, Daimler-Chrysler, HP-Compaq, etc). This happens due to the blending of disparate corporate cultures, high management turnover and a tendency to underestimate potential merger issues. However, if a firm can continue to successfully merge, acquire and integrate to its benefit, this can serve as a source of competitive advantage.

“Although there is strong evidence that mergers and acquisitions, on average, destroy rather than create shareholder value, it does not exclude the possibility that some firms are consistently able to identify, acquire, and integrate target companies to strengthen their competitive positions. Since it is valuable, rare, and difficult to imitate, a superior acquisition and integration capability, together with past experience, can lead to competitive advantage.” – Frank T. Rothaermel

With Hock Tan at the helm, Broadcom has flaunted a stellar acquisition and horizontal integration capability to become the world’s fifth largest semiconductor company. The company’s revenues continue to climb as a result of its deal making proficiencies. Even now Broadcom is seeking approval for a $5.9 billion acquisition of data and storage networking products company Brocade.

Broadcomm Revenues

In an apparent Russian nesting doll scenario, the Wall Street Journal reported that industry heavyweight Intel would have considered an offer for Broadcom if the Broadcom-Qualcomm merger was greenlit. Intel would have been trying to ingest Broadcom, which had tried to merge with Qualcomm, which is currently trying to acquire Dutch semiconductor company NXP.

The semiconductor industry continues to move towards an oligopolistic industry structure as firms look to lower their costs by gaining scale. Scale helps chip companies manage the capital intensive nature of their industry.

Research and Development: Show Me the Money

Broadcom and Qualcomm are similar in one respect; they are both fabless (i.e. fabrication-less) chip companies. They design and market processors while relying on an outsourced manufacturer for production. Despite never owning fabs (like Intel & Samsung), they both have become industry leaders. Outsourcing manufacturing allows firms to invest more capital into research and development and subsequent new products.

“In the absence of manufacturing differentiation, semiconductor players that design the most functionality and performance into their products in the shortest amount of time wield distinct competitive advantage. That puts product-development productivity at center stage.” – McKinsey&Company

Where Qualcomm differentiates itself from its peers is in the relative amount of dollars it invests in research and development. Aside from Intel, Qualcomm spends more on R&D than any other chipmaker ($5.15 billion in fiscal year 2016 and $40 billion over the past decade). To put these numbers in perspective, Qualcomm spends more on research and development than both Apple and Amazon.

This focus on research and development is in sharp contrast to Broadcom which operates with a private equity style philosophy that prioritizes cutting both jobs and research after acquisitions to boost short term profits. In theory, less money allocated for research and development leads to weaker products in the future which leads to a loss of competitive advantage. Broadcom tried to counter this criticism by announcing that they would increase Qualcomm’s research and development budget and create a $1.5 billion fund to train American engineers.

Qualcomm Makes Money Off of Every iPhone Sold! (In Theory)

Although Qualcomm may not be a household name, If you own a mobile phone, then most likely you are using Qualcomm’s patented technology. The company’s innovation bona fides were established by helping to bring about mobile standards like LTE. It is also the United States’ champion in the coming 5G revolution which is why it garnered so much political protection.

Qualcomm is a leader in processors that manage cellular communications in smartphones. It competes with Broadcom in the manufacturing of baseband processors that manage a device’s wireless connections. A combined Broadcom-Qualcomm with more market power in Wifi and baseband chips would not make buyers comfortable (see Porter’s Five Forces: Bargaining Power of Suppliers).

In accordance with Qualcomm’s high R&D spending, it owns a veritable trove of patents that are deemed essential to building cellular phones that adhere to wireless standards. The thousands of patents that the company owns allows it to engage in a unique business model that accounts for two thirds of its operating profits. Qualcomm charges device makers (e.g. Apple, its biggest customer) for use of its patents, it does not issue licenses on its patents to rival chipmakers.

These (device-makers) usually pay for the entire patent portfolio, rather than individual patents. And Qualcomm typically charges a percentage of the total selling price of a device—5%, according to insiders. – The Economist, Jan 28th, 2018

In case you missed that, Qualcomm makes money off of the total price of every iPhone sale.

Apple, among other things, claims that per-device royalties mean Qualcomm is taxing its innovation: it must pay up for new features, such as a new kind of camera, even if these are unrelated to Qualcomm’s patents. – The Economist, Jan 28th, 2018

Qualcomm’s Woes

Even though the takeover threat from Broadcom has dissipated, there are various storm clouds hanging over Qualcomm. As would be expected, Qualcomm is engaged in a protracted legal battle with Apple in regards to its business practices. As a result, Apple has withheld billions of dollars in royalty payments that have put downward pressure on Qualcomm’s stock price.

“Qualcomm’s 2019 forecast includes between $2.5 billion and $4 billion in payments it assumes would flow from resolving its customer disputes. It leaves out a further $5 billion to $7 billion in so-called catch-up payments that Qualcomm has said Apple and Huawei will owe by then for royalties they have withheld.” – Wall Street Journal, March 19, 2018

Qualcomm’s loss could be Intel’s gain as Apple has switched over to Intel chips for some of its devices and could completely cut Qualcomm out of the next cycle of iPhones. This scenario would play in Intel’s favor as it looks to diversify away from the PC market which has been in decline.

Qualcomm is also subjected to charges brought against it by the Federal Trade Commission for abusing its monopoly power on certain chips. Adding to its woes, Qualcomm is dealing with additional anti-trust reviews in Europe and Asia as a result of its patent and royalty-centric business model. The European Commission fined the company for $1.24 billion for abusing its position in regards to LTE baseband chips

China fined Qualcomm $975 million (the largest in China’s corporate history) and also forced the company to lower its patent royalty rates in 2015. Since roughly 65% of Qualcomm’s revenues originate from China and Hong Kong, the company had no choice but to comply.

As it fought off being acquired, Qualcomm was also playing the role of acquirer. The company is trying to complete a $47 billion dollar acquisition of Dutch semiconductor company NXP. On paper, the acquisition would bolster Qualcomm’s portfolio of intellectual property in chips for automotive and IoT devices. The deal was announced in 2016 but is currently held up in anti-trust review in China. The United States’ stifling of the Broadcom deal and current trade war tensions with China have not helped Qualcomm in gaining Chinese approval.

Last but not least, boardroom drama surfaced as Qualcomm recently removed its Chief Executive (scion of the company’s founder) from the board after he made public his desire to bid for the company.

Why the Deal Fell Through

Acquiring Qualcomm would have greatly expanded Broadcom’s position in smartphone and cellular communications chips. However, there is a technology arms race occurring between the United States and China over the future of 5G, and Qualcomm is the designated American champion in this race. America has held its own in 4G innovation but has fallen behind Asian countries in the application of 5G. 5G is touted as the fifth generation of mobile networking technology that will power a new wave of innovative services and applications; think self-driving cars and IoT devices. Although currently there is no defined standard for 5G, it promises to be a faster communications medium than current 4G technology.

The U.S. government and other Western industry CEOs fear that if China gains the lead in 5G technology, then China will gain a technology edge in the next wave of products that will take advantage of the faster speeds and interconnectivity; cyber-espionage tools included.

“China, under President Xi Jinping, has launched an ambitious plan to dominate mobile technology, supercomputers, artificial intelligence and other cutting-edge industries, putting huge resources behind an effort that it considers crucial to the country’s government, military and economy. Beijing wants to build its own technology champions and is encouraging companies to acquire the engineering, expertise and intellectual property from big rivals in the United States and elsewhere.” – New York Times. March 6, 2018

Although Broadcom has stated that it wouldn’t sell “any critical national security assets to any foreign companies”, its private equity reputation of cutting research and development to boost profitability caused more harm than good. The bottom line is that a takeover of an American firm by a perceived foreign competitor with a reputation for cutting research and development, risks strengthening Chinese competitors like Huawei and Xiaomi. The perceived political and national security ramifications of this scenario were deemed unacceptable.

It is for this reason that the biggest merger in the history of technology never happened.

References:

CNBC. March 1, 2018. Beware, Qualcomm: Broadcom is used to winning battles with hostility, as Amazon knows. https://www.cnbc.com/2018/03/01/broadcom-ceo-hock-tan-aggressive-negotiations-with-amazon-hpe-others.html

The Economist. Nov 9th 2017. Broadcom’s $130bn Qualcomm bid highlights a ruthless chip industry. https://www.economist.com/news/business/21731121-worlds-biggest-ever-technology-deal-would-face-antitrust-scrutiny-globally-broadcoms-130bn

The Economist. Jan 28th, 2017. Qualcomm is again under attack for living large off its patent portfolio. Its biggest customer, Apple, is suing it for $1bn. https://www.economist.com/news/business/21715705-its-biggest-customer-apple-suing-it-1bn-qualcomm-again-under-attack-living

McKinsey&Company. McKinsey on Semiconductors. Autumn 2013. https://www.mckinsey.com/client_service/semiconductors/~/media/32ae520663114c6eb1250bbdb92673c2.ashx

The Motley Fool. Feb 21,2017. Here’s Why Qualcomm, Inc.’s Research and Development Spending Dropped in 2016. https://www.fool.com/investing/2017/02/21/heres-why-qualcomm-incs-research-and-development-s.aspx

New York Times. March 6, 2018. The New U.S.-China Rivalry: A Technology Race. https://www.nytimes.com/2018/03/06/business/us-china-trade-technology-deals.html

New York Times. March 7, 2018. Broadcom’s Other Regulatory Hurdle: How It Treats Customers. https://www.nytimes.com/2018/03/07/technology/broadcom-qualcomm-customers.html

Rothaermel, Frank T. 2015. Strategic Management 2nd Edition. New York: McGrawHill, Irwin (2nd edition).

Wall Street Journal. March 6, 2018. Qualcomm’s Spending Buys the Right Friends. https://www.wsj.com/articles/qualcomms-spending-buys-the-right-friends-1520366524?tesla=y

Wall Street Journal. March 9th, 2018. Intel May Intervene in Broadcom’s Effort to Buy Qualcomm. https://www.wsj.com/articles/intel-considers-possible-bid-for-broadcom-1520633986

Wall Street Journal. March 19, 2018. Qualcomm Evaded Broadcom’s Bid; Now, CEO Has a Lot to Prove. Fending off Broadcom still leaves the chip giant with patent disputes and a takeover fight from within. https://www.wsj.com/articles/qualcomm-ceo-steve-mollenkopf-faces-fights-on-many-fronts-1521457200

Copyright: gmast3r / 123RF Stock Photo

Penetration Testing: The Legal Way to Hack

The penetration test is the activity in which a security vendor or white hat hacker will deploy their skills acquired from training, certification and practical experience. The aim of the penetration test is to discover system or network vulnerabilities and exploit those vulnerabilities with the consent of the system owner(s). The penetration test scans for vulnerabilities and looks to actively exploit any uncovered vulnerabilities; it is a complement to the vulnerability scanners used during a vulnerability assessment. The penetration test helps identify which vulnerabilities are real and discern whether they can actually be exploited. “Vulnerability scanners can tell what potential risks are, but pen tests can provide the actual facts about the risks, including if they are exploitable and what information could be exploited if they were” (Howarth, 2010a, para. 2).

There are many different flavors of pen testing. A manual or automated test may be executed. The manual test is more involved and typically more costly if an outside authority is used as it requires significantly more expertise than an automated test. The automated testing approach is carried out via the logic, rules and or AI embedded in a software product. One such commercial product on the market is SAINTexploit, which not only exposes vulnerability points but also exploits those vulnerabilities to prove their existence. SC Magazine for security professionals rates SAINTexploit as an overall 4.75/5 star product for automated penetration testing. The annual cost of the product is $8,745 for 1,000 unique targets; the product must be renewed annually for continued usage (Stephenson, P., 2013). “Automated tools can provide a lot of genuinely good information, but are also susceptible to false positives and false negatives, and they don’t necessarily care what your agreed-upon scope says is your stopping point” (Walker, M., 2013, Chapter 11).

The two types of penetration testing as defined by the EC-Council (the certification body for the Certified Ethical Hacker designation) are external and internal. External assessments test and analyze publicly available information, as well as conduct scanning and exploits from outside the network perimeter. The internal assessment is the opposite and is performed from within the network perimeter.

The concept of black, white and grey box testing also come into play with respect to determining what information is known beforehand in order to carry out the penetration test. Walker (2013) notes that in a black box test the attacker has no information of the system or infrastructure beforehand. The black box test requires the longest to accomplish and is the closest simulation to an actual attack. White box testing simulates an insider with complete knowledge of the systems and infrastructure, who carries out the penetration test. Finally the grey box test provides limited information on the targeted systems and/or infrastructure.

Another parameter that can make the pen test more closely resemble real world conditions is the incorporation of social engineering. The white hat is given permission to use phishing attacks in order to gain access to passwords or other sensitive information. With phishing, the ethical hacker can design any number of email messages, websites, or even utilize phone calls under false pretenses in order to get a user to install malicious software or hand over sensitive information. The organization can gauge the results of these controlled social engineering attacks to see which users need a refresher in the company security policy or to determine if the current security policy is effective.

An organization carrying out an external penetration test by using an outside company should have the scope and the rules of the test clearly defined in contractual or service level agreement terms. In the event of a disruption of service or any other catastrophic event, both parties should know ahead the responsible party for correcting any issues. Graves (2010, Chapter 15) asserts that the documents necessary to have signed from the client before conducting a white hat a penetration test are:

  • “Scope of work, to identify what is to be tested”
  • “Nondisclosure agreement, in case the tester sees confidential information”
  • “Liability release, releasing the ethical hacker from any actions or disruption of service caused by the pen test”

Although penetration testing is widely used by organizations to test for system, network or human vulnerabilities there are some limitations to their effectiveness. All of the potential varying client parameters around the pen test (e.g. financial systems are out of scope, no social engineering, etc..) can work to hide exploits that would still be vulnerable to an actual black hat attack. Real world attacks can use a combination of social engineering, physical, and electronic methods often coordinated by an experienced team. The aforementioned combination of methods and expertise is very hard to simulate in a controlled environment. “The [enterprise’s] board and other stakeholders will not care about a clean network pen test if an attacker enters the building and, through a combination of social engineering and other low-tech gadgets like the hidden camera tie, steals your protected information” (Barr, J., 2012b).

References:

Barr, J. G. (a) (November 2012). Recruiting Cyber Security Professionals. Faulkner Information Services. Retrieved March 23, 2013

Graves, K. CEH—Certified Ethical Hacker—Study Guide. Sybex. © 2010. Books24x7. Retrieved March 24, 2013

Howarth.F. (2010). (a) Emerging Hacker Attacks. Faulkner Information Services. Retrieved April 17th, 2013

Stephenson, P. (2013). SAINTmanager/SAINTscanner/SAINTexploit v7.14 Retrieved March 23, 2013 from http://www.scmagazine.com/saintmanagersaintscannersaintexploit-v714/review/3797/

Walker, M. CEH Certified Ethical Hacker: All-in-One Exam Guide. McGraw-Hill/Osborne, © 2012. Books24x7. Retrieved Mar. 24, 2013

Create a Hex Map in Tableau the Easy Way

There are may different ways to create a hex map in Tableau. The hex map helps visualize state geographic data at the same size which helps to overcome discrepancies that make smaller states harder to interpret. Also, larger states (e.g. Alaska) can overwhelm a traditional map with their size.

I’ve found that the quickest and easiest way to build a hex map is to leverage a pre-built shape file. Shape files can be found at various open data sources like census.gov or data.gov.

In this video I will use a shape file created by Tableau Zen Master Joshua Milligan who runs the blog vizpainter.com. He has a blog post where you can download the shape file I reference. Hats off to Joshua for creating and sharing this great shape file!