Backing up an SSAS OLAP Cube Database, by command

So you have Dynamics AX 2009 up and running, you have deployed everything, got the SPN's set and working, and the OLAP cubes deployed.

You go and run Role Centers, and some KPI's are failing and some Cubes and reports are. You investigate, and go through the motions of fixing the OLAP cubes. (A later post on this to come)

After getting the cubes fixed, dimensions set, KPI's working, and the underlying Data View for the Cube Database all correct, what's next?

Well you will need to make sure and create a SSIS packge, that will backup a SSAS Cube Database. But, looking at the SSAS command task, there is not a simple option for this.

The following is the command used to backup the SSAS OLAP Cube Database with, for an SSIS package, that can execute as a scheduled job.:

SSAS Tag Based Backup Command

The above is stored as a link to a text file, because the command is Tag based, and blogger wants to make it into HTML.

Notice there is a password section. If this is given, then the password is set as that password, and must be supplied if used for restore.

This backup process takes and backups to the file location, and file name the meta-data that describes the Cubes and all that make up those cubes. This does not back up any data for these cubes.

If a restore is needed, simply take and restore from the file, and re-process all the cubes. This will get OLAP back up and running for your cubes, with your latest transactional data processed.

Well more to come, including looking at Role Centers, SSRS and SSAS across forest and domains. See you then!




"Visit the Dynamics AX Community Page today!"

Getting the most out of Dynamics AX - Performance I

Alright, so last week I left you would a post about SSRS performance and scaling. This was to kind of wrap up the long series I have been doing on SSRS, which is a part of Dynamics AX BI and Reporting.

I will continue to write about reporting, and get into more pratical post about it. I wanted to also take the time to start a new series of post 'Getting the most out of Dynamics AX - Performance'.

In this series of post, what I would like to do is talk about performance tuning a Dynamics AX instance. I have thought about my approach to this and considered several different ways.

After looking at this from different ways, I want to address this topic in the manor in which this topic exists, Dynamically.

So in order to deliver a series of Dynamic post about performance tunning and scaling of a Dynamics AX instance, we have to look at this from a scenario approach.

Before we get into the first scenario though, it's important to understand what I mean by 'addess this topic in the manor in which this topic exists.'

What I mean by that statement is that performance tunning is not a single, clear, happyily wrapped box. It's a very Dynamic, instance specific on-going series of events that happens over the lifetime of an instance.

There are certain tools that are used in common across instances, like SQL Server Profiler and the Data Tunning Engine. Like using Query Plans to analyze use of fields, indices, etc. Also taking account events, and performance bench marks about CPU, Disk and Network I/O, RAM usage and need, etc. etc.

All of this must come into play, and you must be able to paint in your head with these tools, logs and so forth a picture of the performance your given Dynamics AX instance is experincing at the time.

So lets take a given scenario now and move from there:

You started out with a single, decent size SQL Server, two AOSes, and a few custom applications. Your concurrent users stayed at 50 or below. But now it's been over 1.5 years since you went live, a lot more custom reports and code has been done by your internal staff and you VAR. You also have 100+ concurrent users. MRP and other processes are running a long time, and custom code, processes and report ar taking extreme amounts of time to complete.

What to you do?

First you need to take and write down a list of all the processes, including reports and noting modules and times that are slowing down or taking a while to complete.

Then you need to take that information, and build yourself a plan of attack for what to look for.

While your building your plan of attack start your SQL Server Profiler Trace run, and make sure to get all the right columns and objects. Also make sure to start performance monitoring for CPU, RAM, Disk and Network on your SQL Server.

Let things run for around 4-8 hours depending on how serve, and how much data your Profiler trace is capturing. Take this captured information, along with your performance information and now you can load both up into profiler and look at the times reported slow downs are taking place.

Then attack the known processes and reports that have slowed down, and look at the queries that are executed as part of those. Use the Data Tunning Advisor on those queries, making sure correct indexes exist, noting ones that need to be created and creating them.

The idea, if you can tell from what I am saying so far is start with your SQL Server database. And look at the interaction between AOS / Dynamics AX and SQL Server Database. This means look at XppSQL, making sure that there is not a lot of unneeded while select looping statments. Making sure that there are covering indexes for your where clauses, and that you make use of joins.

Again, focus on the SQL Server, but that also means the code that is being executed from the AOS and the objects they are hitting or should be hitting (like missing indexes).

There is plenty of information on the web for how to use the SQL Server Profiler, how to read a Query plan, and make use of the DTA. Just keep in mind, with all the information, you have to read into to it. Don't just randomly accept the suggestions from the tools, like DTA. Investigate if what is being suggested is valid.

The other side to this SQL Server focus is looking at the possibility that you need to increase the RAM on the SQL Server, or maybe CPU power. The performance monitor will tell you this.

