This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.
The Introduction of Data Warehousing at Tronics PLC.
Businesses today tend to reach out to more customers andsuppliers in different places through information technology. Diversity of dataand information of application systems in the 1990s have made it difficult forcompanies to maximize their organisational information for a more useful formof analysis. This diversity comes in the form of data coming from differentsources, from different platforms, different formats, different definitions,and for different purposes in the entire enterprise. Coupled with anincreasing massive volume of data, information and business strategies areslowly getting complicated for an ordinary user or a business analyst to makeanalysis in the context of their business' competitive advantage.
The rapid expansion and improvements in getting the required outputs from an online transaction processor environment of an enterprise has also contributed to the need to create a standard repository for all company data for the sole purpose of making decision support (DSS) analysis. Whatever strategic advantages that the company can derive from using a data warehouse depend on their ability to make strategic arrangements and innovate the processes that comprise business functions.
This should be accomplished in such a way that information in the context of business analysis will not only serve to fulfill simple interactive analysis and information retrieval but also on a more complex, strategic, and deeper level of accomplishing them.
What is a Data Warehouse?
The data warehouse is the foundation of decision supportsystem processing. There are different characteristics of a data warehouse thattend to support such processing. A data warehouse is subject-oriented,integrated, non-volatile, and time variant. The databases of a data warehouseare designed specifically to support the analysis needs of management.Normally, the data stored in a data warehouse are organised differently thanthat of operational databases (Lorens and Morgan, 1998, p. 502).
General Benefits of Data Warehousing
The different operational systems support differentbusiness functions and processes within the enterprise. They are needed to runand simplify the different day-to-day core functions and create an environmentfor faster processing of transactions online. In addition to thesemission-critical contributions of operational systems, they also aid in theperformance of querying or digging information purely on a more controlled andsubtle level. This level cannot provide strategic information for executivesand end-users in advancing the business cause of the enterprise.
A data warehouse is different for a number of reasons.It serves a different purpose, it has a different design approach, itencompasses a wider scope of the enterprise, and it has to adapt to aconstantly changing business environment. In addition to these, the building ofa data warehouse involves a thorough consideration of a lot more issues ascompared to operational systems' projects.
Ponniah (2001) differentiates an operational system witha data warehouse system by the need to use strategic information for decisionmaking. He emphasized the need to build a new system environment for thepurpose of providing strategic information for analysis, discerning trends, andmonitoring performance. The complexity of building a data warehouse varies fromdifferent companies and enterprises. Some might find it more complex to collectand transform all source data into data warehouse data because of the waydifferent data are formatted and stored in different platforms.
Some might find it also difficult to design a company-wide data warehouse because of the location of company branches and stores. Others will also find it complicated to build the data warehouse based on how information delivery mechanisms will be implemented to end-users, suppliers, executives, and other business analysts.
Significant trends like fierce competition, governmentderegulation policies, need to revamp internal processes, and the need forcustomized marketing are some of the factors that are influencing companies tomove into data warehousing (Ponniah, 2001). Strategic information, whenproperly used, can be a very powerful tool in adapting a business to meet thesetrends.
Benefits of Building a Data Warehouse at Tronics PLC
When we are talking about customized marketing, we areactually talking about adapting the business to current market trends byreaching out to clients through the delivery of strategic information to them.
Tronics PLC, a leading electronics company, has hundredsof sites throughout Great Britain. It sells electronic products to smallbusinesses through its trade warehouses. These products include telephones,audiovisual equipments, cameras, and other electronics accessories.
Users of the data warehouse of Tronics PLC can come fromdifferent places, different levels of the organisation, different businesspartners and suppliers, and from selected analysts. At the current trend ofbusiness at Tronics, many of the potential users are constantly moving aboutfrom place to place, a distributed form of information delivery approach isneeded to be able to make this possible.
In order to get a bigger picture of how the company iscurrently doing, a comprehensive set of queries at the company-wide datawarehouse can be made by different users coming from different places.Information can be gathered at the different branches, from differentcustomers, and from different suppliers. At the same time, customers, suppliers,and branch officers can perform their own set of business analysis by usingaggregated data for the entire company to make business decisions andstrategies even without the need to consult Tronics PLC management about it.
Suppliers can work with Tronics on improving demand planning and supply chain management. The executives from the main company and its different branches can cooperate on planning between different sales strategies. Customers can make quick purchasing decisions.
This multi-user support strategy makes it possible forall of those involved in the business to make strategic recommendations andformulate their own initiatives in dealing with the company. Under this setup,Tronics can increase the productivity of all the members in the company's valuechain. It will also elevate the roles of knowledge management and technologymore as strategic tools to share each member's corporate knowledge even if theyare strategically located in different locations. This might make them moreeffective and productive in their work. This sharing of information would setthe tone for a new level of cooperation and partnership that would go beyondthe traditional way of engaging in business.
Data Issues and the ETL Process at Tronics PLC
The data extraction, transformation, and loadingprocesses done in the data warehouse development is the most time consuming andhuman-intensive part of the entire data warehouse design process (Ponniah,2001).
Data extraction involves identifying possible sources ofdata for the data warehouse, determining the compatibility of external sourceswith the internal data sources in the data warehouse, and the methods of dataextractions.
Data transformation involves the use of different datatransformation functions. These functions include input selections,normalization and denormalization of source structures, aggregations,conversions, resolving of missing values, and conversion of names andaddresses.
The data loading process pertains to the strategiesinvolved on how to update the data warehouse data, how daily changes made inthe operational environment would be captured for the data warehouse data, andhow the loading of these updates will be initiated and carried out.
Before we can design the data warehouse for Tronics PLC,let us take a look at some of the factors, issues, and problems which mightdirectly affect the complexity of this process.
First, there are different branches located in differentcountries. Suppliers also come from different locations. If the company issupported by a large number of operational systems and applications from differentsuppliers, branches, and customers, data extraction would be quite challenging.Based on this scenario, there are different application systems running on differentcompany branches which may directly or indirectly support the core businessfunctions of the entire company. Other branch applications support internalbranch operations and not necessarily, the entire Tronics enterprise.
Other documents can be internal to a branch (like branch memos for example). The first process in the entire ETL process falls in this area. According to Lawyer and Chowdhury (2004), one of the special challenges in sourcing data for the data warehouse is what data to select and where is the data located. Some of these reside in legacy systems and others are stored in difficult to process formats.
Secondly, applications reside in different hardware andsoftware (and middleware) platforms. The same fields might have differentmeanings. In this case, data fields are used for operational systems purposesonly. The only level of compatibility resides in the way each field in thedatabase is named and defined the appropriate data type. The date field forexample (for SaleDate, PODate) has a text field length of 10 but this formatwould make it impossible for the data warehouse to group summaries of datatogether by hierarchies of date (for example, by day, by month, by quarter, andby year) when queries would be made unless some form of transformation is made.In the second process of the ETL process, the date field can be transformedinto a more specific date data type (maybe by using mm/dd/yy formatt) so thatwhen data (like sales for example) for a particular time element needs to besummarized and aggregated, it will bring out the correct results to theend-users.
Thirdly, how would data conversion take place?Currently, there is no uniform currency format for all fields related to sales.The fields NetSales, VAT, POCOST, and POVAT found in at least two tables have aUS dollar currency format while CostPrice and SellingPrice of the Product tableuses the British pound currency format. To be able to load these set of datainto the data warehouse repository, it must be transformed into a singlecurrency format so that it can be integrated to the data warehouse database. Thechallenges here lies on maintaining standards in the transformation processsuch that it will not violate any business rules and how to maintain or evenimprove data quality. Data quality is crucial in strategic decision-makingbecause any incorrect information can have ravaging and domino effects.
The fourth issue centers on the volatile nature of datavalues. Sometimes, these values are transient or transitory. As businesstransactions happen, the values change. The currency exchange rate varies fromtime to time. The cost of today's product orders may change during the time ofdelivery. A customer may change name and address today without being reflectedin the data warehouse repository because updates have not yet been made.Outstanding balances made from previous orders may dramatically increase overtime. There is no way to predict when these things can happen. The value of anattribute remains constant only until a business transaction changes it. One ofthe challenges in this particular issue is how to preserve the history of thesechanges in the data warehouse and how it would invariably affect the outcome ofany analysis.
Since one of the aspects of a data warehouse is that itis subject-oriented, how will data be grouped into subjects? The fifth issuedeals with how to organise data in the data warehouse in such a way that itwill be more subject-oriented, not application-oriented. Will the datawarehouse contain a subject named customers to include registered usersonly and how high can the level of detail be? Will the data warehouse stillconsider the lowest level of detail (down to the customer of the customerdetail)? Will the loading of nightly data into the data warehouse more responsiveto the immediate needs of end-users? Will it make a difference if loading ofdata to the warehouse be done on a weekly basis or a monthly basis?
Sometimes, it is also important to keep track of thedate when customers registered. Since a data warehouse is time variant, someform of querying and analysis always involve analysis made in relation to time.The tblCustomer and tblProduct does not contain any time or date elements andit would likely create problems when a certain user would want to know thetotal number of transactions made by a particular customer in a particular timeframe. Some analysis of this kind is needed maybe because management would liketo consider giving some form of incentives and discounts to clients as part oftheir business strategies. When one of the subjects in the data warehouse is customer,it is important that the customer must have some fields related to date or timeelements so that users can derive target values for input fields.
Web-Enabled Data Warehouse Implementation and OLAP
Since Tronics has different branches and its suppliersand customers are located in different places, the Tronics data warehouse,there has to be a good coordination among the different Tronics branches forthe development efforts of the corporate data warehouse. While Inmon (1996)disagrees with this theory because it would hinder the progress of thedevelopment of the data warehouse at the local level, the corporate data warehousemodel will reflect the business integration across the different branches. Thisapproach will also facilitate the opening of the entire Tronics data warehouseto the entire community of users in the value chain and perhaps, include generalconsumers who are not currently registered as customers.
The use of the web and other Internet services willchange the way information can be retrieved, analyzed, and shared. Using theweb as the primary information delivery mechanism will cut the cost ofdelivering information to geographically dispersed members of the value chain. Itwill help improve the business collaboration of Tronics and its businesspartners. The web as a tool for information dissemination means an increase inthe level of knowledge and awareness about the Tronics.
There are ways of developing a web-enabled datawarehouse. The data warehouse can be opened to the public through the Internet.Data warehouse access can also be limited to internal company users in the formof an intranet. The intranet is a private and secure computer network infrastructurethat limits outside access through firewalls and other forms of networksecurity. An extranet is an intranet that is open for access to all internalusers and open for selective access for outside parties.
The best choice for a web-enabled Tronics data warehouseis to adopt an extranet web information delivery strategy. If the datawarehouse is to serve one of its purposes of serving the needs of outsidemembers, it should give access to company information to selected users who cando so through proper authentication channels.
In addition to what was previously mentioned about thevalue of the web in data warehousing, a web-enabled data warehouse can have theother following advantages:
1. Suppliers, Customers, and Tronicstop level management will have a single point of entry and reference forinformation thus eliminating the overlapping of information.
2. There will be minimal trainingfor web users since the use of the web is already a part of our everyday lives.
3. Web browsers will run on anycomputer systems.
4. The web will enable users toreceive multiple data formats like text, video, charts, images, and audio.Tronics suppliers can be shown a graphical representation of products which arecommonly purchased.
5. The sharing of informationthrough the web will foster, strengthen and deepen collaborations andpartnerships.
6. It will allow the sharing ofinformation on an unlimited time basis.
7. The web will add another type ofcrucial data needed for decision support analysis. This is called clickstreamdata. Clickstream data tracks how users navigated through the Tronics website and what sections or areas usually attract users the most.
8. The web can handle OLAP (onlineanalytic processing).
Online Analytical Processing (OLAP) Technology at Tronics PLC
The different fields of a particular database areinterrelated to different business dimensions. If we try to take a look at the fieldNetSales of the table named tblSOLINE, we will notice that it isdirectly related to product id (or the product itself!). The POCost isrelated to the PODate (through the POHeaderID).
Most analysts dig through data through scenarios. Intoday's analytical processing, a query like how many units of Headsets did wesell in Bradford is likely to satisfy an ordinary user but it probably won'tsatisfy a manager who wants to find out more about the trends of sales for thelast 3 years in individual branches. In order to maximize the value of dataanalysis at this point, the user usually performs a multiple set of queries sothat he or she may be able to get what is needed.
Time is usually a critical dimension of analyticalprocessing. Hardly is any query performed without any time dimension to it.Aside from time dimensions, there are also business dimensions like sales whichcan be related to time dimensions in performing queries. Top management usuallymonitors performances over time and use this as a form or basis of comparison.
The online analytical processing is a category ofsoftware technology that enables users to have multidimensional views of data. Inother words, OLAP is another term for multidimensional analysis.
Table 1 presents some of the initial results of a querystarting from a high level summary of Product Names (Kimball, 1996). Next, theuser moves to drilling down for details by three months. In this query, therewill still be the same product names for the first column followed by anotherset of columns comprising the three months of analysis (January, February, andMarch). Another column would contain the row total per product name. In thenext step, the user rotates the columns to rows in such a way that the firstcolumn contains the Months, followed by the different product names.
Sum Of NetSale
Telephone (Hands Free)
Table 1: Results of the high level query
The series of queries presented illustrates the power ofOLAP. If we are to present the results of another set of queries by depictingthe three dimensional aspect of analysis. We could get the total netsales alongthe hierarchies of a combination of the three business dimensions identifiedfor the Tronics data warehouse.
Display the total sales of all products for the pastthree months in all branches.
Display of Results
Rows: Month numbers or names (January, February,March)
Columns: Total Sales of allproducts
Page: One Branch per page
Show comparison of total sales for all branches, byproduct, between months January to march only for those products with increasedsales.
Display of Results
Rows: Month numbers of names (January, March); differences;amount of increase
Columns: One column per product, showing only thequalifying products
Page: All stores
This kind of analysis can continue until it satisfiesthe needs of the end user or analyst. It uses these three dimensions namely: rows,columns, and pages as the components of the analysis.
Data warehousing allows companies to tread on a newcomputing environment for their competitive advantage. Information has a newmeaning in the context of making sound business judgments and analysis. Theuse of operational application systems serve to fulfill immediate informationneeds of Tronics users. It does not have the power to leverage data andinformation for a more complex and strategic business analysis. Tronics PLC canleverage the information already at its disposal for a more productive andstrategic purpose especially with the current geographical diversity of itsvalue chain members.
The introduction of web-enabled data warehousing at Tronics will enable the company to delivery strategic information to business partners and clients so that there could be better interactive collaboration through the sharing of individual business analysis. It will enable all those involved in helping Tronics attain its current status to be a part of the company on a new level of cooperation and participation. This can be made possible through a carefully thought and well researched transition to a new information processing environment in the form of a data warehouse. A data warehouse is an information delivery system. It is not about technology but about using data and technology to solve problems related to using information for major business strategies and innovations.
The success or failure of a data warehouse depends on how it would be able to use information for establishing a new set of enterprise management strategies involving not only to internal and selected users but to other members of an enterprise's value chain.
Inmon, W(1996). Building the Data Warehouse. 2nd edition. John Wiley.Canada
Kimball, Ralph(1996). The Data Warehouse Toolkit: Practical Techniques for BuildingDimensional Data Warehouses. John Wiley.
Lorents, Aldenand Morgan, James, Database Systems: Concepts, Management, and Applications. Dryden Press.
Ponniah,Paulraj (2001). Data Warehousing Fundamentals. John Wiley. Canada
Jeff Lawyer andShamsul Chowdhury, Best Practices in Data Warehousing to Support Business Needs and Initiatives, Retrieved March 4, 2005from http://www.csdl.computer.org