SQL Server 2008 R2, PowerPivot - BI for the Masses

With the year closing out fast, I thought I would point out some recent published articles, across the Microsoft Universe focusing on SQL Server 2008 R2, specifically in regard to enabling the 'BI For the Masses'.

Recently, I posted the following.:
Microsoft PowerPivot - On the way, and use with AX

In this post, I talked about Microsoft PowerPivot, the new technology that is going to be released with SQL Server 2008 R2. This mixed with Office, including SharePoint 2010, will truly start to enable Microsoft's vision of 'BI for the masses'.

Some recent articles were posted, covering PowerPivot, from different points of view. The first is from RCPMag.com, with the following post.:
SQL Server 2008 R2 CTP is Feature-Complete

In the above article, PowerPivot is one of the topics covered, being that SQL Server 2008 R2 is the foundation technology that enables this. From the article.:
"The November CTP of SQL Server 2008 R2 offers the first preview of Microsoft's new PowerPivot technology (formerly code-named "Project Gemini") and allows BI data collected from it to be shared to the company's SharePoint Server.

PowerPivot is designed to let business users create their own BI solutions in Excel with a new in-memory analysis engine that Microsoft says will work on millions of rows of data. "Users can manipulate the data in new ways to create BI solutions and then publish them to SharePoint to collaborate with other users," explains Fausto Ibarra, Microsoft's director of SQL Server product management."

Next, the MSDN - The Architecture Journal, did a very focused and detail article about PowerPivot, and the technologies that enable it. The post.:
Increasing Productivity by Empowering Business Users with Self-Serve BI



From the article.:
"When it comes to BI, there is a constant battle between business users and IT. Business users know the functional components of the business; they understand fully what they want to analyze and the questions that they want to answer. The IT team understands the structure of the data, the models, the cubes, data flow from the operational systems, the data warehouse, and the control mechanisms for the data. Business users often feel that IT is always saying “No” when they make a request for a data cube, report, chart, graph, or even raw data. The members of the IT team often feel that users are making unreasonable requests, with timelines that equate to boiling the ocean by first thing tomorrow morning. Self-serve BI attempts to solve this problem by providing a mechanism that will satisfy the analytical needs of users and provide the governance and control that IT requires.

In its latest release of Office Excel 2010 and Office SharePoint Server 2010, Microsoft attempts to provide the self-serve analytical needs of business users in a feature that is known as PowerPivot."

And...
"PowerPivot for Office SharePoint accomplishes two important tasks. The first is that it provides a home for the PowerPivot documents that users create in an environment that IT controls. The second is that it provides users throughout the organization the ability to view and interact with PowerPivot documents by using nothing more than their thin-client Web browser.

For business users, consuming a PowerPivot document is as simple as going to their Intranet site and clicking a document. The document then renders in their browser, and they can interact with the document and perform their analysis. In fact, the consumers do not even need to have Office Excel installed on their local computers to interact with the PowerPivot document. The result is that business users focus on their business analysis and are oblivious to the technology that enables the interaction."

As well as...
"Office Excel documents in Office SharePoint can use connection files that are managed by the IT department. For example, IT can create connection files to the source systems and then simply point end users to these approved connection files. This alleviates the need for IT to service numerous requests for connection information for every Office Excel file that is created for a business problem.

One of the most powerful features of Office SharePoint is called Excel Services. Excel Services is the ability to render Office Excel documents in a thin client (Web browser). An important Office Excel document can be saved to a document library, and the entire organization can then view and interact with the Office Excel document without having to leave their browser. The consumers of the document just navigate to their company intranet and click the Office Excel document.

This functionality is particularly powerful when thinking about rolling out Office Excel 2010 to provide PowerPivot functionality. Only a handful of business users actually produce content, with the rest just consuming it. Using Excel Services, the only users who will need to have Office Excel 2010 are the producers of content. The consumers can interact with the PowerPivot documents without ever having to leave their browser or install the latest version of Office Excel."

Now with this information, lets loop back around, and refocus this for use with Dynamics AX. Think again, Role Centers, and the dashboards that executives love to use and make use of. Role Centers are great! And these dashboards will become even more useful, with the ability for content producing users to have access to the needed data, and be able to create usable PowerPivot and Excel parts, that can be published through MOSS, and be made use of for the Role Center Dashboards.

This is the battle that happens a lot with BI tools. They are hard to setup, then complex to maintian and develop. With the latest releases of Office, SharePoint, SQL Server --- Microsoft is deliverying a product / technology in PowerPivot that is addressing this issue. And it can be used with Dynamics AX, and should be considered for upcoming, future implementations and anyone on older AX installs that are seeking and wanting self-service, BI solutions that are familiar and work native with their ERP investment.

To wrap this post up, I want to piont you to the PowerPivot home.: www.PowerPivot.com

That is a great resource to start checking out, and reading now. Also there is a really great blog called.: PowerPivotPro, which is operated by Rob Collie, who is a Microsoft employee of 13 years, and a self proclaimed Excel geek, Business Intelligence professional, all-around data enthusiast, and one of the founding engineers behind PowerPivot, Microsoft’s new self-service BI offering.

His blog has some great post, which I actually subscribe to through my Amazon Kindle. (Thanks Rob for a great blog!)

Also there is the Microsoft Team blog, for PowerPivot.: Microsoft PowerPivot - Team Blog, and with it's recent post, helps bring the Cloud (Azure) into focus with BI and PowerPivot. Check out the latest DataSource adds.:



Notice SQL Azure? That's right, all of these technologies, coming next year in full release, giving the ability to span, scale, dig and report --- all for helping make businesses and customers more Dynamic. And what fits in their best as the ERP platform? Microsoft Dynamics AX!

That's all for now, and I know it's a lot to chew on. Check back soon, as we kick off 2010 next week, and as I have a whole lot more to post about, including a new interview, or two. 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!"

Why upgrade to SQL Server 2008 with DAX 2009?

So some of you may be wondering, why should we be pushing Dynamics AX 2009 on SQL Server 2008? What would be the big gain of moving from SQL Serve 2005 to SQL Server 2008 or even from SQL Server 2000?

Well one of the biggest reasons for clients, is if they have already a Dynamics AX install base, with a database that keeps growing and growing.

In SQL Server 2008, they will gain performance but also have the ability to compress their database by 40-60%, and still keep those performance gains! This was benchmarked some time back and recently again via Microsoft information.

So for those wondering, yes there is a very great reason, well several: Performance and Compress, just to start.

Check back soon!






"Visit the Dynamics AX Community Page today!"

Dynamics AX 2009 & SQL Server 2008

In my last post I talked about, how we are just now starting to scratch the Dynamics AX 2009 surface. And with the soon to be release SQL Server 2008 Database Product, I thought it was a perfect time to start talking about the two together.

I did some digging around, and came across some nice published information about Dynamics AX and SQL Server 2008. First off we have this recently published SQL Server 2008 Benchmark document:

Microsoft SQL Server 2008 - Benchmark Report

When looking at the above benchmark, do a search on the page for 'Dynamics AX'. Doing so will bring you to the following quoted statement:

"Record scale improvement of 70% in throughput, scalability and response time
Benchmark tests demonstrate record scale showing up to 70 percent improvement in throughput scalability and response time; maximizing performance while minimizing database growth using SQL Server 2008 database compression."

And Dynamics CRM benchmark information was just right below that:

"Record scale at 24,000 concurrent users with sub-second response rate
Benchmark tests demonstrate that record scale at 24,000 concurrent users with sub-second response rate was achieved with Microsoft Dynamics CRM 4.0, SQL Server 2008, and Windows Server 2008 for enterprise-level workload. Learn more"

Now, lets take a look at the offical Press Release for Microsoft Dynamics AX 2009: (Microsoft Dynamics AX 2009)

"- Enhanced security, reliability and scalability though integration with Microsoft SQL Server 2008. For example, Microsoft Dynamics AX 2009, through Microsoft SQL Server 2008, can compress the database size by 40 percent to 60 percent, which can lead to lower hardware and data maintenance costs."

So there is for sure a benchmarked reason why clients would want to upgrade to Dynamics AX 2009 & SQL Server 2008:

- Huge increase in throughput, scalability and response time.
- Ability to use compression, out of the box, to minimize hardware cost

And this seems to be the case for Dynamics AX 2009 in general. The overall architecture has improved greatly (see my article on MSDynamicsWorld.com: Scaling of the AOS), and now we can scale up and out, and take advantage of newer technology offerings. This also means that a client would gain a lot of performance benefit for switching from SQL Server 2005 to SQL Server 2008.

Check back soon!




"Visit the Dynamics AX Community Page today!"