Alright, we will stay with this scenario and dive deeper. This is a good start to think about performance, and start getting your mind around what it takes to be able to address performance problems.

The mind set must be, it's a Dynamic set of on going events. This can't be a static thing that happens once. You don't have to deal with it everyday, but just because you get things running smooth and fast, does not mean that will last for 3-6 months. If you add a lot, change a lot, etc. then what ever is changing and adding needs to be investigated for best performance possible.

I will continue this series, and stick with the scenario later this week. To make this more Dynamic, if you have questions about getting the most out of your Dynamics AX instance, feel free to leave me a comment or email me through my profile. If you leave a comment, leave a way for me to contact you. Also if you wish the comment remain private, for email spam protection, etc. make sure to note as well in the comment and I will not publish the comment.

That's all for now, see you soon!



"Visit the Dynamics AX Community Page today!"

SSRS Performance and scaling

Now that we have dove deeper and deeper into the BI world, one of the Technical things that needs to be understood by someone who would be implementing and administarting a companies BI solution, is proper planning of hardware, and also scaling-out for performance.

That means, that someone would need the ability to understand SSRS, how to scale, when to scale, differences in version between SQL Server 2005 and 2008 for SSRS, etc.

So lets start with an older article from MS TechNet. Planning for Scalability and Performance with Reporting Services

This article was wrote in 2005, focusing on SSRS for SQL Server 2005. Still the knowledge that can be gained from this article is a good bit, and a great place to start.

This document covers scaling up vs. scaling out, the technologies used with SSRS and getting into specifics.



Like some of the basics for SSRS performance would be spliting out the Report Catalog (SQL Serve Databases) from the Report Server (IIS and SSRS Service). That would be an example actually of a scale out.

More true to the term of a scale out would be though, taking the Report Server, and having a few different boxes that run the Report Server role, and load balance them for execution. These all would point to the same Report Catalog, and therefore could connect to Dynamics AX.

You can see this from the following:
"The remote configuration eliminates contention for machine resources between the Report Server and the SQL Server hosting the catalog. However, you must provide adequate network bandwidth between the Report Server and the catalog server.

Scale-Up and Scale-Out

After you split the catalog to another system, you can choose to either scale up the Report Server, by adding processors, or scale out by adding machines. Figure 4 illustrates a scale-out configuration that uses multiple Report Servers to access a single catalog."


After you get a grasp at this level, the next step would be to go a little further with the when to scale out, and there is another article found here: Reporting Services Scale-Out Architecture



That one goes into more detail about the scale out architecture, and also covers the max number of concurrent users SSRS can handle, and how this increases when your scale out.

After getting this under your belt, the next level would be to understand the comparison between SQL Server 2005 and SQL Server 2008 SSRS. For that, a very detailed analysis was done over at SQLCat.com. The article for that can be found here: Scaling Up Reporting Services 2008 vs. Reporting Services 2005: Lessons Learned

From the post:
"Http Errors vs. Request Time-Out Errors

To better understand the hardware limits we hit with Reporting Services 2008 compared to Reporting Services 2005, we compared the HTTP 503 errors we saw with Reporting Services 2005 with the Reporting Services time-out errors that we saw with Reporting Services 2008. We discovered that while the failure rate for reports with Reporting Services 2005 was directly correlated with HTTP 503 errors, the failure rate for reports with Reporting Services 2008 was directly correlated with time-out errors. These time-out errors, combined with our analysis of the disk queuing on the data source, indicate that the data source was unable to keep up with the request for data from the front-end servers."


So This is all very important to understand, or have someone udnerstand who will be helping you get the best out of your SSRS part of your overall Dynamics AX 2009 solution.

Well check back soon, performance tunning Dynamics AX 2009 will be getting a lot of focus from my post. I will also be adding another Dynamics AX BI pratical post, focusing this time on making use of OLAP Cubes, and creating a custom cube for use within your Dynamics AX 2009 instance.

See you then!




"Visit the Dynamics AX Community Page today!"

MFP: AX6 sneak preview - SQL AOD

Well MFP is giving us a sneak preview into AX 6.0 milestone. The AOD files are 'a changing'.

Check out the post here: MFP Blog Post: 'AX6 sneak preview - SQL AOD'

From the post:
"Today we built the first official build of Dynamics AX ever that does not run on AOD files. Starting today Dynamics AX's model database is stored in a Microsoft SQL Server database."

That's right, the Model Database will now be in a SQL Server Database.

More from the post:
"So far we have aimed at providing 1-to-1 parity with AOD files; but the potential of this is amazing - I hardly know where to start...

-Performance can be further improved as the subsystems querying the meta data store starts levarging the powers of SQL. For example; the ISAM database used for the AOD files (and still used by temp tables) doesn't support joins. Due to this many subsystems are overly chatty when reading meta data as they query row-by-row.

