Setting up RapidStart for AX2012

I have just recently starting to work with Microsofts Dynamics RapidStart Services, and I would like to share my hints and tips for getting it up and running successfully - from a technical point of view. I have still some ground to cover, so I'll blog some more when I get there.

If you don't know what RapidStart Services is, then you're missing out. To put it short, the idea is to have a web site holding the parameters and data. These data can be maintained by the customer and the partner. This is a great tool for doing implementation projects, but it can also be used for quickly creating a demo or development environment. While the current version doesn't cover ALL possible parameters and data entities, the framework covers a whole lot.

So let's get started!

These are the major components in this installation:

• A fully working AOS with Microsoft Dynamics AX 2012. All code compiled without error and CIL compiled successfully. 
• The latest version of Data Import Export Framework. Installed and configured. Download from InformationSource. 
• Latest version of the ERP RapidStart Connector. Download from CustomerSource or PartnerSource.(direct link)
• A browser with Silverlight addon. Yea, Internet Explorer does work. 
• A dedicated user in your Active Directory.

Service Account

Start by making a dedicated account. Don't use your own, or you will break the connector when you change your password. When installing the connector you will be asked to provide the username and password for this account, and the service installed will run under this account. You also need to add this account as a user in AX. Give it full access rights, unless you want to spend time figuring out which roles to chose. There are no built-in "Rapid Start" role.

Since this will be a service account user, you will also have to add this user to the Local Policy rules with "Log in as a service".

You will also find the log file in this users application data folder (example C:\Users\daxrapid\AppData\Local\Temp\Microsoft.Dynamics.Framework.RapidStart.Connector\8.2.327.0\RapidStartConnector.log).
This log will be extra helpful for debugging why the Connector fails, if that happens.

ERP RapidStart Services Connector

Install the ERP RapidStart Services Connector. At the time of writing, I am running version 8.2.327.0. Head over to "Programs and Features" and make sure you don't have any older versions installed. There should only be one, and make sure you have the most recent one. You can uninstall and install this component as many times you need. This components must be installed on the same machine as the AOS. Maybe they will change this later on, but right now it only works if it sits on the server running the AOS. I've read that it will require one Connector for each AOS you want to connect to, but I will test that further.

Data Import Export Framework

You need this installed and prepared for the connector to be able to push and pull data. Installing this component is a topic on its own. Right now, this model gets installed in the FPK-layer, so it should be relatively easy to get it in. Again, make sure your AOT compiles without errors.

You need to manually enable one of the Service Groups after installing the model. This Service Group has its "AutoDeploy" set to "No", so you will have to manually deploy and verify it is active. The services needed for the connector to work are:

• AifGDS
• AppConfigServices
• MetadataService
• QueryService
• DMFService

Except for the Metadata- and QueryService, you will need to check these and make sure they are running.

You also need to create a special Source Data Format and choose it as your format when using the ERP Connector. The format should be delimited file, first row active, row by CR+LF and column by comma. Head over to Parameters and clear out "Ignore Errors" as well.

Ready to Connect

When you start the ERP RapidStart Services Connector the first time, it will be without any Activation ID (GUID). Fill in the servername and the WSDL port. Head over to "Data Import Export Framework"-tab and select the Source Data Format you created earlier. Save the changes. Back to the "General"-tab and hit the Active-button. A link to the RapidStart page is enabled.Now you can start the Service. 

It will launch the RapidStart website where you can login using your Live-ID. After choosing a customer and a Service, you will be asked to choose which endpoint you will use. Select endpoint and hit Submit.

Next you can click "Manage Endpoint" and start creating projects or importing metadata through the ERP Connector.

Troubles in paradise?

If the service doesn't start, check the Evenlog or the RapidStart log file. Did you remember to add the RapidStart service account as user in AX? Did you give it admin access? Forgot to add the user with "Log on as Service" in Local Policy?

