DAXGuy: SSIS for Bulk insert into Dynamics AX 2009

DAXGuy posted today, about a recent Proof of concept he was working on for inserting a lot of records from an outside LOB application into Dynamics AX. He was able to insert 100,000 records in 13.6 seconds!.

A direct link to DAXGuy's post can be found here.: Dynamics AX 2009 Bulk Data insert using SSIS



This is actually an interesting post. What we have to look at is the fact that this bulk insert is being done on a custom table, with not a lot of X++ logic. So for example if you were creating Sales Orders, Forecast data, etc. there is extra logic that is ran on the delete's, insert's, etc. methods of these tables.

Such a Bulk insert works for custom table data, that is targeted and focused. However if you needed to take and do bulk insert for forecast data, for example, or Sales data, you would either need to recreate the logic in SSIS package that is done for the insert's and delete methods on the given tables your operating on, or make use of the .Net BC.

This in turn is where we run into an issue. I believe the work DAXGuy is a great example, and I am glad he posted it. It brings up though a point with Dynamics AX about loading massive amounts of data.

Bottom line, if it's custom tables being loaded, then it can be fast. However if out of the box tables, with a lot of underlying X++ business logic embedded into the creation of the data for said tables exists, you hit a road block.

You can either choose to by pass the logic, if your instance does not need it. Then the bulk inserts and massive data imports can be done. However if needed, then you either have to recreate through your choosen data import engine, like SSIS, or make use of the code.

This is where a slow down takes place.

Let me leave this post, with thanks to DAXGuy. A great post, with detailed steps on making use of SSIS for bulk insert. Let me also leave though, that this leads into a post I had planned, with working with threads.

That's all for now, check back soon though as more to come!




"Visit the Dynamics AX Community Page today!"

Dynamics AX 2009 - Reporting Possibilites

So we have talked about BI in the past few months. Went over several Microsoft tehnologies that make up the Microsoft BI platform. This includes SQL Server, Reporting Services, Analysis Services, Integration Services.

This is all the BI platform from Microsoft. And we now have started to look at how this BI platform fits in with and can be used inside Dynamics AX 2009.

The first dive into this was a pratical use for creating custom SSRS reports for a Dynamics AX 2009 instance, and looking into OLAP use for Dynamics AX 2009. (Link: Dynamics AX 2009 BI: Practical use with SSRS and looking at OLAP)

So with that we saw with Dynamics AX 2009, there is a new Visual Studio 2008, Project Template for Dynamics AX 2009 SSRS custom reports.

With showing this, and coming back around to all that we have covered with this so far, I think we are at a point to where we should look at the higher level of reporting options inside Dynamics AX, and get some higher level pros and cons about them.

So with that said, lets look at the reporting options for a Dynamics AX 2009 instance.

The point of reporting is to gain access to data that lives inside a System and it's database. This is the point of reporting with Dynamics AX 2009. To give users access to the different data they may need to perform functions, processes and help make business decisions.

So the reporting options with Dynamics AX addresses a spectrum of reporting. You can look at this as having Procedural based reporting on one end of the spectrum and Analytical reproting at the other end.

On this spectrum, there is a few different ways that reports can be created that span the above mention spectrum. The following is a high level list of these reporting options.:

Standard MorphX reports
The standard MorphX reports are the base reports that come inside Dynamics AX. Most of these are very procedural based in their nature. They are meant to delivery things like invoice journal data, Sales Order Confirmation, Invoices, etc. They serve a purpose to help fulfill a business process.

These kind of reports have the most flexibility for working with business logic, and Dynamics AX objects. They are also the most difficult to develop and modify at times, and are the older way of reporting inside Dynamics AX. (They have been around since the early Axapta days.)

You use X++, and MorphX to create, modify and work with these reports. End users can make use of custom queries and filter possibilities that can stay with that user for as long as their usage data is kept.

So this offers the most flexibility of any of the reporting options for getting at Data inside Dynamics AX. These are very developer driven reports, and super users would not be creating these.

Though this is mostly procedural based reporting, some analyical reports exists in this form, though not many.

Custom SQL Server Reporting Services (SSRS) reports
This reporting option, though could be done to some degree in earlier versions, is really just now a true offering in Dynamics AX 2009.

