OLTP vs OLAP - Definition and Differences

We can divide IT systems into transactional (OLTP) and analytical (OLAP). In general we can assume that OLTP systems provide source data to data warehouses, whereas OLAP systems help to analyze it.

In Microsoft Dynamics AX 2012 database we can find an OLTP database, which stores the AX data and the AX Model store, and the SSAS database used for SSRS reporting. At the same time, the Microsoft SQL Server Analysis Services uses an on-line analytical processing (OLAP) engine that helps users analyze business data and identify trends.

In this post I would like to describe what OLTP and OLAP is, differences, and provide a day-to-day example about the two.

OLTP (On-line Transaction Processing) deals with operational data, which is data involved in the operation of a particular system and it is characterized by a large number of short on-line transactions (INSERT, UPDATE, and DELETE).

The main emphasis for OLTP systems is put on very fast query processing, maintaining data integrity in multi-access environments and an effectiveness measured by number of transactions per second.

In addition, in an OLTP system, the data is frequently updated and queried and to prevent data redundancy and to prevent update anomalies the database tables are normalized, which makes the write operation in the database tables more efficient.

Example: In a banking System, you withdraw amount through an ATM. Then account Number, ATM PIN Number, Amount you are withdrawing and Balance amount in account are operational data elements.

An OLAP (On-line Analytical Processing) deal with Historical Data or Archival Data, and it is characterized by relatively low volume of transactions.  In addition, the Queries needed for these systems are often very complex and involve aggregations as for OLAP systems the response time is an effectiveness measure.

Example: If we collect last 10 years data about flight reservation, the data can give us much meaningful information such as the trends in reservation. This may give useful information like peak time of travel, and what kinds of people are traveling in the various classes available (Economy/Business).

The following table summarizes the major differences between OLTP and OLAP system design.

	OLTP System	OLAP System
Source of data	Operational data; OLTPs are the original source of the data.	Consolidation data; OLAP data comes from the various OLTP Databases
Purpose of data	To control and run fundamental business tasks	To help with planning, problem solving, and decision support
What the data	Reveals a snapshot of ongoing business processes	Multi-dimensional views of various kinds of business activities
Inserts and Updates	Short and fast inserts and updates initiated by end users	Periodic long-running batch jobs refresh the data
Queries	Relatively standardized and simple queries Returning relatively few records	Often complex queries involving aggregations
Processing Speed	Typically very fast	Depends on the amount of data involved; batch data refreshes and complex queries may take many hours; query speed can be improved by creating indexes
Space Requirements	Can be relatively small if historical data is archived	Larger due to the existence of aggregation structures and history data; requires more indexes than OLTP
Database Design	Highly normalized with many tables	Typically de-normalized with fewer tables; use of star and/or snowflake schemas
Backup and Recovery	Backup religiously; operational data is critical to run the business, data loss is likely to entail significant monetary loss and legal liability	Instead of regular backups, some environments may consider simply reloading the OLTP data as a recovery method

Intelligent Data Management Framework (IDMF)

Hi there,

There has been a lot of talk about the Intelligent Data Management Framework (IDMF) in AX 2012, and for a good reason!  This is because the Intelligent Data Management Framework allows the system administrators optimize the performance of Microsoft Dynamics AX installations, which is something that we all should be concerned about when implementing AX 2012.

Why? Well, first of all, the system requirements for AX 2012 are going to be much more than the ones for AX 2009.

This has been proved by a recent installation in Bank of America where they thought the hardware required was much more of what is needed for AX 2009. For this reason, the IDMF assesses the health of the Microsoft Dynamics AX application, analyzes current usage patterns, and helps reduce database size.  In addition, the IDMF lets you analyze the database and maintain an optimal database size by providing the purge and archive functions.

A Microsoft articles says “the purge function removes or deletes data from a set of related entities, or tables, from the production database. The archive function moves data from all related tables from the production database to a standby database called the archive database. Users can use the archive database for reporting purposes but cannot update it”

Further, both the purge and archive operations depend on a hierarchical relationship tree of related tables based on the Microsoft Dynamics AX metadata, and to create a hierarchical relationship, you select a parent table and discover all related tables based on the AX metadata.  

Moreover, the parent table in the relationship is put at the root level of the tree and the discovery process creates a nested relationship tree from a parent entity to a child entity. This is done until there are no relationships left at the lowest level.

Also, a purge object is used to remove selected records from all tables in the relationship tree from the AX database. Similarly, an archive object is used to move selected records from all tables in the relationship tree from the AX database to the archive database.

After creating a purge object or an archive object, you can apply business rules and selection criteria to entities and transactions to determine which records are deleted or moved from the production database.

System architecture

The IDMF was created using the Microsoft .NET development environment and provides a single document interface (SDI). In addition, the IDMF uses a database, called the management database, for the storage and retrieval of data, and communicates with Application Object Server (AOS) through COM Business Connector or .NET Business Connector.

