ERPJEWELS.COM

In general, there are lot of questions about OLAP, OLAP cubes, multi-dimensional analyses, data warehouse, data marts and DTS. An attempt is made to answer a user’s all query related to OLAP in this FAQ. This FAQ is general and wherever applicable specific reference to ERPJewels is made. Majority of the question in this FAQ are available also in Microsoft’s public website.

Q. What are cubes and how will they benefit me?
A. A cube is a specialized multi-dimensional database that is optimized to combine, process, and summarize large amounts of data in order to provide answers to questions about that data in the shortest amount of time. This allows users to analyze, compare, and report on data in order to spot business trends, opportunities, and problems. A cube uses pre-aggregated data instead of aggregating the data at the time the user submits a query.  MS Excel Pivot Table is similar multi-dimensional reporting tool available natively in MS Excel. For more information on Pivot Table, please see this.

Q. Must I use DTS in order to build cubes?
A. Technically not required. However, practically one must use some DTS for good intelligence. In a data mart solution, however, you will need to use some sort of ETL utility. The prime consideration is speed / performance of cube browsing. DTS provides a set of tools and programmable objects that let you extract, transform, and consolidate data from disparate sources into single or multiple destinations. ERPJewels server part programs are such DTS giving flexible ETL capabilities from any Baan table and any Baan Report with accurate job scheduling services. Baan report itself an ETL tool fetching data from disparate sources in Baan itself.

Q. Can I use a cube in Excel? Can I access cubes over the Web?
A. Yes to both questions. In fact, a user could connect to a cube over the Web and still be in Excel. There are many solutions to delivering cubes and OLAP data over the Web. Among these, Microsoft ActiveX® controls, Active Server Pages (ASP) scripting, and ActiveX Data Objects (ADO) Application Programming Interfaces (APIs) provide a variety of solutions for querying OLAP data over the Web. When user uses Microsoft Query and saves the OLAP query file before creating the cube, he/she can open the OLAP query (ie oqy files) in Excel which loads the cube in MS Excel using MS Excel Pivot Table report. When users publish the MS Excel Pivot Table using MS Excel, user can access the cube over web also.

Q. What is the difference between a data mart and a data warehouse?
A. Data marts are specialized databases designed to handle the reporting needs of a single department or single line-of-business application. A data warehouse is typically several data marts "rolled-up" into one giant database so reporting can be done enterprise-wide instead of on a departmental level. Warehouses are often expensive and time-consuming to build while marts can be built quickly and inexpensively. Typically, an organization will build a mart for their most important department first and later (if ever) build the warehouse. In ERPJewels, data marts are nothing but the analyses created by users on any Baan Table, Baan report or ASCII file. All these data marts collectively constitute the data warehouse which is resident in Baan production server only. These data marts provide the requisite data redundancy (not more and not less).

Q. Do I need to have a data mart in order to build cubes?
A. Not technically. However, Microsoft cube software is optimized for building on data marts or data warehouses and specifically, multidimensional databases with a star schema configuration. Without pulling the data from a multidimensional data mart or data warehouse, it can be more difficult to build cubes. For most solutions, the best long-term solution will be found by using a data mart or data warehouse consisting of an OLAP database configured as a star schema. ERPJewels allow users to build incremental data marts periodically (when after analyses results are saved after analyses and not deleted).

Q. A data mart (or star schema) seems redundant and a waste of hard disk space. Why can't I just use my operational data without also storing it in a data mart?
A. The fact is that the best reporting solutions require some amount of redundant data (So does ERPJewels). ERPJewels provide for the requisite data redundancy (Not more, not less). In addition, OLTP systems have inherent problems that severely limit their effectiveness as for business intelligence: 

OLTP data can be very inconsistent. For example, customer name fields may be formatted as last name, first name & middle initial in one table, as first name, middle initial & last name in another table, or contained all in one field in another table. Cleansing the data prior to loading it into a data warehouse can remove many of these inconsistencies. ERPJewels has lot of in-built functions to enhance the utility of OLTP data like date conversion, calculated fields, custom grouping etc.

OLTP data typically changes frequently. For example, the number of available units of a particular product can change very rapidly in the course of an hour. An analysis of the number of units sold could vary greatly from one analysis to the next. Refreshing the data in a data warehouse can be scheduled so that the data used for analysis is relatively constant. With ERPJewels, it’s possible to store the summary data on Baan server & client PC for future reference and use.

The data might be located in multiple data sources. Data warehouses provide a way to consolidate data from various sources into a single data source. 
Schemas for OLTP databases are usually optimized for entering groups of records (also known as transactions) and, therefore, tend to contain large numbers of individual records. Summarizing large numbers of records can take a long time. In contrast, data warehouses contain more summary data, which tends to better performance for reporting. Typically Baan report can report data from multiple data sources and ERPJewels uses Baan report as data source.