-Solving the element ID problem is now possible, as we can leverage the flexibilty and powers in SQL for modifying and extending the model schema.

-Capabilities... There are so many cool scenarios we can enable - but you'll have to wait for an sneak preview after the next milestone to learn more. But to get you thinking: What deployment capabilties did AOD files lack?"

My gears are already turning on how much better this will make the overall performance of the entire system work. True indexed model data, speed at the SQL level, this is for sure the right move.

I can't wait to hear more, thanks MFP for sharing and getting this out there.




"Visit the Dynamics AX Community Page today!"

Blog Spotlight: SQLSkills.com - Kimberly L. Tripp

Convergence is wrapping up as I type, with most people thinking about getting back home and getting a little rest. There has been a lot of good information coming out from convergence, so look for my wrap up next week on that.

Now, as promised, I am getting back to some SQL post. This time, what I wanted to do was actually point out, and do a Blog Spotlight for a great SQL resource. I am talking about Kimberly L. Tripp from SQLSkills.com

Her Blog can be found here: Kimberly's - improving my SQL skills, through your questions - blog

She is just a great resource, and post on a regular basis. She covers internals well, and has a post up right now talking about database maintenance best practices. This post is Part I of a series she is doing right now, and I recommend you check it out.

I also recommend you read back through some of her other post. This is a great resource for all around SQL Server skills, how-to and information. This is not directly related to Dynamics AX, but these skillsets and information that you can learn from her post apply to the Databases and Database Servers that Dynamics AX instances run on.

So thanks Kimberly for doing such a great job over at SQLSkills.com and please keep the good posts coming!

Check back here for more, as I do a wrap up of convergence, continue on the SQL Server 2008 path, book reviews, and much more! See you then!




"Visit the Dynamics AX Community Page today!"

SQL Sever 2008 - Performance with Dynamics AX 2009 - Resource Governor

Alright, so lets get back to the SQL Server 2008 and how it's new features can be used with Dynamics AX 2009 to help with the performance of an instance.

Now with most any instance, the key to performance is in understanding query execution, query plans, and being able to tune these with indexes, and such. This of course is after you have done your benchmark and made sure you have enough RAM, CPU and HD Spindels running for you and correctly running.

These still apply to a DAX 2009 / SQL Server 2008 instance. However as I mentioned before, there are a good bit of new features in SQL Server 2008 to really help an instance out.

On this post, let's look at the: Resource Governor

So lets think of a make believe instance of DAX 2009. Just deployed, with EDI at this company, MRP, and nightly integrations flowing out of DAX 2009. Sounds typical these days for a lot of instances.

Ok, so you have all of this running, you do the normal SQL performance tunning and you notice that the nighlty integrations that flow out of DAX are causing some delays to the MRP process and also the same thing happens all the time with the EDI transactions and jobs that are being ran.

So we have run away query's going on here, and what can we do about it?

Well in SQL Server 2008, the resource governor can be used. This is the first version of SQL Server that you can now differentiate between workloads! What a concept huh?

So now you can take the resource govenor, create resource pools and workload groups, even policies that make use of classifier functions, to limit how much certian, specific incoming spids and their workloads can have access to the SQL Server's resources.

So you can make your MRP and normal DAX SQL traffic have higher priority to SQL Server resources, while placing the integrations and EDI into a resource workload group and pool that could limit the max amount of resources those could ever have. This way you can manage these, and stop these run away queries from ever hurting your normal day to day or high priority Dynamics AX processes.

Of course you should do this anyway, and then get with the EDI vendor and the development of the integrations so they can improve their processes to work better with the limited resources. ;-)

So the resource governor is something totally new to SQL Server 2008, and is a new tool that we can use to help manage and improve our Dynamics AX 2009 instances with.

Check back soon for more!




"Visit the Dynamics AX Community Page today!"

Dynamics AX - Why it's good: Value point two, "The Stack"

To continue our talks here about why Dynamics AX is good, and even more so in a recession, lets look at the next value point:

The Stack
This is refering to the stack of software that Dynamics AX sits on top of, is a part of and is surrounded by.

Lets look at what Dynamics AX sits on top. If you look at the core parts that make up Dynamics AX: Fat Client, Web Client & Parts, AOS & Database.

Fat Client
This is the normal, everyone is used to using standard front end to the Dynamics AX solution. This client is actually being moved more and more into managed code, but as of right now is built using C++ for it's core. These C++ codes are developed using Microsoft windows core Libs and OS abilities. So the Fat client sits on top of the core Microsoft Windows OS.

This client also has the ability to interact, via X++ Code calls, to .Net assemblies, therefore openning up to the side stack that runs as part of the client experince.

