SSAS tools: mdx studio

So many good tools out there, and yet not all of them get noticed…..

At the SQL Pass conference the week before, I attended one of the classes by the Microsoft CSS groups on common Analysis Services support issues.  While not the best presentation format or speaking, the information and thought processes were very relevant and thought out, so all in all – good session. 

What surprised me was how few of the attendees were familiar with Mosha’s MDX Studio that the Microsoft speaker presented as a resource – so in the nature of spreading the good word, here is the link:http://www.mdxstudio.com/

What is it? From Mosha’s site:

MDX Studio is tool which helps users of Microsoft Analysis Services to analyze complex MDX expressions, monitor performance characteristics of MDX queries and get insight into how MDX interacts with other UDM features such as attribute relationships. MDX Studio provides unique visualization representation of MDX expression and allows the user to interact with different stages of MDX execution.

November 17, 2009 16:39 by Sid
E-mail | Permalink | Comments (3) | Comment RSS

Sharepoint server 2010 Insights

You may or may not have heard by now that the business intelligence capability in SharePoint 2010 now has a platform branding called INSIGHTS.

I will blog more about the features in my “down time” at the SQL PASS conference next week, but I wanted to put the links out to everyone so you can start ramping up on what is coming inside SharePoint 2010 with respect to business intelligence.

First and foremost, the official Insights link: http://sharepoint2010.microsoft.com/product/capabilities/Insights/Pages/default.aspx. Lots of information and resource links posted here - -a great way to take a top down look at what the Insights platform is all about.

If you have completely been ignoring the news and the gossip, then you probably have missed the two biggest architectural features in the Insights platform will be PerformancePoint Services and Excel Services support of PowerPivot (formerly Project Gemini).  PerformancePoint Services will utilize the new application services architecture that is now part of SharePoint - -this allows for far greater flexibility in deployment and most importantly, security. 

PerformancePoint Services links:

• http://technet.microsoft.com/en-us/library/ee661741(office.14).aspx

1.  
   • http://technet.microsoft.com/en-us/library/ee424392(office.14).aspx

Excel Services: http://technet.microsoft.com/en-us/library/ee424401(office.14).aspx

Report Builder 3.0: http://technet.microsoft.com/en-us/library/bb418434(SQL.10).aspx

Technical Diagrams: http://technet.microsoft.com/en-us/library/cc263199(office.14).aspx

So, keep reading and don’t get left behind.  If you landed here and you are more interested in the other features of SharePoint and not business intelligence, visit my fellow Catapultian Matt’s post here. I will, of course, encourage you to stay here and become more interested in BI, but I’m not into forcing my opinion in anyone, I’ll leave that to the media.

October 28, 2009 15:57 by Sid
E-mail | Permalink | Comments (5) | Comment RSS

SSIS on a cluster

I figured this was worth a quick post: while many of SQL Server’s aspects are cluster aware, SSIS is not. Why? My guess is that there is not a pressing business need for this to be a cluster aware service – while many companies have vital ETL processes to help run company business, the need for failover in this scenario is not as pressing as in transactional systems.

With that said, I had a customer that was testing the failover of one of their main SQL clusters and suddenly noticed that a more than several jobs failed to run.  In helping on the issues, I noticed that they had SSIS steps in the job pointing to the cluster name for pulling out the packages form MSDB, and that, more importantly, someone had forgotten to install the SSIS service on the failover node.

Someone had configured SSIS to point to the cluster on the primary node, but must have forgotten to install the service and do the same config change on the failover node – easy enough fix; but also a good education opportunity for the client as they were not familiar with SSIS at all (transitioning to a new environment for them this past year, so most of this is new for them).

SSIS is not recommend to be configured as a cluster resource, but that does not mean it cannot be used on a SQL Server that is clustered – you just have to be aware of what you are doing and the consequences.  In my current scenario, we have SSIS configured to work on the cluster environment, but not as a cluster resource (the second link below will show you how).

Here are the references:

• - http://msdn.microsoft.com/en-us/library/ms345193.aspx
• - http://msdn.microsoft.com/en-us/library/ms137789.aspx

Just be sure to weigh the pros and cons as even the simple config change has its own consequences, in particular the section in the first link on disadvantages of SSIS as a cluster resource.

October 15, 2009 15:25 by Sid
E-mail | Permalink | Comments (5) | Comment RSS

Awesome speaker for our next BI User group!!

I am excited to announce that we have secured a great speaker for our next Central Texas Business Intelligence User Group meeting on October 21st at the Microsoft office in Austin.