Is the connector being blocked by a firewall? Expect http (TCP 80) and https (TCP 443) in addition to TCP 9350-9354. Simply check by disabling the firewall. If it works, turn it back on and enable the traffic.
Did you make sure the Service Endpoints inside AX are Active?

Still having issues? Comment below, or head over to the Community Forum and get help.

Some other good sources:

• Install the RapidStart Connector
• RapidStart on TechNet
• Microsoft RapidStart Team Blog

Setting up scheduled processing of cubes in AX2012

Here is a quick guide on how you can easily and quickly setup scheduled processing of the cubes for Microsoft Dynamics AX 2012. The same approach works for other Analysis Databases, but sometimes it helps to put things in context in order to make it easier for you AX geeks out there.

I'm assuming you have setup and configured the standard Dynamics AX Analysis Database. Preferably also run a manual processing and analysed any errors or warnings. My example is from AX 2012 R2 on SQL Server 2012, and I just want to have it process without any error logging.

Start off with logging on to the Analysis Instance using SQL Server Management Studio. Find the "Dynamics AX initial" OLAP Database. If you have created multiple partitions, they will have their separate OLAP Database.

Right click and choose Process to open the processing dialog:

In the process dialog click the "Change Settings..." button:

A new windows will pop up. Head over to the tab "Dimension key errors" and click "Use custom error configuration". Choose to "Ignore error count" and define error conditions to "Ignore error". Here you can opt in for "Report and continue" and also have any issues be reported to a Log file, but do a test and pay attention to the Log filesize. It may grow rapidly, and even become several GB. 

Press OK, but don't start the processing. Now with the current setup, choose to Script the Action to a New Query Window:

The Action will be scripted as a XML structure. We will now use this command in a SQL Server Agent Job.

Head over to SQL Server Agent and create a new Job:

On the section for Steps, add a new Step:


The new Step needs to be of type SQL Server Analysis Service Command. It will be run under the SQL Server Agent Service Account, so make sure this service account has Read access to the Dynamics AX Transaction Database. Paste in the XML command from previous steps and save the step by pressing OK:


Now you can chose the Schedule section and create a new schedule for this job. You may chose to have it process on a daily basis, or multiple times per day. Choose whatever is suitable for your needs.

Finally, do a test of the Job and make sure it runs through. If it doesn't, use Job History to find the cause of any errors. Feel free to comment or ask, if you get stuck.

Thanks for reading!

Review of Implementing Microsoft Dynamics AX 2012 with Sure Step 2012

First and foremost; this is a very good book! I had the pleasure of reading through Implementing Microsoft Dynamics AX 2012 with Sure Step this weekend, and while I initially planned to just read the chapters and topics related to my focus in Dynamics AX projects, it was quite interesting to read the other chapters as well. It is clear that the authors have a lot of experience with Dynamics AX as a product, in addition to delivering implementation of this huge enterprise solution.

The book covers a lot of ground, and contains a bunch of great tips for a successful project deliverance. Sure there are a lot of topics that you would think were common knowledge (or sense), but anyone who has participated in implementing AX 2012 knows there are a lot of pitfalls compared to previous versions of AX. The book covers them very well. The width of the content makes it an excellent book for all roles and participants in an implementation of AX 2012. It is not too technical, but still have a fair amount of valuable details.

I would highly recommend this book for all team members, not just the decision makers or project leaders. You could even argue the customer should have a copy. ;-)

My favorite chapters were Planning the Infrastructure (3), Installing the AX Environment (4), Integration (7), Designing and Developing the Solution and Reports (10). So that is almost half the book already. But again, chapters like AX Setup (6), Harnessing the Power of Standard AX Features (8) and Designing and Developing the Solution (9) couldn't be left unread.

The book does contains content on good project methodology, but I read it more like good advice and valuable experience for a successful implementation. Some sections could easily be found in books like Inside Microsoft Dynamics AX 2012, like explanations on application code layer and deployment on modelstores vs models. Actually, even if your company uses a different implementation methodology, this book is a great asset. That is also why I found it easy to read and very interesting.

So to wrap it up, if you are a part of a team implementing Dynamics AX 2012, have someone get a copy of this book. Read the chapters covering your part of the delivery, and before you know it, you have read the other chapters as well.

At the time of writing, the eBook is sold for just £15. Affordable, if you ask me.

Good job, Keith Dunkinson and Andrew Birch.

Create a new Baseline Database for AX2012

The Baseline database in AX2012/AX2012R2 is optional when installing the database(s). During installation of the databases, this is the only point it gets installed. So what if you need to setup a Baseline database after having installed AX2012? If you have a Baseline database laying around, you could restore it - and hope the schema is valid for the version of AX you are working on.

There is another easy method. :-)

Start by creating a new database, an empty database. I'm giving it around 2GB of space in my example.

Set the recovery mode to Simple. You don't need point-in-time recovery for this database.

Then run the PowerShell command Initialize-AXModelstore on the database.

Observe the database now has the elements required for a modelstore.

Make sure you add the necessary users with proper access, so they can use the new Baseline database.

Finally, head over to the AOS Server Configuration and choose the Baseline database.

That is it. :-)

Remove old modelstore from an upgraded AX2012 R2 database

One of the benefits of AX2012 R2 compared to AX2012 RTM is the fact that they decided to split the database in two parts, one part for the business data and one part for the application data. This makes it a lot easier to copy business data from production to test without overwriting the application data in test. Sure, there were ways around this in RTM, but you can't deny it is a lot easier to do this now. Another benefit is that you no longer have the application (modelstore) residing in the business database. This means you don't need to have a full backup with point-in-time recovery for the production application. The application in RTM would be aroud 2.5 GB exported, but inside the database you would have huge indexes and also the compiled assemblies stored. All of these data are now stored in the application database in R2. I would argue that there is no need to have the recovery mode set to "Full" on these databases containing the application. I mean, each time you import a modelstore, have a look at the database log. It will grow huge.

Inspired by a response from Kevin Kidder at the Community Site, I decided to try make a Stored Procedure that removes the modelstore elements inside the database. Getting rid of that old modelstore from RTM that now sits in the business database after successfully upgrading to R2 will reduce the size of the database with a couple of GB, so why not.

I found this nice script to remove all schema elements, and used is as a base for my script. I also initialized a new modelstore for the purpose of testing my script. Actually, the SQL scripts being used for creating a modelstore is available as resources in the AxUtilLib assembly. You can study them by using any tool that can revese engineer a .Net dll.

This procedure might not be flawless, so use it with care. Obviosuly; take a backup and test. By default the procedure will print out the SQL statements you need to run in order to remove all the modelstore elements in the database. You will have to explicitly pass "w" for it to execute the statements for you. You will have to decide if you want to evalute the output and run it manually, or let the procedure do it all.

Please report feedback and/or errors back to me. :-)

Here it is:

CREATE PROCEDURE [DBO].[CleanUpModelStore]
(
  @WORKTEST CHAR(1) = 't'  -- use 'w' to work and 't' to print
)
AS
/*-----------------------------------------------------------------------------------------
 
  Author : Tommy Skaue
  Date:    06.04.2013
 
  Description: Drops all modelstore elements.
  Usage and License: Free and at own risk
  Parameter: t = test/print out
             w = work and execute statements
    
  Report any errors or feedback to tommy@skaue.com.
  
  Inspired by the script made by Ranjith Kumar S
  http://ranjithk.com/2010/01/31/script-to-drop-all-objects-of-a-schema/
 
-------------------------------------------------------------------------------------------*/
BEGIN   
 
DECLARE @SQL VARCHAR(5000)
DECLARE @MODELSTOREELEMENTQUERY VARCHAR(2500)
DECLARE @MSG VARCHAR(500)
DECLARE @SCHEMANAME VARCHAR (50) = 'DBO'
 
IF OBJECT_ID('TEMPDB..#DROPMODELSTORE') IS NOT NULL DROP TABLE #DROPMODELSTORE
CREATE TABLE #DROPMODELSTORE
(
   ID INT IDENTITY(1,1)
  ,SQLSTATEMENT VARCHAR(5000)
 )
 
-- Common filter for ModelElements
SELECT @ModelStoreElementQuery = 
 'AND (T.NAME LIKE ''Model%'' OR 
  T.NAME IN (
   ''axIdAllocsTable'',
   ''AxIdAsk'',
   ''existingPaths'',
   ''Sources'', 
   ''newPaths'',
   ''SourceMerge'',
   ''SYSXPPASSEMBLY'', 
   ''ElementTypes'', 
   ''SchemaVersion'', 
   ''Layer'', 
   ''LayerVersioning'',
   ''GlobalFieldIdPool''))'
 
-- removes all the foreign keys that reference a PK in the target schema
 SELECT @SQL =
  'SELECT
       ''ALTER TABLE ''+SCHEMA_NAME(FK.SCHEMA_ID)+''.''+OBJECT_NAME(FK.PARENT_OBJECT_ID)+'' DROP CONSTRAINT ''+ FK.NAME
  FROM SYS.FOREIGN_KEYS FK
  JOIN SYS.TABLES T ON T.OBJECT_ID = FK.REFERENCED_OBJECT_ID
  WHERE T.SCHEMA_ID = SCHEMA_ID(''' + @SCHEMANAME + ''')
    AND FK.SCHEMA_ID <> T.SCHEMA_ID
    ' + @MODELSTOREELEMENTQUERY + '
  ORDER BY FK.NAME DESC'
 
 --IF @WORKTEST = 't' PRINT (@SQL )
 INSERT INTO #DROPMODELSTORE
 EXEC (@SQL)
  
 -- drop all default constraints, check constraints and Foreign Keys
 SELECT @SQL =
 'SELECT
       ''ALTER TABLE ''+SCHEMA_NAME(T.SCHEMA_ID)+''.''+OBJECT_NAME(FK.PARENT_OBJECT_ID)+'' DROP CONSTRAINT ''+ FK.[NAME]
  FROM SYS.OBJECTS FK
  JOIN SYS.TABLES T ON T.OBJECT_ID = FK.PARENT_OBJECT_ID
  WHERE T.SCHEMA_ID = SCHEMA_ID(''' + @SCHEMANAME + ''')
   AND FK.TYPE IN (''D'', ''C'', ''F'')' + @MODELSTOREELEMENTQUERY
  
 --IF @WORKTEST = 't' PRINT (@SQL )
 INSERT INTO #DROPMODELSTORE
 EXEC (@SQL)
 
 -- Common filter for ModelElements
SELECT @MODELSTOREELEMENTQUERY = 
 'SELECT OBJECT_ID FROM SYS.OBJECTS SO2 WHERE 
  SO2.PARENT_OBJECT_ID IN 
  (
   SELECT SO3.OBJECT_ID FROM SYS.OBJECTS SO3 WHERE SO3.TYPE IN (''U'') AND 
   (
    SO3.NAME LIKE ''Model%'' OR 
    SO3.NAME IN (
     ''axIdAllocsTable'',
     ''AxIdAsk'',
     ''existingPaths'',
     ''Sources'', 
     ''newPaths'',
     ''SourceMerge'',
     ''SYSXPPASSEMBLY'', 
     ''ElementTypes'', 
     ''SchemaVersion'', 
     ''Layer'', 
     ''LayerVersioning'',
     ''GlobalFieldIdPool'')
   ) 
  )
  OR
  (
   SO2.TYPE IN (''U'') AND 
   (
    SO2.NAME LIKE ''Model%'' OR 
    SO2.NAME IN (
     ''axIdAllocsTable'',
     ''AxIdAsk'',
     ''existingPaths'',
     ''Sources'', 
     ''newPaths'',
     ''SourceMerge'',
     ''SYSXPPASSEMBLY'', 
     ''ElementTypes'', 
     ''SchemaVersion'', 
     ''Layer'', 
     ''LayerVersioning'',
     ''GlobalFieldIdPool'')
   )
  ) 
  OR
  (
   SO2.TYPE IN (''V'') AND 
   (
   SO2.NAME LIKE ''SysModel%'' OR
   SO2.NAME LIKE ''Util%''     OR
   SO2.NAME LIKE ''Model%''    OR
   SO2.NAME IN (''ConfigurationKeys'', ''LicenseCodes'', ''Origins'',''SECURABLEOBJECT'') OR
   SO2.NAME IN (
    ''SECURITYENTRYPOINTLINK'',
    ''SECURITYPERMISSION'',
    ''SECURITYROLE'',
    ''SECURITYROLEEXPLODEDGRAPH'',
    ''SECURITYROLEPERMISSIONOVERRIDE'',
    ''SECURITYROLETASKGRANT'',
    ''SECURITYSUBROLE'',
    ''SECURITYSUBTASK'',
    ''SECURITYTASK'',
    ''SECURITYTASKENTRYPOINT'',
    ''SECURITYTASKEXPLODEDGRAPH'',
    ''SECURITYTASKPERMISSIONOVERRIDE''
    )
   )
  )
  OR
  (
   SO2.TYPE IN (''TR'') AND
   SO2.NAME IN (
    ''ModelSecurityPermission_CreateSecurable'',
    ''UpdateChangedBy'',
    ''SetInstalledAndChangedBy'',
    ''UpdateModelFromManifestChangedBy'',
    ''IO_Trig_INS_ModelElement'',
    ''IO_Trig_INS_ModelElements'',
    ''IO_Trig_Del_ModelElements'',
    ''ModelSecurityCommon_Insert''
    )
  )
  OR
  (
   SO2.TYPE IN (''P'') AND
   (
   SO2.NAME LIKE ''XI_%'' OR
   SO2.NAME LIKE ''XU_%''
   )  
  )
  OR
  (
   SO2.TYPE IN (''FN'', ''TF'',''IF'',''FS'',''FT'') AND
   SO2.NAME IN (
    ''CreateTemplateName'', 
    ''GetLayerMask'',
    ''GetNextAvailableFieldOrIndexAxId'',
    ''GetNextAvailableAxId'',
    ''GetAxIdHole'',
    ''IsAxIdExcluded'',
    ''SECURITYROLE_FUNC'',
    ''SECURITYTASK_FUNC''
   )
  )'
 -- drop all other objects in order  
 SELECT @SQL =  
 'SELECT 
 CASE 
  WHEN SO.TYPE=''PK'' THEN ''ALTER TABLE     ''+SCHEMA_NAME(SO.SCHEMA_ID)+''.''+OBJECT_NAME(SO.PARENT_OBJECT_ID)+'' DROP CONSTRAINT ''+ SO.NAME
  WHEN SO.TYPE=''U''  THEN ''DROP TABLE      ''+SCHEMA_NAME(SO.SCHEMA_ID)+''.''+ SO.[NAME]
  WHEN SO.TYPE=''V''  THEN ''DROP VIEW       ''+SCHEMA_NAME(SO.SCHEMA_ID)+''.''+ SO.[NAME]
  WHEN SO.TYPE=''P''  THEN ''DROP PROCEDURE  ''+SCHEMA_NAME(SO.SCHEMA_ID)+''.''+ SO.[NAME]         
  WHEN SO.TYPE=''TR'' THEN ''DROP TRIGGER    ''+SCHEMA_NAME(SO.SCHEMA_ID)+''.''+ SO.[NAME]
  WHEN SO.TYPE IN (''FN'', ''TF'',''IF'',''FS'',''FT'') 
                         THEN ''DROP FUNCTION   ''+SCHEMA_NAME(SO.SCHEMA_ID)+''.''+ SO.[NAME]
 END
FROM SYS.OBJECTS SO
 WHERE SO.SCHEMA_ID = SCHEMA_ID(''' + @SCHEMANAME + ''')
  AND SO.TYPE IN (''PK'', ''FN'', ''TF'', ''TR'', ''V'', ''U'', ''P'')
  AND SO.OBJECT_ID IN (' + @MODELSTOREELEMENTQUERY + ')
ORDER BY 
 CASE 
  WHEN TYPE = ''PK'' THEN 1
        WHEN TYPE IN (''FN'', ''TF'', ''P'',''IF'',''FS'',''FT'') 
                     THEN 2
        WHEN TYPE = ''TR'' THEN 3
        WHEN TYPE = ''V''  THEN 4
        WHEN TYPE = ''U''  THEN 5
        ELSE 6
    END'
 
--IF @WORKTEST = 't' PRINT (@SQL )
INSERT INTO #DROPMODELSTORE
EXEC (@SQL)
 
DECLARE @ID INT, @STATEMENT VARCHAR(2000)
DECLARE STATEMENT_CURSOR CURSOR
FOR SELECT SQLSTATEMENT
    FROM #DROPMODELSTORE
    ORDER BY ID ASC
    
 OPEN STATEMENT_CURSOR
 FETCH STATEMENT_CURSOR INTO @STATEMENT
 
 WHILE (@@FETCH_STATUS = 0) BEGIN
  IF @WorkTest = 't' 
   PRINT (@statement)
  ELSE
   BEGIN
    PRINT (@statement)
    EXEC(@statement)
   END
  
  FETCH STATEMENT_CURSOR INTO @STATEMENT    
 END
 
 CLOSE STATEMENT_CURSOR
 DEALLOCATE STATEMENT_CURSOR
 
 PRINT '------- ALL - DONE -------'   
END
GO

Hope this helps someone.

Simple way to recover lost columns

Easter vacation is almost over and I felt like posting a new blog entry.

This will be something even a novice DBA would know about, but hopefully it might be helpful for a Dynamics AX developer who isn't too familiar with SQL - but wants to learn.

I once had to help someone get back a couple of columns that were lost due to a flawed client cache (apparently). This may also easily happen if someone accepts a database synchronization which creates data loss of specific columns.

The solution is quite easy. A DBA or developer with some SQL Server experience can prepare a restored database sitting on the same SQL Server Instance, or create a Linked Server for the purpose of this restore.

When the database is ready, make sure to keep users away while you restore the data. It should be a matter of minutes. I restored 3 columns in around 800 000 rows in just a few minutes. Keeping users away is just a precaution. SQL Server will by itself make sure the entire update either fails or succeeds.

Here is an example SQL query to restore the columns:

BEGIN TRANSACTION RestoreColumnData WITH MARK N'Restoring Column Data';
GO

UPDATE     SalesTableInTarget
SET        SalesTableInTarget.CustomColumn1       = SalesTableInSource.CustomColumn1 ,
           SalesTableInTarget.CustomColumn2       = SalesTableInSource.CustomColumn2 ,
           SalesTableInTarget.CustomColumn3       = SalesTableInSource.CustomColumn3
FROM       DynamicsAXTarget.dbo.SALESTABLE            AS SalesTableInTarget
           INNER JOIN DynamicsAXSource.dbo.SALESTABLE AS SalesTableInSource
           ON         SalesTableInSource.RECID = SalesTableInTarget.RECID
GO

COMMIT TRANSACTION RestoreColumnData
GO

The query simply joins the target table and the source table based on the RecId,which should be unique. I chose to mark the update in a custom transaction. 

You can find multiple examples of similar queries on the web, but sometimes it helps to put these things in our AX context.

Hope this helps someone.