Dr. BI Tip: SQL Server 2008 R2 & Report Builder 3 for AX 2009

As the title of this post suggest, we have Dr. BI, Michael Stashwick back for another round of tips, tricks and information around reporting with Microsoft Dynamics AX 2009 / BI.

For Michael's latest article below, he dives into SQL Server 2008 R2 and the benefits of Report Builder 3 for Ad-Hoc reporting use. This tool is great, and as he will show has great improvements over earlier versions. He also talks us through how to upgrade to Report Builder 3 with SQL Server 2008 R2.

---



I can’t think of any single subject clients ask me about more than self-service Business Intelligence (BI) and ad hoc reporting. There seems to be no ceiling to the demand that has increased daily for the past few years, and it’s mostly due to the very robust BI stacks available.

Unfortunately, the primary ad hoc tool that comes packaged with Dynamics AX, Report Builder 1.0, is a bit archaic and leaves users underwhelmed. Fortunately, the latest version, 3.0, is rich with functionality and pleases every super user that I’ve introduced it to.

Implementing Report Builder 3.0 requires SQL Server 2008 R2, and the upgrade path, which I’m going to discuss in this article, should be anything but intimidating.

To begin, there is a free downloadable Upgrade Advisor that removes all compatibility guesswork. After running the Analysis Wizard against all installed SQL Server components, the Upgrade Advisor Report Viewer will provide a list of all issues with links to MSDN for assistance. Even in the most perfect situation, there will always be one issue--the custom extensions and assemblies from the AX installation.



This is nothing to worry about and should be expected. Once all else checks out, run the R2 upgrade executable. After a successful upgrade, the first logical verification step is to run Report Manager and a deployed report.

Immediately accessing Report Manager will result in a cryptic error message that you’ve probably never seen before:

"The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version."

After giving the computer a verbal snarky response, gather more meaningful information by logging into Report Server. You will get an error message that the ‘Microsoft.Dynamics.Framework.ReportsExtensions’ could not be loaded. This is exactly what the Upgrade Advisor warned of in the beginning of the procedure.



The reason this happens is that in the upgrade process, a new sub-folder is made, as opposed to files being appended, in your Microsoft SQL Server directory. The necessary information to run AX Reporting Extensions can’t be located due to this move.

The simplest fix is to re-install Reporting Extensions from your AX install media. Of course, it must first be uninstalled before it can be re-installed.

There is one last step in upgrade process. If you tried to run a report now, it would fail as the system would be “unable to find the file specified.”



Because of this, all reports must be deleted and re-deployed to the Report Server. This realization made me so happy to have Report Libraries in AX 2009, because the countless number of reports, including any custom AX 2009 SRS reports, can be deployed in a single action.

Now, Report Builder 3.0 will launch by default when accessed by Report Manager. The additional functionality provided (compared to 1.0) is staggering, and can be seen in the MSDN article, Comparing Report Authoring Environments. Not every piece of functionality may be relevant to AX, but the UI is grossly improved with the familiar ribbon interface, and more in-depth querying is available.


---

I would like to thank 'Dr. BI' for taking the time and sharing with us yet another reason why Microsoft Dynamics AX is so powerful.: It's Continued use of the rest of the Microsoft Stack

Also, this ties right into the use of tools like Globe Software Atlas, and Microsoft PowerPivot (Microsoft Dynamics AX 2012 and the importance of PowerPivot) for Ad-Hoc and Self-Service BI needs.

That's all for now, till next time!

"Visit the Dynamics AX Community Page today!"

Microsoft Dynamics AX 2012 and the importance of PowerPivot

As my continued series on Microsoft's next release of AX, Microsoft Dynamics AX 2012, I wanted to take and focus around Microsoft Office PowerPivot.



I first wrote about this topic back in May of 2009, which you can find here.: The Future of Microsoft BI: Project 'Gemini'

In that article, I attempted to explain the benefits and purpose of what then project Gemini, and now that we know it as PoverPivot, will have for Dynamics AX.

Today, with Microsoft Dynamics AX 2009 and the Office 2010 system PowerPivot is already being used in some forms or fashion, however as I pointed out in a previous AX 2012 post, through the use of OData feeds, it will have a much bigger role. Microsoft Dynamics AX 2012, OData and EDM

Moving beyond this, lets think about what PowerPivot means for a customer. The point of PowerPivot is to enable true self service BI within a company. To allow IT depts to control, through security, access to certain datasets, published feeds, etc. that can then be handed off to power users, who can consume and work with those feeds, published cubes, etc. to perform self service BI with that given data and it's context.

PowerPivot takes the need of having every little cube type, out of the hands of a OLAP developer, and places its ability to get at the same data within the power users that need to define the model and reporting results desired.

Looking at this from Microsoft Dynamics AX 2012 point of view, we will see PowerPivot users consuming AX data, in really three ways.:

• Through published Data Services from AX Query Objects as OData feeds


• Through consuming developed OLAP cubes, that are built from AX data.


• Finally through consuming SSRS Atom feeds. (Whats new in SQL Server 2008 R2 - Section: Rendering Reports to Data Feeds)

This is very powerful stuff, because all of these ways of exposing datasets that come from Dynamics AX 2012, all interact and get their respective data through the Application layer.

Very important to understand this point, and so it's very powerful indeed. What you will have then is a Developer, or Architect create a Cube from a perspective within AX, also maybe a couple of Query Object based OData feeds, as well as some AX SSRS reports. These can then be consumed by a PowerPivot user, with proper security context and rights to perform self service BI needs.

There is a great article on Microsoft TechNet about consuming SSRS 2008 R2 Atom Data Feeds for PowerPivot.

"With the release of SSRS 2008 R2, we now have a new rendering extension to render Atom data feeds from SSRS reports. You use this extension to generate Atom-compliant data feeds that are readable and exchangeable with applications that can consume data feeds generated from reports. For example, you can use the Atom rendering extension to generate data feeds that you can then use in the SQL Server 2008 R2 PowerPivot client."

Now will these replace the need for dashboards, No! In fact these will help enable more AX mashups, that mix and match technology, that enable secure access to company data, while enabling the users tools they need to get at and work with that data.

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

Till next time!

"Visit the Dynamics AX Community Page today!"

Master Data Management with Dynamics AX and MDS (Future)

Recently I interviewed Mike Ehrenberg, Microsoft Dynamics Distinguished Engineer. That interview can be found at the following post.: Interview with Microsoft Distinguished Engineer - Mike Ehrenberg

As promised, I am taking different aspects of that interview and diving into the different topics that were talked about. In one section of the interview, Mike talks about.:

"[T]he ability to drive our innovation in this area together with innovation across Microsoft in areas like Master Data Management enables to drive great solutions through Microsoft Dynamics."

So the 'this' part, that Mike is talking about was from a question I asked around SOA, AIF, and the Vision laid out by Lachlan Cash, in an interview I did last year. (Microsoft's strategy and vision for Dynamics AX and SOA)

This is the only section, during that interview, in which Master Data Services is mentioned. However, this I believe is going to be a big item, that more and more companies, of varing sizes, will focus on.

So we can rewind a little bit, and look at a post I did, nearly a year ago, talking about SQL Server Master Data Services, which was project 'BullDog' for Microsoft, that is the enabling technology that Microsoft will soon be releasing to manufacturing as part of SQL Server 2008 R2. That post can be found here.:
SQL Server 2008 R2 Master Data Services - Aka: Microsoft MDM, Project BullDog

In that post, I go into the need and why for MDM, and what SQL Server MDS will bring to the table. I actually referenced a good bit of information from different Microsoft sources, that hopefully helps bring to light what could be possible with MDS and why.



Visit Hillstar Business Intelligence (www.HillstarBI.com) in order to truly unlock your data trapped in your Microsoft Dynamics investment. With our value driven business intelligence strategy Hillstar help you transform into a data informed company.


So now, here we are, a few days from Convergence 2010 in Atlanta, and mid-summer set for SQL Server 2008 R2 release, that will include SQL Server MDS. What does this means for Dynamics AX?

In the simplest terms MDM is meant to enable "One source of the truth." Now lets think about the role that Dynamics AX plays in a customers overall enterprise solution.

Some customers, Dynamics AX is the end all, be all business package. Older systems are migrated away from, and placed those processes and data inside Dynamics AX. When this is the case, there are still some need for MDM, and so MDS will have relevance. And how is that? Well if that given client has one or more websites / portals? What about CRM? What about a small cloud application?

In this Dynamics AX is the hub, and these other applications are the spokes systems. Now could be that an arguement could be made that CRM is the hub for certain information right?

And that is true, so depending on the data need, and where the true hub, or owenership of data should really live, will depend on how your data mapping of hub and spoke will exists.

Lets also add another possibility, what about the recent Marketing that Microsoft is doing, where Dynamics ERP can be used in a two tier level? Where Dynamics AX is positioned to be the spoke for Divisons of large corp? Also what about Dynamics AX instance that span the world? If for example there is a Dynamics AX production system in China, one in Latin America and then one in the U.S.?

In the past, for enabling integration, a lot of this was code driven. You could / can use BizTalk, along with AIF, X++, .Net, MSMQ, and any number of paths to help manage, not just the transactional data, in which these are designed to enable, but the Master data. There was not a seperation of these two, and is not, in regard to how they flow from one system to the next.

The vision, as I am seeing it, for MDS is enabling the seperation of how the Master data, true Master data elements, flow through a companies enterprise. This includes on-premise and cloud based.

So the transactional integration, now is different than the Master Data flow through a given company.

With MDS, a whole new world will open up for Master Data Management. I mean, look at the list of possibility, with versioning, workflows, business rules, role based access and listed control. Such things, in the past, where developed and not configured.

And this is the overall big move we are seeing, look back at a recent post I did, SQL Server Modeling - Coming to a Dynamics AX near you! , in that we see that SQL Server Modeling is coming to Dynamics AX, and the goal is to reduce the need for custom code, enable model driven application development, and allow for Domain Specific Languages to address problem domains for business.

Similar, we are seeing with the MDS offering, that Dynamics AX will be a point in which MDS can connect to and allow proper flow of Master data elements in and out of Dynamics AX. Some where Dynamics AX is the Hub, and some where Dynamics AX is the spoke.

A lot is coming to Dynamics AX, and very soon. Master Data Management, through SQL Server Master Data Services is one of them. For this reason, I did create a new LinkedIn group.:


LinkedIn group: SQL Server Master Data Services Group

Feel free to join if you like, and help foster the growth and knowledge of SQL Server Master Data Services. This group is not limited to Dynamics AX focus, but is meant to focus on all aspects of SQL Server MDS. However I am sure plenty will revolve around Dynamics AX, CRM, the Cloud, and beyond!

I expect that people will be talking about MDM, and you will hear about some of this at Convergence. After SQL Server 2008 R2 is released this summer, and we get closer to Dynamics AX 6, we will start to see some offical information from Microsoft, getting into more details on exactly how MDS and also SQL Server Modeling will be used with Dynamics AX.

That's all for now, but I invite you to leave comments, send me an email, and of course join the LinkedIn group and start good discussions, news, etc. See you next time!



Visit Hillstar Business Intelligence (www.HillstarBI.com) in order to truly unlock your data trapped in your Microsoft Dynamics investment. With our value driven business intelligence strategy Hillstar help you transform into a data informed company.





"Visit the Dynamics AX Community Page today!"

Microsoft PowerPivot - On the way, and use with AX

Alright,

So one of the hot topics I have talked about, over the past year is BI. This makes sense, since, with Microsoft Dynamics AX 2009, BI had a huge boost in what was offered out of the box, and platform and tools for creating BI.

As part of this coverage, I have been talking to you about Microsoft 'Project Gemini', how this will relate to Dynamics AX, and how it fits into Microsoft's vision of 'BI for the masses'.

Below is a list, in order of release, starting with the post I did in May of this year about Project Gemini.:

• The Future of Microsoft BI: Project 'Gemini'


• Dynamics AX 2009 BI: Practical use with SSRS and looking at OLAP


• MSDW: MDM vs. IDM


• More on the Future of BI with Microsoft, Dynamics AX and Project Gemini

So, now we know the actual product name for Project Gemini is PowerPivot, or Microsoft PowerPivot.



MSDN, has a great resource covering PowerPivot, which can be found here.:
PowerPivot Overview (SQL Server 2008 R2)

So what is PowerPivot, from it's base offering? MSDN puts it as.:
"PowerPivot refers to a collection of applications and services that provide an end-to-end approach for creating data-driven, user-managed business intelligence solutions in Excel workbooks.

PowerPivot integrates with Excel and SharePoint. In an Excel environment, PowerPivot for Excel provides a familiar authoring and analytical experience on the workstation. In a SharePoint farm, PowerPivot for SharePoint is the set of server-side applications, services, and features that support team collaboration on business intelligence data. SharePoint provides the platform for collaborating and sharing business intelligence across the team and larger organization. Workbook authors and owners publish and manage the business intelligence that they develop to their SharePoint sites."



So with PowerPivot, the push for BI to the Masses is actually taking shape. It's all about User Managed BI. And Excel is the UI for that BI.

Now we have all known for years, and have seen it more and more with every release of Dynamics AX, that Office is a part, and sometimes the User Interface for Dynamics AX. This will become even more true as the Dynamics AX Road Map becomes reality, and the technologies for BI, like PowerPivot are made use of with Dynamics AX.

So how will PowerPivot be used with Dynamics AX? Think Role Centers, and the dashboards that everyone loves. So web parts, that are created and make use of Excel services (enter MOSS), that are generated, published and collabarted on by end users, present in and made use of in Role Centers and EP for Dynamics AX reporting and analysis.

Look at how this works, from Querying PowerPivot Data in SharePoint.:


So that Analysis Services at the bottom of the above image, well those are the Same Analysis Services that attach to Dynamics AX transactional data, along with other possible transactional data, for anaylsis work to be performed on.

And the delivery, SharePoint! That means Dynamics AX Enterprise Portal, Role Center Web Parts, and Published PowerPivot workbooks, and Excel parts that live on your Exdecutives Dashboard for reporting and company wide information and drill down knowledge.

So can you see this taking shape, BI for the masses, and Dynamics AX? Notice the required technologies.: SQL Server 2008 R2, and SharePoint Server 2010.

Now, go back a few post, and check out the RoadMap and "Wave 14" for next year. All of these technologies, combined as a platform that Microsoft Dynamics AX is a part of, to enable customers and their business to be Dynamic, and be successful.

There is no other platform that is coming close to offering such abilities! This is very exciting, and should be for business users, and IT shops. This means more controlled and secure power in users hands, while allow control of Master Data or one version of the truth, platform wide.

That's all for now! Check back soon as more to come!




"Visit the Dynamics AX Community Page today!"

SQL Server 2008 R2 Master Data Services - Aka: Microsoft MDM, Project BullDog

Recently, Jeff O. over that MSDynamicsWorld started the conversation about MDM vs. IDM. I covered this on the following blog entry:
MSDW: MDM vs. IDM

In that I went deeper into the need for understanding the technology behind MDM, and what will make up MDM in order for a company to create policies and procedures for managing their 'Master Data'.

Well Microsoft last week, at TechEd, took the covers off of Project "BullDog" and we now have 'Master Data Services'. This will be a part of SQL Server 2008 R2, and be managed, ran and used from SQL Server point of view.



'Master Data Services' is Microsoft's offering for 'Master Data Management'. Here's from the Microsoft MDM home page:
"In June 2007, Microsoft announced the acquisition of Stratature, a privately held company based in Alpharetta, Georgia, and a leading provider of master data management (MDM) software. This was the first step in Microsoft establishing a presence in the master data management space, and will accelerate Microsoft’s delivery of technology in the MDM market. The Microsoft MDM product codename is “Bulldog”. Bulldog includes Microsoft process and standards applied to the Stratature code base as well as several important new capabilities. Microsoft customers want a solution that leverages their technology investment, integrates with their software assets, is easy to install and maintain, and provides a low total cost of ownership. Bulldog will meet these requirements while building on a long‐term vision for master data management at Microsoft.

On initial scoping, it was determined that 'Bulldog' would ship as part of Microsoft Office SharePoint in the O14 wave. At TechEd 2009, we announced a change in packaging for the new MDM capabilities. Project 'Bulldog' will now ship as part of the next release of SQL Server codenamed ‘Kilimanjaro’ as 'SQL Server Master Data Services.'

This means that in addition to new capabilities such as Self Service BI and multi-server management, SQL Server ‘Kilimanjaro’ will also provide customers with a rich platform for MDM through SQL Server Master Data Services. Customers who have purchased Software Assurance (SA) should view this as net new value and innovation that they will have access to as a result of their investments in SA."

Great bit of information there, specifically about the move from office SharePoint to residing at the SQL Server level. This was the correct move. This should live at the SQL Server level.

Also note the bolded text, those customers that invested with Software Assurance will get this, as part of their next SQL Server Upgrade. Great value add there!

The link to the Microsoft MDM or Master Data Services for SQL Server 2008 R2, can be found here: www.microsoft.com/mdm

Here is a list of top features from that page:
"-Master Data Hub that provides central management of master data entities and hierarchies and provides a comprehensive role based security model that ensures fine-grained, secure access to master data.

-Flexible Data Model completely defined in a metadata driven data model. Data administrators are free to define the data model in any way that satisfies the needs of their organization. As new business entities appear or current business entities change, the master data hub data model can change to accommodate the new business requirements.

-Business Rules that ensure the quality of data entered in the master data hub. The business rules engine is efficient and flexible.

-Human workflow that notifies assigned owners of business rule violations by email. Notifications can be sent for every violation or grouped together for periodic notification.

-Versioning of all data entities and hierarchies. New versions can be created by an administrator whenever they are required.

-Hierarchy Management supports both level-based and ragged parent child hierarchies and is managed by an intuitive drag and drop interface.

-Security model is flexible and can be enforced at a very granular level. Security is integrated with Active Directory.
"

Security, Versioning, Workflow, Flexible data model, all the aspects of an MDM solution a company needs in order to have true policies, procedures and technologies to achieve Master Data Management.

Notice the tag into the self-service BI. This was a voiced concern by a lot of people, 'different versions of the truth' that people were complaining about would exists with this self-service BI. Master Data Services is the solution to that possible issue, that techology, being implemented correctly with customer specific policies and procedures for managing it.

There is a ton of things to check out on the Microsoft MDM home page, so I suggest going there, and learning as much as you can.

Also read from Kirk Haselden, Microsoft Product Manager for for Master Data Services.:
Master Data Services – What’s the big deal?

"As corporate information ecosystems become more complex, so goes the management of company lists. Master lists are used, accessed, managed, changed and unfortunately corrupted in myriad ways throughout the company in various divisions and in various locations. For example, customer data may be used in the front office for direct contact with customers or in the back office in marketing, billing and other business processes. ERP and CRM systems do a great job of providing the functionality around how you use say, the product master or customer master. That’s what they were designed to do. However, they don’t do a great job of actually managing the forces that impact the master data itself. Four of the most important of those forces are decay, conflict, corruption and inconsistency."

He goes on...
"Traditional applications manage master data and these forces insufficiently. What is needed to solve these problems is an application that focuses on managing these forces with a set of tools that is specifically designed to ensure that the master data remains authoritative, is available in a secure way across the enterprise and is integrated with the applications that use it. Often the term “One source of the truth” is used to describe what master data management applications deliver. I prefer the term “authoritative source of master data”. Having one source isn’t always desirable nor is it always realistic. Master data isn’t truth. It’s simply data that represents the world at a given time slice. The trick and it’s a difficult one, is to keep the master data authoritative. Authoritative data is reliable, represents the state of the world at the time it’s referenced and is delivered in a secure, integrated and performant way."

So your thinking this is all great, but how does this apply Brandon? Well Master Data Services will apply, as Jeff pointed out in his article, as more and more SOA or Service Based ssolutions come into play. And read back to the top, where I interviewed Lachlan Cash and talked about SOA and Dynamics AX.

That will be the Service Bus for Dynamics AX WCF Services / SOA will be. And that means, MDM offered through Master Data Service for SQL Server 2008 R2 will offer the ability to a Microsoft customer to manage their Dynamics AX instance, master data with.

This will also apply to BI, which I have been heavily writing about, and any custom SOA projects that a customer would be working on.

So as you can see, it so very important you have a Partner that can understand all of this. Someone that can take and guide you through all this technology, to do proper planning, to understand your technical needs that are demanded from your business functionality desired. Sunrise Technologies, Inc. is that kind of partner that has the Technical and Functional depth and expertise that can help you.



Check back soon, as Master Data Service will be something covered more often and sooner rather than later. I will go into the pratical, by examples of how this will be used with a Dynamics AX instance, include BI and get into even a custom SOA and how all three can come together for a customer and offer "one source of the truth."

See you soon!




"Visit the Dynamics AX Community Page today!"