ETL – Data Wharehouse

Above is the common elements that make up a Data Warehouse. The data warehouse consists of several different elements, the source can come from legacy systems that are usually an operational system used by the corporation or external data sources, the data staging area where it is processed and moved to the presentation server where the data is organized and stored for future queries and reports. The last step is the end user data access point, currently one of the most popular forms to access data is through a web page.

The first step is extraction and involves obtaining the data from the source systems.

Next it is transforming the data using a series of steps to make the data usable for the data warehouse. The steps consists of cleaning the data to improve data quality and consistency, purging of any unnecessary data that is not required by the data warehouse, combining of similar data from different source systems and creating surrogate keys.

After the data is extracted, transformed and cleansed, it is ready to be loaded and indexed into the warehouse for fast querying.

Other steps include running a series of reports to ensure that the data stays consistent and that the quality stays intact. The data is released to users for generating reports and dashboards and is secured against unauthorized user access.

Main steps in the ETL cycle are:

  • Initiating the Cycle
  • Building of reference data
  • Extraction
  • Validation
  • Transforming
  • Staging (if staging tables are necessary)
  • Audit reports to verify that all business rules are kept
  • Publishing to warehouse tables
  • Archiving
  • Clean up.

Elements Used in a Data Warehouse

Many Business Intelligence solutions are based on the use of a data warehouse. Here is a view of the components of a data warehouse both logically and physically.  Data Warehouse Elements

The data warehouse consists of several different elements:

  • The source can come from legacy systems that are usually an operational system used by the corporation or external data sources,
  • The data staging area is where data is processed (normalized and some history is stored) and moved to the presentation server.
  • The presentation server takes the data, organizes it and stores is for future quires and reports
  • The last step is the end user data access point, currently one of the most popular forms to access data is through a web page and mobile applications.

You can download the visio drawing I created here –>data-warehouse-elements.vsd

Develope a Web Based CMS Using PHP

Download this File Here

The Content Management System (CMS) is a web based application using a Linux Server,
Apache Web-server, MySQL Database, and PHP Programming Language (LAMP). The
objective of managing users, and information in any given network environment can only be
hindered by the creativity of an information technology professional and not by technology. The
main objective of this thesis is to develop the early development steps of a LAMP software bundleCMS. By creating the
building blocks for developing, and taking into consideration basic methods for creating the core
platform of a CMS for further development. All information gathered, and experience gained will
assist with developing and offering my own personal e-commerce business solutions in the future
and to obtain additional business and practical knowledge in an open source software and ecommerce.
Continue reading

Sources of Data for Business intelligence

