B.I. Basics: Create an SSIS Data Profiling Task In SQL Server

Data Profiling is necessary when trying to gain an understanding of a given data set. A data profiling assessment should begin before any reporting or application development work begins. My video will demonstrate how to create a basic SSIS Data Profiling Task using SQL Server Data Tools.

According to the DAMA Guide to the Data Management Body of Knowledge:

“Before making any improvements to data, one must be able to distinguish between good and bad data…. A data analyst may not necessarily be able to pinpoint all instances of flawed data. However, the ability to document situations where data values look like they do not belong provides a means to communicate these instances with subject matter experts, whose business knowledge can confirm the existences of data problems.”

Here is additional information direct from Bill Gates’s former startup outfit regarding the types of data profiling tasks available in SSIS: https://msdn.microsoft.com/en-us/library/bb895263.aspx

If you’re interested in Business Intelligence & Tableau please subscribe and check out my videos either here on this site or on my Youtube channel.

More Than You Want to Know About Wal-Mart’s Technology Strategy Part 3

This article is the final piece and a continuation of my earlier analyses (Part 1, Part 2) where I waded into Wal-Mart’s strategy for information risk & security, stakeholder requirements and project ROI. Whether you love or hate Wal-Mart, no one can argue that historically the organization has been highly innovative, effective and efficient. In this third part of my three part series I will broach the company’s strategy for data acquisition, social media, and project execution.

Wal-Mart: Strategy for Data Acquisition and Impact on Business Processes:

Wal-Mart has always been on the forefront of how an organization acquires, handles and shares data with internal and external parties. The company’s information technology spans across their 11,500 stores operating under 63 banners in 28 countries and e-Commerce websites in 11 countries (Wal-Mart Stores, Inc, 2015). This diverse assortment of digital and traditional brick and mortar assets services 260 million customers. The company’s sprawling POS system must remain highly operable and robust enough to harvest daily sales data across its global footprint. In order to accommodate data from a substantial customer base of millions of shoppers, the company must have the requisite back end storage infrastructure. As of 2015, Wal-Mart is embarking on a plan to build a massive private cloud which is expected to make 40 petabytes of data available everyday (Buvat et al., 2015). From a strategic standpoint the in-house cloud build-out makes sense, as employing Amazon Web Services would be making the company reliant on a key competitor’s technology offering to house sensitive data.

The data acquired from its POS systems allows the company to better allocate its product mix in real time. For example, on “Black Friday”, which is typically the busiest shopping day of the year, the company’s buyers mine the day’s sales data as early as 6am on the East Coast in order to optimize the company’s product offerings for the day (Sullivan, 2004).

From a data acquisition perspective, Wal-Mart pioneered the use of bar code scanners. The usage of scanners and Universal Product Codes was not just to register the price and make the cashier’s job of processing customers faster (as its early competitors had used the technology); Wal-Mart realized that bar codes enabled the company to determine where and how sales were made. In order to link POS, inventory and supply chain management data together with headquarters, the company invested in its own personal satellite system in 1984 (Sherman, 2013). This investment impacted in-store business processes as analytics could be run against purchase data to determine customer market basket mix and product to product correlations. Being on the forefront of purchasing data analytics allowed the company to more effectively place products in-stores that customers demanded.

This information linkage enabled by Wal-Mart’s satellite investment also led to profound business practice impacts with respect to its supply chain management process. As mentioned earlier in this analysis, with the development of Retail Link, Wal-Mart led the industry by providing first P&G and then the rest of its supplier network with direct visibility to real time store-shelf data. This was a highly innovative move as suppliers and internal buyers could work together on “forecasting, planning, producing, and shipping products as needed” (Sherman 2013). Enabled by its new technology system, the data sharing of information between supplier partners allowed those suppliers to manage inventory at Wal-Mart stores with a more comprehensive understanding of product demand, while Wal-Mart benefited from lower inventory storage costs.

Wal-Mart: Strategy for Social Media/Web Presence:

Wal-Mart operates e-commerce web presences across 11 different countries. Although it is by far the biggest brick and mortar retailer in the world, it has struggled to compete with online “e-tail”, competitors such as Amazon and Target. Updating its digital know-how and refreshing its digital properties will be a requirement in order to keep pace in a shifting industry dynamic. To this end, Wal-Mart has embarked upon a number of strategies to keep itself relevant and enhance its digital capabilities. The company is leveraging its web properties to not only analyze purchasing behavior but also review customer search histories and customer social media interactions. The latter activities are aimed at boosting its online sales and predicting customer demand for its brick and mortar locations.

“Teams at WalmartLabs use visualization techniques to analyze social activity to capture insights that may indicate changes in product demand. Walmart can then use these insights to stock extra inventory at locations where it expects higher demand and reduce it from locations with lower demand” (Buvat et al., 2015).

Wal-Mart has also embarked upon a strategy of purchasing startup companies with the intention of adding to the retailer’s knowledge in the mobile, analytics and social media realm. The retailer purchased a company called Kosmix, whose founders had sold a digital company to Amazon in the late 1990’s. Kosmix is a social media data aggregator which analyzes data from Twitter and other social networks with the aim of helping the company understand the relationship between customers and products. Shortly after the Kosmix acquisition, a small team at @WalmartLabs prototyped a new search capability code-named “Polaris”. Polaris helps to determines customer intent when embedded within Walmart.com. “As a result, if a user types in the word ‘denim’, it returns results on ‘jeans’ while ‘chlorine tablets’ returns results related to pool equipment (Ribiero, 2012). Within 9 months the prototype was production ready as it replaced an Oracle based product (Endeca) whose search functionality was simply keyword based. The company claims that it sees a 10% – 15% boost in shoppers completing a purchase using the Polaris search algorithm.

One recent splashy acquisition in the e-tail space involved Wal-Mart’s purchase of Jet.com for 3 billion in cash. Wal-Mart realizes that customer preferences have shifted to the online retailer space while Wal-Mart has a substantial legacy footprint in brick and mortar locations. According to the Wall Street Journal, “The retailer gains access to a larger group of young, wealthy, urban shoppers through Jet” (Nassau, 2016). The company also gains access to a startup minded employee talent base, startup executive experience and Jet.com’s proprietary pricing software and customer data.

Additional social project activities employed by Wal-Mart include adding a ratings review capability to its products on Walmart.com and a partnership with Facebook to offer individual pages for each of the retailer’s 3,500 stores. The company has also used the hashtag #lovedata to appeal to potential technology hires.

From an employee social engagement perspective, Wal-Mart developed an internal web site called mywalmart.com with the aim of developing an employee social media community where employees are allowed to blog and answer questions related to the company. Roughly 75% of the company’s 1.4 million U.S. based associates log on to the site (Tuttle, 2010). This effort required integrating multiple disparate websites running on different web platforms (for example payroll and benefits sites).

It should be noted that although the company is taking positive steps in the social media space, its initial attempts in the early 2000’s were clumsily executed. “Its Walmarting Across America blog in 2006, about two Wal-Mart enthusiasts traveling around the U.S. in an RV, was revealed to be less than authentic when it was learned that Wal-Mart paid for the flights, the RV and the gas of the two protagonists. ‘The Hub,’ a MySpace-like clone, closed in October 2006, just 10 weeks after it launched, while a Wal-Mart sponsored Facebook group reportedly had lackluster results” (Edelson & Karr, 2011).

Wal-Mart: Strategy for Organizational Change Management, Project Strategy and Complexity:

Best practice project management principles for handling complexity include implementing risk management practices and analyzing project risk/rewards. This series has already addressed Wal-Mart’s heavy reliance on ROI as a measure of project success. But Wal-Mart also has the advantage of running a common information systems platform for its global operations. This has allowed the company to offset the higher costs of developing in-house systems by building a system once and then rolling it out along with the respective business processes enterprise wide.

The company also lowers IT project complexity by closely examining the current process that the system is supposed to improve. This activity has been described by former CIO Kevin Turner as “eliminate before we automate”.

“Eliminate steps, processes, reports, keystrokes; eliminate any activity that you possibly can for two reasons: One, you’ll end up building a whole lot better system that’s easier to support, and two, invariably you will have a better solution that’s more [user] friendly” (Lundberg, 2002).

Once the system is built, then a piloting phase occurs amongst the stores, distribution centers and customers that will present the most challenges. If the challenges are caught and addressed by pilot builds in the most trying situations, then installing at less challenging locations should experience minimal interruptions.

From an Enterprise Risk Management standpoint Wal-Mart uses a 5 step process that allows its ERM group to work with the business to mitigate many of the risks that the company faces. “The five-step ERM process involves: 1. risk identification, 2. risk mitigation, 3. action planning, 4. performance metrics, and 5. shareholder value” (Atkinson, 2003).

An additional project strategy is to funnel all IT projects through the central IT office with a single enterprise-wide portfolio overseen directly by the CIO. King (2014) asserts that objectively ranking projects by using an automated spreadsheet tool helps to eliminates politically-driven decisions. Resources are assigned to projects based upon criticality and available funds. When available employees and funds are exhausted for the quarter, remaining projects on the list do not make the cut. Technology resources are asked to remain flexible as they rotate to different jobs within the company to gain additional skills. The flexibility of IT resources is a boon to change management plans as resources can be swapped out without minimal interruption to the overall project plan.

In case you missed something, make sure to revisit Part 1 & Part 2 of the series.

If you’re interested in Business Intelligence & Tableau check out my videos here: Anthony B. Smoak

References:

Atkinson, W. (December, 1, 2003). Enterprise Risk Management at Wal-Mart. Risk Management. Retrieved from Factiva.

Buvat, J., Khadikar, A., KVJ, S. (2015). Walmart: Where Digital Meets Physical. Capgemini Consulting. Retrieved from https://www.capgemini-consulting.com/walmart-where-digital-meets-physical

Edelson, S., & Karr, A. (April, 19, 2011). Wal-Mart to Buy Kosmix. Retrieved from Factiva.

King, R. (October 2014). Wal-Mart Becomes Agile But Finds Some Limits. Dow Jones Institutional News. Retrieved from Factiva

Lundberg. A. (July 1, 2002). Wal-Mart: IT Inside the World’s Biggest Company. CIO magazine. Retrieved from http://www.cio.com/article/2440726/it-organization/wal-mart–it-inside-the-world-s-biggest-company.html?page=2

Nassau, Sarah (2016). Wal-Mart to Acquire Jet.com for $3.3 Billion in Cash, Stock. Wall Street Journal http://www.wsj.com/articles/wal-mart-to-acquire-jet-com-for-3-3-billion-in-cash-stock-1470659763

Sherman, Richard J.. ( © 2013). Supply chain transformation: practical roadmap to best practice results. [Books24x7 version] Available from http://common.books24x7.com.libezproxy2.syr.edu/toc.aspx?bookid=49746.

Ribeiro, J. (August 31, 2012). Walmart rolls out semantic search engine, sees business boost. Retrieved from http://www.computerworld.com/article/2491897/internet/walmart-rolls-out-semantic-search-engine–sees-business-boost.html

Sullivan, L. (September 24, 2004). Wal-Mart’s Way: Heavyweight retailer looks inward to stay innovative in business technology. Retrieved 6/17/16 from http://www.informationweek.com/wal-marts-way/d/d-id/1027448?

Tuttle, D. (February, 17, 2010). Wal-Mart’s social media community earns accolades. Retrieved 6/17/16 from Factiva

WAL-MART STORES, INC. (January 31, 2016). FORM 10-K. Retrieved from https://www.sec.gov/Archives/edgar/data/104169/000010416915000011/wmtform10-kx13115.htm

Photo Copyright: dutourdumonde / 123RF Stock Photo