This makes use of a easier, Visual Studio driven development model, that .Net developer would find familiar to work with. This process creates report definition files, that are used and connect through the .Net Business Conector to work with Dynamics AX data and business logic.

The business logic can be access through using the AxaptaWrapper .Net class, that comes from making use of the .Net Business Connector and the Dynamics AX .Net Reporting Framework.

This is not as complex to create, makes use of a report designer that is easy to use, drag and drop possibility, and real flexible in design and layout possibilities.

This also executes on a SSRS server, which can offload some of the report generation from the AOS itself.

You don't have as much flexibility with this option as a standard report, for example the custom Qeuries for filtering a report, that are user driven can not be used. The developer of these custom SSRS reports must give access to filtering through the report design.

This option offers the ability to create procedural based or analyical based reports in nature. OLAP cubes, KPI's, etc. can be access and added as sources of information for building reports here.

Report Builder / Ad-Hoc Report Creation
This option first came to exists in DAX 4.0, and has been improved in Dynamcis AX 2009.

This makes use of the perspectives node inside the AOT, to expose report models with to the Report Server. This option is targeted at super users who want to get at report data quickly, and in an ad-hoc nature.

This makes use of the SQL Server Reporting Services - Report Builder tool, and based on your instance and the deployed perspectives, will govern what report models your super users can access to look and report on data.

This is very much a drag and drop ability, and meant to be a design so the super users don't have to know the data model.

This is the most limited of the reporting option, and that understandable as this is Ad-hoc, and not meant to give all access to everything. The biggest limit is access to the X++ calculated fields inside Dynamics AX. Those fields that are not just a single table field, but calculated depending on a number of different variables.

The point of this though is to get super user the ability to gain access to around 80% of the reporting data needed. If more is needed, then they can get with thier IT staff to deliver a custom SSRS report for the full amount of data.

This reporting option, like the one before, can offer Procedural and Analytical in nature reports. Access to Cube and Procedural perspectives allow access to different data, and the different data and relations that might exists.

SQL Server BI Development Studio (BIDS)
This is the out of the box tool that most standard Microsoft BI projects use to create reports with. This is also used to create and manage OLAP cubes and OLAP database, and create and managed SSIS packages and logic.

For the report creation this is direct access to the SQL Server database, and now interaction with Dynamics AX itself. So that is the biggest Con with using this to create reports with. No access to those, sometimes much needed X++ calculated business logic fields.

If you choose this option, and need those fields, you would have to 're-create the wheel' and duplicate that logic through T-SQL or SQL Scalar Functions.

This can be very Procedural or Analytical, depending on what your doing with BIDS. Also, BIDS is used, even if not for report creation, to manage a Dynamics AX 2009 instance OLAP cubes. To look at them, modify them, trouble shoot them, etc.


Well that's all for now. I think this is a good start to the reporting options inside Dynamics AX 20009 instance. Moving forward we will dive deeper into looking at each of these, getting some screent shots and real world examples.

See you soon!





"Visit the Dynamics AX Community Page today!"

What is SQL Server BI Anyway? - Part II

Recently I post this article: 'What is SQL Server BI Anyway'

In that article I took and addressed, at a high level, what SQL Server BI is. What technologies from SQL Server can and are a part of a SQL Server BI Solution.

We answered with that post: 'So what is BI in terms of SQL Server?' We talked about the 3 main teachnologies that make this up: SSRS, SSAS & SSIS.

Now having that knowledge level of SQL Server BI Solution, and technologies, that make it up, we can move forward with the next question posed during that post.

Why is this important to understand?

The reason why this is most important to understand, for someone wanting to know about BI in terms of Dynamics AX, is that the BI offered in Dynamics AX is based on the technologies that SQL Server BI Solutions are made up of.

On top of that, the BI solutions inside Dynamics AX add another part to the dimension of reporting, with KPI offered up by PerformancePoint Services for SharePoint. Also SharePoint itself plays another critical role in offering these reports, Cube, KPI point, etc. via Role Centers, targeted to information worker roles inside the Dynamics AX instance.

Once you understand the BI solution from SQL Server point of view, then you can truly incorporate that into Dynamics AX 2009 (and beyond) with how those technologies, were meant to be used.

For example, SSRS reports are meant to be used within Dynamics AX, and Dynamics AX role centers for reports. There is a new AOT node inside Dynamics AX 2009 called Datasets. These Datasets are part of what enables datasources for SSRS reports to be built with.

You also have OLAP cubes, which Dynamics AX comes with standard cubes out of the box. These can be built, processed, and then viewed from within Dynamics AX. For analytics, this is where your data comes from. Looking at different dimensions of data, across a slice of time, customers, vendors, inventory, etc. etc.

Then PerformancePoint comes in, which will not be delivered by SharePoint, and be a part of SharePoint. This offers KPI and other Analytical dashboards, etc. for use with your transactional data.

This could also be made use, of say you make use of SSIS to build a data mining database or several that come into a Datawarehouse. Well in turn the KPI, Dashboards and reporting inside PerformancePoint Services for SharePoint could be ran not just on the Dynamics AX parts of the information, but on all the data in your Datawarehouse for cross sections of information from all the systems you make use of.

There is a white paper on Microsoft's site, that is right at a year old. Actually one year old this month, to be exact. Even though this is a year old white paper, this does a great job of giving even more details into what I am talking about here. The direct link to that whitepaper can be found here: Microsoft Dynamics AX - BI White Paper (2008)

The only thing you need to really make note of in this, is that PerformancePoint Services is now part of SharePoint. That knowledge was not known yet when this white paper was wrote.

Also feel free to take a look at the BI home page of Microsoft: Microsoft BI Home page.

With the knowledge that you have gained, or can gain from these several post that have been done, you can start to really understand BI from Microsoft's point of view. The solution, how they are used and built from SQL through Dynamcis AX.

One other thing that can come into play is Office. I did not mention that, but Excel is a favorite of everyone. That very much can play into your BI solution, where Excel can be the front end for deliverying BI reports and Analytical information to your information workers.

Well check back soon. More great post are coming out. If you have further questions about BI, getting BI working for you at the SQL or Dynamics AX level and want to get into more details. Feel free to contact the experts at Sunrise Technologies, Inc..

See you soon!




"Visit the Dynamics AX Community Page today!"

What is SQL Server BI anyway?

One of the major focuses for a lot of companies these days, even in these tough times, is BI. I have wrote about this recently, specifically with the move of PerformancePoint as being part of the SharePoint offerings.

Direct link to my last post here: Dynamics BI: BI for the Masses

That post gave a real broad picture into what BI will look like from an overall Microsoft BI strategy.

However, what is BI in terms of SQL Server? Why is this important to understand?

Well Dynamics, and therefore Dynamics AX, makes use of SQL Server to run on top of. And more and more SQL Server features are being tightly integrated and very much a part of Dynamics AX solution.

Since this is the case, it makes sense, that one needs to understand BI from SQL Server point of view. In doing this, then the incorporation can better be unnderstood, and the use of SQL Server BI solutions inside Dynamics AX can be better made use of.

So what is BI in terms of SQL Server?

BI for SQL Server is made up of several key technologies / terms. They are:

• SQL Server Reporting Services (SSRS).
  


• SQL Server Analysis Services (SSAS)
  - Multidimensional Data
  
  - Data Mining
  


• SQL Server Integration Services (SSIS)
  

These technologies make up what can be used to create BI solutions from SQL Server's point of view. Each of the above links goes to the home page found on MSDN, which contains to wonderful amounts of information about each of these topic area's.

SSRS can be used to create functional / procedural reports, where SSAS can be used to mine data, create predication based cubes, and other OLAP data dimensional cubes from transactional data. SSIS can be used to ETL, or Extract, Transform and Load data from many, many sources into a SQL Server staging or Datawarehouse that can then be used by SSRS and SSAS for reporting and knolwedge management about a businesses operations.

These are the key to understanding BI solutions from SQL Server's point of view, and answer the question What is BI in terms of SQL Server?

Next I will move forward with these topic area's for help in understanding them, and answer the next question in line: Why is this important to understand?

Check back soon, and see you later!




"Visit the Dynamics AX Community Page today!"