CassettesRaw data on its own is not very useful, it is just stacks of symbols, sounds, pictures, numbers or words. After data is collected the ability to connect the data, to give it relational meaning is when data becomes useful information. Information can answer questions of who, what, when and where. Once you see the relation in information you can start to see patterns in how the information changes through visual representations. Knowledge is gained from information when businesses can understand the changing patterns of information to answer questions on how items function in the real world; this is the main goal of business intelligence. Unless business information data compromises privacy or security, all data in every activity that can be observed and recorded will eventual become legally obtained business intelligence. Data acquisition helps in making informed business decisions by transforming information into graphs, charts, simulations or datasets to analyze trends and conclude on what businesses decisions should be made. Wither it is for a government or commercial business the optimal amount of data collection that is wanted is unlimited, because once you now everything about your target goal, you can begin to make the most accurate decisions.
Data Collection in Manufacturing
Managers want feedback from the manufacturing equipment they are in charge of, to monitor how machine processes are, how long the machine has been idle, and how many parts per shift have been produced. Equipment can be attached to machines that can monitor all aspects of physical change from several different points. Analog to digital converters can send the information to a management resource planner or process planner for real time updates on the total progress of a manufacturing plant. Events are monitored in real time for information that can help with corrective actions or adjust accountancy billing based on the amount of parts produced. Thousands of data points can be monitored each second for change on equipment, the several samples of variable changes are “critical to process inputs, even from multiple channels, as fast as possible. But there is more to it than that. Inputs must be processed and correlated so that the feedback loop can initiate changes earlier” (Varhol, 2006, para. 7).
Data entry from security entry ways within factories has also been tied into the billable hours for payroll and taxes, by using the same devices that scan your identification card to gain access to the company to also track when a employee logged in or out of work. So you know when personnel entered the building and when they started working to when they finished working and left the building. Their is several additional data points that that when entered and managed can help better manage personnel, production, billing and collections, sales management, customer care, marketing campaigns, supply chains, accounting, decision supports and any other business decisions. Information and data that is collected in a manufacturing company is obvious and usually serves an internal purpose. More and more companies are gleaning data that can be taken without people even realizing it and used for better advertisement.
Data for Online Marketing
More and more data every year is being amassed from companies over the internet. The use of smart phones has probably doubled or tripled the amount of data available for use in marketing campaigns. Websites, interactive applications, emails, and advertisements can incorporate cookies, pixel tags and web beacons to track individuals browsing behavior to better facilitate the effectiveness for advertising and search engine optimization. Online companies also try to track occupation, language, location, and unique device identifier of device when a product is used so that companies can better understand customer behavior and improve products, services, and advertising. Data gathered from Internet enabled devices also help to control the amount of times you see an Internet ad, to help display ads that are similar to your personal interests and help to monitor the effectiveness of an on-line ad campaign. The most recent data mining craze is storing your personal location using the global positioning system (GPS) and your phone.
Wireless Data Collection
Your location is just as important as all the other data previously discussed. Apple even changed there privacy policies in 2010 so the companies “iPhone, iPad and Mac computers collect location information, but do so anonymously in batches and encrypt it before sending the data over a WiFi connection from the devices to Apple’s servers every 12 hours (Apple, 2010, para. 1). Apple’s admitted that the main reason for wanting to track location of there customers is to provide location based services.
More and more governments are pursuing locational data. Companies like Chevron have been able save millions in freight costs with “more efficient routing that cuts the number of hours and miles the boats travel” (Feldman, 2010, para. 3). Companies can also use location based web services and geospatial information systems (GIS) to decide where the next office or store should be built based on actual driving times and traffic patterns. Other companies are using GPS, RFID, and Wi-Fi technologies to control shipments and other logistics in supply chain management, local government can report problems instantly of time sensitive situations like a down power line, government workers can use smart phones to geo-tag a picture and send it to the main office so the correct department can be notified to fix the problem. The United Parcel Service uses GPS to report were your package is during transit and GPS can be used for just socially updating friends were they are by using applications like Foursquare, a social driven location sharing application.
Data collection is being used more in businesses and governments every year. It has saved money in production and shipping because of the ability to monitor production and shipping in real time. Marketing can be used to target individuals on-line and remember who you are for your visit. We have only scratched the surface on how business intelligence will develop in the future. Even small business can take advantages of some of the technology that was to expensive to consider just a decade ago because of the price drop in devices like smart phones and other technological advancements.

Apple Tells Congressmen it Batches Encrypts Location Data 411968. July 20, 2010 pNAeWeek, p.NA. Retrieved March 17, 2011, from Computer Database via Gale:
Feldman, J. (Nov 1, 2010). [fusion_builder_container hundred_percent=”yes” overflow=”visible”][fusion_builder_row][fusion_builder_column type=”1_1″ background_position=”left top” background_color=”” border_size=”” border_color=”” border_style=”solid” spacing=”yes” background_image=”” background_repeat=”no-repeat” padding=”” margin_top=”0px” margin_bottom=”0px” class=”” id=”” animation_type=”” animation_speed=”0.3″ animation_direction=”left” hide_on_mobile=”no” center_content=”no” min_height=”none”][Location Data] Here And Now. InformationWeek, 1284. p.53. Retrieved March 17, 2011, from Computer Database via Gale:
Varhol, P. (June 26, 2006). Advanced control designs are drowning in data. Electronic Engineering Times, p.39. Retrieved March 17, 2011, from Computer Database via Gale: