AX 2012 Simple Reporting Database

Creating a database to hold simple but powerful reporting shortcuts can help provide data on demand to executives and department heads when a unique or surprising issue arises. There are lots of BI and reporting products out there for AX but this will just cover setting up something very simple to (hopefully) make life a little easier for your DBA's, Developers or report writers. First, lets talk about why someone would do this. You could build (or buy) a data warehouse where everything has a place and everything is in its place. Performance is great and its simply a matter of picking what you want and how you want to see it. Alternatively, you could build a data mart. Like a data warehouse but smaller with fewer options and less overall offerings. Finally, what I am proposing we make is something like a convenience store, or C-Store, for our data. It would offer very little data, minimally organizing by specific area with a (relatively) high cost of getting it. Much like a C-Store in real life where you pay $2.50 for that chocolate bar that is $1 everywhere else, our database will have a higher cost be getting stuff but it will be convenient. This would be a short term or ad hoc solution until a more enterprise solution is made available. I am assuming you have admin privileges on a DB server somewhere with an AX transactional database on it.

• First, make a database. Not that difficult. Call it something like AXReportsCStore or whatever you like.
• Next, we will have to synonym tables and views from the AX transactional database into our new database.

 

-- only use inside AX transactional db 
DECLARE @varAXDatabase VARCHAR(30) = Db_name() 

SELECT 'CREATE SYNONYM [dbo].[' + table_name 
       + '] FOR [' + @varAXDatabase + '].[DBO].[' 
       + table_name + ']' 
FROM   information_schema.tables 
UNION 
SELECT 'GO' 
ORDER  BY 1 

 

• This will create SQL you can copy/paste into another query windows in SSMS pointing at the database you just made. Execute it to create all the synonyms.
• Once you have synonyms, you can interact with tables in views in AXReportsCStore just like you were in the AX transactional database

Why make a second database for this? Just to keep things separated and organized. I will be posting some views that provide some common data requests for AX as well as some pretty cool views to help with and simplify financial reporting. You can find them here.