Servers hosting OLTP databases are usually busy with transactional processes. Summarizing large groups of records can rapidly tax a server hosting OLTP databases resulting in poor reporting performance or poor transactional processing. Data warehouse are optimized for reporting. Interestingly ERPJewels job scheduling capabilities makes it possible to use OLTP server when there is no transactional load. Small analyses ( say upto  200,000 data source records with 2000 distinct summary records ) can easily be done even when transactional systems are loaded. They are just like any heavy update / process session running in Baan. Through process monitor tools user can easily decide what kind of load is appropriate for their servers.

 
If you want to reduce or eliminate redundancy, then your reporting efforts will have to be based on your source OLTP systems. It is worthwhile to note the ERPJewels is designed to use Baan Job management which runs at night and uses Baan's native reporting capabilities. Spending money on additional hard disk systems and some time to create a data mart (or data warehouse) is well worth the benefit that an OLAP tool provides.

Q. What is the difference between MOLAP, ROLAP, and HOLAP?
A. Remember that a cube is pre-calculated summaries (aggregates) of your data mart data. MOLAP, ROLAP, and HOLAP are different methods of storing these aggregates on disk. Here is a summary of their major features: 

MOLAP (Multidimensional OLAP)—Stores the aggregates and the base-level data in your data mart into a number of proprietary files. While this requires a bit more hard disk space, it is the fastest type of cube accessibility and it greatly reduces the strain on your data mart. 

ROLAP (Relational OLAP)—Stores the aggregates in your data mart as tables alongside your base-level data. While this minimizes the hard disk space needed, it is the slowest type of cube access. 

HOLAP (Hybrid OLAP)—A compromise between MOLAP and ROLAP. The aggregates are stored in a MOLAP file, while the base-level detail is kept in the data mart. This provides excellent performance while browsing aggregates, but is slow when a user "drills down" to base-level detail. 

Usually, MOLAP is the best choice for most cubes.  Precisely Excel Pivot Table output from ERPJewels does this only. HOLAP & ROLAP are not possible with ERPJewels. The reason for this is performance and speed of browsing multi-dimensional data.

Q. What are the general steps involved in creating an OLAP and building a cube?
A. Typically, When the user uses the OLAP services of OLAP server, the process of building a cube can be broken down into these general steps : 

Data is transformed and loaded into a data warehouse or a series of data marts—The operational data of the business is copied from the origin data sources into a data warehouse or data marts. During this process, the data is "cleansed" to remove erroneous data and formatted to be consistent. This process is usually accomplished by some ETL utility such as DTS in Analysis Services or ERPJewels for Baan. The data warehouse typically consists of one or more fact tables joined by a number of dimension tables in a star schema. The fact tables contain contains the numerical data (that is, measures) and the dimension tables contain categories by which the measures can be separated for analysis such as customer information, product information, or time periods. The data in the dimension tables is sometimes further subdivided into additional tables that are joined to other dimension tables, resulting in a snowflake schema.

Hierarchies and levels can be defined for the dimensions—Hierarchies typically display the same data in different formats such as time data can appear as months or quarters. Levels typically allow the data to be "rolled up" into increasing less detailed information such as in a Region dimension where cities roll-up into states which roll-up into regions which roll-up into counties and so forth. This allows the user to "drill-up" or "drill-down" to see the data in the desired detail. Levels and hierarchies for a star schema are derived from the columns in a dimension table. In a snowflake schema, they are typically derived from the data in related tables. 

The cube is created—Once the data has been loaded into a data warehouse or series of data marts, the cube can be built. As such, it is also defined as a set of dimensions and measures. In addition, because a cube is used for data analysis and decision support, the data in the cube can be further aggregated to provide a more summarized view of the data than that available from the data warehouse. 

The storage mode for the cube is selected—Physical storage options affect the performance, storage requirements, and storage locations of the data used by the cube. The three options available include MOLAP, ROLAP, and HOLAP. 

The cube is processed—When you process a cube, the aggregations designed for the cube are calculated and the cube is loaded with the calculated aggregations and data. Processing a cube involves reading the dimension tables to populate the levels with members from the actual data, reading the fact table, calculating specified aggregations, and storing the results in the cube. After a cube has been processed, users can query it. 

The cube is now ready to be used by users—Users can view the cube data by using the Cube Browser in the Analysis Manager, by using Microsoft Excel, or by using other specialty applications such as the Microsoft Data Analyzer. Cube Browser allows you to quickly browse multidimensional data in a flattened, two-dimensional grid format. The Data Analyzer provides a complete overview of your data on one screen so that you can quickly find hidden problems, opportunities, and trends. 

Of course, depending on the complexities and structure of your data and the types of analysis your users will be doing, other, more complex steps may be necessary to complete the process.

However when user wishes to create the using MS query (ie without using SQL server), the process is very simple. On any external data source, run Microsoft Query and with Menu click ‘Create Offline cube’ which has easy to follow steps to create the cube. Microsoft query is also called directly from MS Excel when using external data.

Q. Do I need to use SQL Server in order to build cubes?
A. It is true that SQL server’ OLAP services and data analyzer services facilitates creation of cubes from SQL server, it’s not mandatory to have SQL server for creating local cubes. You can build local cubes from Microsoft Excel, for example or by using MSQuery. MSQuery is an optional component of MS Office 2000 / 2002. MS Pivot Table is a multi-dimensional reporting tool like cubes available natively in MS Excel. Functionality is built in ERPJewels to integrate with MS Excel Pivot table report. User can optionally view the analyses results in Microsoft Query.  Further in case the user wishes to use SQL server’s OLAP services, it's possible to use the same along with ERPJewels interface. In any case, the user will be able to query the cube / analysis results very fast. This is because very precise & concise information is available to the client / SQL server (MOLAP). Due to performance considerations users do not use ROLAP or HOLAP cubes even if available.

Q: Will local cube files support all the features of a cube sitting on an OLAP server? 
A: Local cubes will not support ALL features of OLAP Server.  For more information on local cubes and pivot table service, please click this .

Q. Which OLAP databases can I use for building cubes and performing multi-dimensional data analysis?
A. Cubes can be built and managed with Analysis Services of SQL Server. Analysis Services is included with the Enterprise, Standard, and Developer editions of SQL Server. Further Oracle 9i database also has full fledged warehouse building capabilities with relational and multidimensional systems. ERPJewels DTS can give summary information periodically to SQL server / Oracle for updation to SQL server / Oracle. General program with DDL statements have to be written in a client program by customer to update the SQL server/ Oracle. If the Baan production server also is OLAP server, (which is possible in case of SQL server & Oracle 9i) users can use forward integration of ERPJewels to use the database native OLAP services. All users need to do is to write a small after analysis program for each analyses, to update the same into native databases relational tables.

MS Excel Pivot Table report also allows multi-dimensional analyses on any data source using client Pivot Table service. There are limits to MS Excel Pivot Table. As the very nature of MIS is summary, compact and precise data, limit of 2 million cellsets (8000* 250) may not be material for many users.

Q. Do I need separate OLAP server separate from Baan production server.
A. It is necessary to understand the basic data marting / ETL process and the premises underlying the requirement of separate OLAP server before coming to any definitive conclusion.

1. ETL applications are highly CPU intensive. Hence loading ETL applications on ERP production server will load the production server with unnecessary extra work and will affect the work of others doing day to day transaction activity. This is true. However with DTS like ERPJEwels which can be scheduled at night when there is no load on ERP server, this premise can be addressed easily. However in situations where customer works 24X7 on ERP server or where servers are shut down in the evening, a separate OLAP server may be desired. However one will easily agree that these are rare situations. Further for small analyses (e.g. 200,000 data source with 2000 distinct cellsets), the load on cpu for few minutes only. Further in OLTP itself there are lot of cpu intensive programs and processes. One can view the similar processes with process manager of the OLTP server.

2. Good data warehousing or Business Intelligence pre-suppose the existence of good DTS (Data Transformation  Services )  on ERP . In normal cases , there are no such tools to do these tasks. Hence MS SQL server which has it's native DTS are normally suggested. However with ERPJewels, users need not have SQL server to do the DTS. Traditional querying does not have the capability to translate relational data into multi-dimensional data which ERPJewels possess.

3. ERPJewels loads the analysis results into MS Excel Pivot Table which is very good multi-dimensional analytical tool for users. Further with the use of MS query , users can create cubes locally without SQL server (Office 2000 & 2002). Office Data Connection files (Pivot table in html format with all drag and drop features of MS Excel) are yet another tool to present multi-dimensional data in internet explorer. Pivot Table lets users play with summary MIS in a very user friendly manner. For many users these are more than enough. In such a case, users may not use separate OLAP services. For more information on pivot table features please click this. In order to understand the limits of MS Excel and Pivot Table, click this. As the very nature of MIS is summary, compact, precise and concise, one will appreciate these limits are no limits at all.

4. Further there is a perceived need to drill down to transaction level from final analytical results. Pivot Table output of MS Excel does allow the drag and drop, drill-up and drill through. However going back to transaction level is not possible with ERPJewels (though in most cases it is not desirable also). This is possible only with ROLAP / HOLAP cubes which are possible only using SQL server's OLAP services. However due to poor performance reasons (even in single user mode on the server), key users seldom drill down to transaction level because of poor performance. Key users do need the hierarchies and levels which can well be done with MOLAP cubes (like local cubes created with MS Query / Excel Pivot Table report / ODC files). So in cases where users do not need to go to transaction level (which are the most), separate OLAP server may not be required.

5. Many a times it is seen that careful data marting is not done with separate OLAP server and users follow EETL (Extract from production server to OLAP server, Extract from OLAP Server, Transform and finally Load the results in cube) while claiming to follow ETL methodology. This is definitely a longer route. ’EXTRACT’ is the major time and CPU consuming resource in overall OLAP. For more information, please see this. Further due to differences in technology on OLAP server and Baan production server (like date, domains, enums, labels , field description ,table indices, company numbers, field alignment, case conversion , DML , DDL statements etc), building data marts is time consuming.

If any of these issues is a material consideration, then users may use OLAP services of an OLAP provider to do multi-dimensional analyses. Ideal roadmap could be to use / test ERPJewels with MS Excel native Pivot table capabilities. And if required user can still use OLAP interface using ERPJewels DTS (Data Transformation Services).

Q. Why do I use ERPJewels DTS instead of SQL server or Oracle 9i' native DTS, if I am on SQL server or Oracle 9i itself . 
A. It's true that SQL server and Oracle 9i also offer powerful DTS. But users will still require and use ERPJewels for following reasons.

1. ERPJewels is written in Baan 4GL which is native to Baan and can understand Baan dictionaries better than anybody else (like date, domains, enums, labels , field description ,table indices, company numbers, field alignment, case conversion etc). ERPJewels query generator takes care of this in a better way.

2. ERPJewels can extract data from Baan report. And Baan reports can calculate any figures and numbers (especially after field). Baan reports are scalable transformation engine capable of versatile data transformation. Please see Baan report as data source for more information. In order to get best Baan reports, it’s necessary to understand the Baan reporting structure thoroughly.

3. There are about 2000+ standard reports in Baan. And the customer may already have created great number of customized reports for their internal MIS. If the customer is to have the same MIS outside Baan for OLAP analysis, then tremendous development and implementation-like effort is required. Using ERPJewels existing investment in Baan MIS is protected and it reduces the TCO of  data warehousing & business intelligence.

4. Baan report can be run under Baan Job. And ERPJewels is well integrated in with Baan Job management for analyses. Thus client can use Baan Job management for MIS purposes in addition to day end job activities. This facilitates sophisticated PUSH intelligence as against traditional PULL intelligence. 

5.Baan users are well familiar with Baan report, it's functioning & it's development .And these users don't have to be re-trained for other DTS software.

6. ERPJewels implementation is very easy and requires minimum implementation time.
7. ERPJewels enable MOLAP cubes. ROLAP / HOLAP cubes will not be possible. However this is what users require normally due to very poor response times associated with ROLAP/HOLAP.

8. ERPJewels has tackled most complexities and intricacies of a Baan report and is a relatively simple, yet very effective and stable tool for good DTS.

9. There is very good Easy analyses creator for Baan report which helps users create analyses without having to know much technical details.

Thus it's clearly evident that ERPJewels is best fit DTS for any Baan user.

Q. Does any existing OLAP users require ERPJewels?
A. Many a times it is seen that Data warehouse implementations are done poorly (please see EETL ), data marts are not constructed intelligently and key users do not use OLAP services due to poor response times. In such cases, ERPJewels DTS can help the users with simplicity, flexibility and convenience.

Q. How does ERPJewels interact with SQL Server / Oracle 9i.
ERPJewels does not directly use the analysis services of SQL Server and does not require SQL Server as a pre-requisite. It uses MS Excel Pivot table service and MS query cube creation service to create and view cubes. If however the user wants to use SQL server features only or has SQL server already, they can use ERPJewels as DTS which can extract and transform data from Baan table/report and make the transformed data available to custom built clients which populate SQL server Data Marts. These will enable user to do play with MOLAP cubes very fast. In such a case customer will be able to use analysis services of SQL server. User has to choose OLAP Interface analysis type for such interface.

Q. What is the difference in Relational and Multi-dimensional data modeling?

A. Form difference in relational and multi-dimensional data modeling, please see this.

Q. Are there differences with OLAP source and non-OLAP source data when using Pivot table in MS Excel? If yes, what are the differences?

A. From user perspective, the difference is none. However technically there are some differences. For more information, please see this.

Q. How does ERPJewels interface OLAP services of an OLAP provider?

A. OLAP interface is an output type in ERPJewels. For more information, please see this .

FastTrack to ERPJEWELS :

Latest Trend | Hot Links | Jewels of ERPJewels | Jewels of Baan | Jewels of Excel