Now, just be aware that during installation, the IDMF installs a Windows service called the Intelligent Data Management Framework for Microsoft Dynamics AX service. This service is used to run scheduled jobs and is referred to as the scheduler service for the Data Management Framework.

The following diagram provides a high-level overview of the Data Management Framework system architecture.

Deployment scenarios

Single-server deployment

Multi-server deployment

Distributed deployment

Take care!

Inside Microsoft Dynamics AX 2012 Performance

Hi there!

I found a very interesting video on AX 2012 performance. It is amazing and really exciting to be part of this release as there have been many improvements to create a better, more reliable and faster product. The following are the key points in the video:

1-   Ax 2012 focuses its efforts on the AOS, this is, the AOS can scale up and scale out.  For example, as computer manufactures create bigger boxes (strong processing power), AX 2012 application server scales out to take full advantage of this new advances.

2-    Ax 2012 takes the unmanaged X++ and run it in .NET. Therefore X++ is now a managed language which means is faster (way faster than before!)

3-    There has been a lot of work done towards space optimization. For instance, in retail situations the number of transactions can quickly grow, and this data needs to be stored efficiently without increasing storage costs.

4-    Transactions are much faster thanks to the improvements in X++ compatibility with .NET. For example, in AX 2009 you could run 100 lines of code in X amount of time (X being a number of seconds). In AX 2012 you can run 1,000 lines of code in the amount of time (X) that AX 2009 does.  This helps achieving fast deployments and customer usability of the product.

5-    The goal of AX 2012 is increase transaction speed and customer satisfaction by implementing the solution on pretty much the same hardware an end user had before.

Check it out.

Take care!

AX 2009 Data Import / Export

The following post is about shows how to:

1-   Export data from a company in AX using a definition Group

2-   Creating a new company that will take the new data

3-   Importing the data exported from step 1 into the new company created in step 2 using a definition group as well.

In a current project, I needed to freeze the Test1 environment due to some focus testing of a certain functionality we implemented. At the same time, we needed to allow the uses to keep testing other customizations without affecting Test1.

In addition, to make the user’s lives easier, we decided to have the same data that they were using in Test1 in our Test2 instance. So, we exported the data from our Test1 environment, and then imported it to a new company in our Test2 environment.

The following steps will show this process step by step:

1-   First select the company you want to export the data from as the AX current company by clicking in company name in the right lower corner of your AX interface. 2-   This would open the “Select Company” form as shown;

3-   Select  Administration (module)

4-   Under periodic->Data exports /import ->Click Export to

5-   The “Export options” form will appear

6-   Select a definition group. You can either create a new definition group to include what you need, or just choose ALL to get all the tables. NOTE: In some cases, the already defined “ALL” definition group will not have all the tables, so in this case you would have to create a new definition group. In my case I created a new one name “ALL_NewDef” that included the latest tables.

7-   Enter a file name and select where to save it

8-   Click OK. At this point the company date export process should be done (after processing of course), and the files (.dat & .def) should have be saved on your computer.

Now, let’s create a new company

9-   Make sure that the selected company empty and click area node Administration

10- Click company Accounts under Common forms

11-   Press (Ctrl + N) in order to Create new company

12-   Make sure you select the newly created company as the current company

Import company file already exported in the prior steps and click Administration

13- Under periodic->Data exports /import ->Click import; The “Import options” screen will appear

14- Select the “import definition group” drop down list and choose ALL (otherwise create your own)

15-   Select the .dat file (exported before) and Click Ok to start the process

The import process may take some time according to the size of transactions in the original (exported) company.

In addition, you can choose to delete all the transaction data before the import when you are importing a .dat file o an existing company account to update the data.

SQL Injection: Defense in Depth -

SQL Injection happens when a developer accepts user input that is directly placed into a SQL Statement and doesn't properly filter out dangerous characters. This can allow an attacker to not only steal data from your database, but also modify and delete it.

Certain SQL Servers such as Microsoft SQL Server contain Stored and Extended Procedures (database server functions). If an attacker can obtain access to these Procedures it may be possible to compromise the entire machine.

In addition, attackers commonly insert single qoutes into a URL's query string, or into a forms input field to test for SQL Injection. If an attacker receives an error message like the one below there is a good chance that the application is vulnerable to SQL Injection.


Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the
keyword 'or'


The following article talks about how to prevent SQL Injections. I thought it was pretty comprehensive and has good examples on how to achieve a good strategy to minimize these attacks.

http://www.simple-talk.com/sql/learn-sql-server/sql-injection-defense-in-depth/

You can also learn more about it here http://msdn.microsoft.com/en-us/library/ms161953.aspx

Also, there are a few videos that walk you through some of these issues here http://www.google.com/#q=sql+injections+tutorial&hl=en&sa=X&prmd=ivns&source=univ&tbs=vid:1&tbo=u&ei=DGCDTceeN6WY0QG8r7XkCA&ved=0CEcQqwQ&bav=on.2,or.r_gc.r_pw.&fp=3d8c1b5379a812ef