Data Warehousing for SME

	Blog	INTRO	CRM	DATA WAREHOUSING	RESOURCES	CONTACT US

	Your source for Data Warehousing Design

Data Warehousing Tools
you do not need to spend huge amounts to benefit from data warehousing The Relational Database - Microsoft SQL Server 2005
At the heart of any data warehouse is relational database technology; without this technology there would be no data warehousing. There are many competing vendors, including specialist suppliers like Red Brick offering capabilities for massive data warehouses, but for SME customers we recommend Microsoft SQL Server (2005 on) which offers:-
Low Entry costs - there are open source database platforms available, but Microsoft offer a low entry cost compared to commercial rivals. Many SME businesses have already deployed SQL Server to host business systems, and this often allows use of SQL Server to host the data warehouse without further licence costs.
The Microsoft BI/DW toolset - SQL Server comes with a set of tools that can be used to help build,load and report from the data warehouse - these tools are fit for purpose in many instances and enable us to dispense with additional tools. This is particularly relevant when considering lower cost alternatives like MySQL, since these do not provide off the shelf tools and the cost of their provision can easily exceed the saving on software licences.
Widely available skills - we know that you will not always want to rely on a narrow choice of specialists to support your data warehouse. SQL Server skills are widely available in both the contract and permanent IT market, and at rather lower cost than some of the obvious competitors.
Microsoft have produced an updated SQL Server 2008 product. This builds on SQL Server 2005 but the change is incremental and would not justify an upgrade. Earlier versions of SQL Server (up to SQL Server 2000) are significantly less useful, so we recommend 2005 as the minimum level. For most smaller organisations the 32bit Standard edition is more than adequate. Extraction, Transformation and Loading (ETL) Tools Traditional "big iron" ETL tools can be VERY expensive, and the skilled staff to manage them command high salaries. But the good news is that for most SMEs there is no need to go to these lengths - we can use much cheaper tools and still get a good reliable result. All relational databases use a language called SQL (structured query language) to manipulate the data they hold (SQL is not a synonym for "SQL Server" - SQL existed before Microsoft produced their first relational database - they were a bit naughty when they named it). All ETL and reporting tools make use of SQL to manipulate databases, and we can and do use SQL for many processes within the data warehouse. SQL server (2005 on) comes with a very useful toolbox called SSIS (SQL Server Integration Services). This allows creation of automated jobs to load data from many sources into your data warehouse staging, and then business rules applied to load the reporting tables. It is quite practical to undertake all the ETL processes using SSIS alone. If you are using SalesForce CRM or other saas ("cloud") applications, then there are specialist tools that can make extracting their data very much simpler, and avoid the need to retain specialised staff to implement the data warehouse. Pervasive Software provide a tool that can link more or less anything to anything at a reasonable cost. The Pervasive tool is an IT technical tool, although it does not require a high-priced specialist to make it work. At a somewhat higher licence cost, Informatica Cloud Edition is designed to allow non-technical staff to integrate saas applications like SalesForce.com into the data warehouse and other applications, and schedule updates from a single easy-to-use interface.
Reporting Tools It is possible to deploy most reporting tools directly from a single business application without a data warehouse, and many applications come with embedded reporting tools. This approach can be very cost-effective, but if you are in a competitive, knowledge led business and use multiple applications then integrating data across applications will result in information that provides significant business advantage. SQL is the basic reporting tool - all other reporting tools use SQL to return data from the database. However, by itself SQL is limited and you will need to supplement SQL with other reporting tools. SQL Server (2005 on) comes with a reporting suite called SSRS (SQL Server Reporting Services). SSRS can provide useful automated reporting in many formats and with a variety of delivery mechanisms, and SSRS can archive report results. Reports can be embedded in SharePoint sites on your intranet, allowing your staff to access updated information without manual intervention. SSRS is a toolkit with a lot of functionality but it does require a degree of expertise to code, and there are limits on the formatting and presentation of the finished reports. As a step forward, Crystal Reports Server (CRS) is a class leader in providing sophisticated reporting facilities for SME organisations at a modest cost. Crystal Reports is a long-established reporting product now owned by SAP (one of the lagest software vendors in the world) as part of their Business Objects range. With Crystal Reports it is possible to produce sophisticated and customised reports to match precise requirements, and to enable user access to run the reports on demand to select the information they need at the moment - or implement automated delivery of the report in Excel or PDF format via email or to a defined file location. CRS is a relatively complex product - SAP provide support on an "as-required" basis at a fixed per-incident cost, which is more cost-effective than the traditional annual maintenance charge. Crystal Reports is included in the Microsoft .NET toolkit. This means that if you have any requirement that includes programmatic manipulation of reporting services alongside other bespoke IT services then Crystal Reports is a good option. The SAP Business Objects flagship product is Business Objects Enterprise. Traditionally this was a "big-ticket" corporate product, but SAP now have a mid-market version called Business Objects Edge which offers the same functionality at much lower per-user cost (the difference is a limit on the total licences you can deploy). The advantage of Business Objects compared to Crystal Reports is that it includes a meta-data layer called a Universe - a layer of information that allows report development to be separated into two roles - a small number of specialist Universe designers (for an SME often a specialist consultant engaged for short periods), and a report writer who need not be a technical person at all. If your business requires lots of reports to be produced from the same basic data, and there are a lot of business rules to be applied in extracting information then Business Objects can be a good option, but even the Edge edition is significantly more expensive than the other reporting options listed here. Data Cubes and Analysis Services Analysis services (sometimes referred to as OLAP) are inter-active tools that allow a business user to interrogate their data in real time to answer business questions. Typically, the interface allows the user to select the column and row headings, and data filters to apply across a set of dimensions, and select from a set of measures - for example date, country, market sector and product could be dimensions, and units sold, gross revenue, margin, and delivery costs could be measures. We usually advocate getting conventional reporting organised as a first step, but analysis services can provide significant business benefits over and above fixed reports, and some organisations deploy analysis services in preference to fixed reports. SQL Server includes SSAS (SQL Server Analysis Services), which includes a data cube builder product and a client tool to view cube contents. SQL Server datacubes can be accessed by most OLAP clients from other vendors, but we advocate using Excel as your first analysis client, since this does not involve purchasing any more software. Most of the intelligence in an analysis application is created at the server, and since we advocate using Microsoft SQL Sever OLAP cubes the choice of client software is less important - one we particularly like is Tableau.
All images on this site are of the wild beauty of Montenegro