Bryan C. Smith, prolific SSAS author and now of Microsoft fame, will be coming down to speak on a yet to be disclosed topic on SQL Server Analysis Services.

If you have not done so, and you live/work in the area, be sure to join our group on LinkedIn or send me an e-mail and I will add you to our distribution list – this is a speaker you do not want to miss (if you love all things SSAS).

On a broader note, we will be upping the frequency of the meetings to monthly after the October meeting.

In that vein, we are looking for some more presenters (Larry Clark (MS) and I love presenting, but I think the group would like to see some new faces). Please e-mail me or post to the LinkedIn group board if you are interested.

Hope to see some new faces there!

September 14, 2009 16:45 by Sid
E-mail | Permalink | Comments (9) | Comment RSS

Design Tip - Analysis Services Aggregations

I was cleaning up a cube design for a customer, primarily working with the current implementation to resolve some performance issues and make some minor alterations to the dimensions, when I ran across an aggravation in designing aggregations for the partitions I implemented ( there were none before, part of the issue).

I had just finished designing my partitions when I started to design the aggregations for my partitions – when the designer returned this obnoxious message:

    0 aggregations have been designed. The optimization level is 0% (0 bytes).

I will mention again that this cube was not my design, and when given the opportunity, everything from the dimensional model to the entire cube will be redesigned, however, this message was a nuisance. Fortunately, I kept my cool and figured out what I was doing wrong. The Aggregation Design Wizard will not allow a resulting aggregation set to exceed a certain percentage of the fact table (I hear it is 30%, but I can't find this documented anywhere).

Here are a few general rules with aggregations that will help avoid this message:

1. Make sure the partitions you are trying to assign an aggregation design are evenly distributed. In other words, do they have a similar number of facts/rows in them or are they varied? One aggregation design can work for multiple partitions so long as those partitions are similar or close in overall row counts
2. If you have a large number of attributes in your dimensions related to a particular partition set you are designing for, you may consider changing some of the options on the Review Aggregation Usage screen in the wizard. Attributes that are rarely used can potentially be set to None
3. On the Specify Object Counts page, check the counts tabulated for each dimension as well as the measure group. On the dimensions, the wizard does not calculate the partition count. This number is the count of that member distinctly appearing inside the specific partition. For example, if your partition broken by year, then the count of dates on your Calendar dimension would be 365, months would be 12 and so on.

In addition to the above, take a look at your individual partitions -- are the individual partitions too large? BIDS Helper suggests a 20 million plus row count per partition is probably not partition with enough detail.

As for my specific problem – due to the "funky" (lack of a better word) design of the fact tables, I did not have reasonable splits in both my row counts and related member counts for my initial partition designs as I had initially suspected, and being in a hurry that evening, I did not update the counts inside the partition design screen before moving over to designing aggregations. I simply assumed I had picked a good way of doing it on the first try!

So, a lesson in patience reiterated yet again, and after getting some sleep, I looked at the issue the next morning with some fresh eyes and found my solution.

Hopefully this little explanation will save some time for someone out there – feel free to let me know if any further explanations are necessary, or feel free to share your aggregation design issue!

August 17, 2009 12:17 by Sid
E-mail | Permalink | Comments (17) | Comment RSS

Reminder: BI User Group meeting August 13th!

Reminder, we are having our next Central Texas Microsoft Business Intelligence User Group meeting a week from today, August 13^th, 2009 from 6:00pm to 8:00pm at the Microsoft Austin office.

Session Topic: "PerformancePoint Monitoring and Analytics 101"

Description:

In this session we will cover the basics of PerformancePoint: what it is, how it is deployed and sample architecture patterns. We will also cover the basic elements of Dashboard Designer and how developers and business analysts will use this tool to develop charts, grids, scorecards and KPIs for their business to consume.

If you haven't joined the group on LinkedIn yet, then please feel free to do so – it is an easy way to keep up with any updates, meeting announcements and meet others in our profession in the central Texas area. Logo below will take you to the group home page:

Be sure to RSVP if you are coming!

August 6, 2009 16:53 by Sid
E-mail | Permalink | Comments (4) | Comment RSS

WhitePaper: PerformancePoint in an extranet environment

Great read for when you are stuck in the airport: Publishing PerformancePoint Server in Extranet Scenarios.

This is actually a very detailed document outlining many of the aspects around establishing an extranet deployment, particularly a secure one.

Important Note: We have run across a bug in Server 2008's implementation of Kerberos. This issue will cause a fatal flaw to your implementation with SSAS in the scenario– Dan English provides a greater level of detail in his post earlier this year. Just as a recap, you will run into errors in accessing SSAS where the MDX will get truncated in the Kerberos ticket that gets passed to the SSAS service. The only way around this at the moment is to have the front end servers be Server 2003, or CTP versions of Server 2008 R2.

Note that there is no "official" fix for this at the moment, though one is expected as a cumulative update in August.

July 30, 2009 15:06 by Sid
E-mail | Permalink | Comments (5) | Comment RSS

PerformancePoint, Security and Account Access

I was assisting a client clean up an installation of PerformancePoint. They thought they had followed all of the steps, but were perplexed by an error they were getting after deploying the dashboards to SharePoint. Given what was at the bottom of the error, and how few people realized this slight difference in authenticating accounts, I figured it was worth a quick post.

To start, the error received was:

"You do not have permission to see this data. (Domain\UserAccount). Contact the administrator for more details."

My client was perplexed. They had developed the dashboard in Dashboard Designer and previewed it with success on the Monitoring server preview site. This person was an admin of Monitoring server and a site collection admin in SharePoint. The question to me then was – what was going on?

To help explain, below is a diagram of a sample diagram of a PerformancePoint deployment on a farm, which is exactly what this client has in their environment:

The big "gotcha" here for him is based on the usage of NTLM as the authentication method and a lack of understanding on where deployed dashboards actually render and load.

In a non-Kerberos implementation, deployed dashboards will connect to data sources under the identity of the SharePoint service, which was the bit of understanding this user was missing – his perception was that SharePoint rendered output from the Monitoring web service, and thus authentication still happened from the Monitoring Web Service – once this perception was corrected, we added the SharePoint account as a reader on the cube for his dashboards and life went back to normal.

July 21, 2009 12:04 by sid
E-mail | Permalink | Comments (8) | Comment RSS

Installing SSRS for SharePoint, the fun way

I figured the search engines would do a good job of delivering the answers to these questions, but as I have seen this theme reoccur many times over the past few weeks in the MSDN forums, I am going to write out the steps for everyone (if nothing else, at least I'll just be pasting a link to this versus copying the steps all over again in forum responses).

For many people installing the SSRS plug in for SharePoint/WSS (both SSRS 2005, but more likely 2008), you can get these annoying errors (this error can be found in Root:\Users\YourLogin\AppData\Local\Temp and will be prefixed with RS_SP):

For some of you, this error may become a common nuisance when installing the SSRS plug in for MOSS/WSS 3.0 for both SSRS 2005 and 2008:

******* User does not have permission to add feature to site collection: http://<SharePoint URL here>:36076.

This can be particularly frustrating if you are logged in to the box under the farm admin account and have local admin privileges.

To work around this error, follow these steps:

1. Double check that you have a site collection set up on the farm, not just the web app

2. Log into the server you are installing the Reporting Services plug in to with credentials that are the farm admin for the MOSS install

3. Run the CMD tool with Administrator privileges (also called elevated permission)

4. Navigate to where you have the sharepointrs.msi and type sharepointrs.msi SKIPCA=1

5. Navigate to your Temp% directory to find the unpacked rsCustomAction.exe file

6. In the CMD tool, execute rsCustomAction.exe /i

7. After the install, you will probably need to navigate to Central Administration > Site Actions > Site Settings and under the heading Site Collection Administration click Site Collection Features.

8. From there, click activate on Report Server Integration Feature

9. The settings for SSRS will now have their sub section on the Application Management tab in Central Admin

References:

• http://technet.microsoft.com/en-us/library/aa905871.aspx

July 7, 2009 13:09 by Sid
E-mail | Permalink | Comments (16) | Comment RSS

Book Recommendation: Drive Business Performance

Good sales staff for consulting and services firms are hard to find. These great sales people are always working to strike the balance between knowing enough about the services, and in our case, the Microsoft products to talk intelligibly, but at the same time, not be overwhelmed in studying details and product nuances so that they are distracted from selling.

Bruno and Joey's "Drive Business Performance, Enabling a Culture of Intelligent Execution" is a great aide for business intelligence consultants looking to help their sales leads to understand the BI thought process and lift the talk away from fancy Excel charting and into true performance management.

While this book may be a bit of a slow read for someone mainly focused on technology implementation, I still recommend it. As consultants, we still need to bring our clients along with our solutions, and this primer can help with those talking points around people and organization, not just the tools.

June 25, 2009 14:32 by Sid
E-mail | Permalink | Comments (6) | Comment RSS