Web Client & Parts
Use to, this was just called web client in my mind, but that has changed with the release of Dynamics AX 2009. This is because Role Centers are being used, and Microsoft Office SharePoint Services are being used to host the Web client and it's many possible and customizable web parts.

This is extending the to the next level of information worker that is actually becoming more commonly used than the Fat client. This, as I already mentioned sits on top of Microsoft Office SharePoint Services, which rides on top of the Microsoft WIndows Server OS.

This part also has access to .Net assemblies / managed code and therefore can be really extended.

AOS or Application Object Server
This is the serving part of the Dynamics AX solution. A company may have many AOS instances working to serve a single Instance of Dynamics AX based on user demand and size. This part is also written right now in C++ but more and more is being ported to be wrote in managed code, or better refered to as .Net.

This sits on top of Microsoft Windows Server OS, and has access to the .Net assemblies / managed just like the others mentioned so far. This means it too can really be extended, even beyond the core possibilities of X++.

Database
This is the heart and soul of a Dynamics AX instance. If you have this running right, tunned and optomized, then you have happy users. This runs on top of Microsoft SQL Server, which itself runs on top of Microsoft Windows Server OS.

With the release of Microsoft SQL Server 2005, SQL Server from Microsoft became a true enterprise level RDBMS. This is the work horse, and offers all kinds of things from just being SQL Server, including: OLAP, BI, SQL Server Reporting Services (SSRS) and much more.

As you can see, just looking at the core parts of Dynamics AX and what it sits on and is closely coupled with, this is truly a business platform. The great thing is, it can be extended, customized and used by people who are already familiar with the Microsoft product stack. That means as a customer, you can take and capitalize on your existing employees knowledge of SQL Server, Windows, .Net, C# SharePoint, Office and so on.

This Value point builds on top of tha last, of being Dynamic, in that the stack is very important. It's important for: Value, Cost, Ability to learn, Flexibility and scalability.

All those are key words into why the stack should matter, and why it's our second value point of discussion for Dynamics AX as a Business Platform.

This leads into our next topic: Platform Check back soon, and see you then!




"Visit the Dynamics AX Community Page today!"

SQL Quick tip - OpenQuery

Alright, it's been a while since I had to do this, but just in case. If you are in an implementation and you happen to be moving data from Oracle or and AS/400 and want to query the tables and views, first:

• Take and create a linked Server, with the correct provider to the database.


• Make sure to make use of the openquery syntax

The syntax for openquery goes: select * from openquery(Linked_Server,'Select Statement')

This will save a lot of pain, and allow the other server to handle servering up table attributes, data types and the likes that will cause issues if you try a standard linked server, FQN statement. :-)

Check back soon!

Find a job at: www.DynamicsAXJobs.com

X++ Team Blog Entry - SQL Imporvements in next version

In review of some other Dynamics AX and X++ blogs I came back across the X++ development team blog. When I did, I came across a very interesting article on improvements for X++ SQL in the next major release of Dynamics AX 5.0. Check out the link below:

X++ Team, SQL Improvements write up

With this, we can see some good moves, that will really help X++ developers work with more T-SQL like statements from within X++. The thing that is always a little scary about these types of changes is backwards compatability. So hopefully they are taking the time with these improvements to make sure that the upgrade takes care of this, or that it will still work in the old fashion. Either way though, I believe this is a good move, and a great resource of a blog!

check back soon!


Find a job at: www.DynamicsAXJobs.com

Katmai and you!

With the next release of SQL Server code named Katmai, due next year and Dynamics AX 5.0 to follow right behind, its time to start thinking about what this means for the Dynamics AX eco-system.

Basically if you read up on Katmai, one of the major goals is Business Analaysis, and tighter integration with the Office 2007 platform, including SharePoint. For Dynamics AX, this means more out of the box ability for Ad-Hoc report generation from Excel, with Dynamics AX data. What comes to mind here is the Business Data Catalog. This will enable Report Models (refernce current perspectives in DAX 4.0) that will allow an end user to select from a given report model set, and drag and drop into Excel, SSRS, and work with that via SharPoint.

Ok maybe I am getting ahead of the ability curve here, but it will be close. This also means that the Business Scorecard manager will become more web part based, and start to turn into a RIA (Rich Interactive Application) experince hosted via SharePoint. This also means the business analysis in Dynamics AX will have more interactivity and interaction abilities with Excel 2007, SharePoint, etc.

So when we look at the overall picture, one of the big pushed for Katmai SQL Server, and Dynamics AX 5.0 is tighter integration with the Office 2007 platform. This means for the information worker, more and more job role responsibilities can start to live back into the Office domain, and less dependant on having to live and work inside Dynamics AX client itself.

Well check back soon!

Find a job at: www.DynamicsAXJobs.com