Microsoft Certified Solutions Expert

/0 Comments/in /by

Just got myself upgraded from MCTIP BI (which retires on January 31st, 2014) to the new MCSE BI certification by passing the three required exams. Over the course of almost 20 years, I’ve managed to stay current and accumulate a healthy dose of certifications (some of them retired by now), including:

1996: Microsoft Certified Professional

1996: Microsoft Certified Solution Developer

2002: Microsoft Certified Application Developer

2003: Microsoft Certified Trainer

2006: Microsoft Certified Technology Specialist

2011: Microsoft Certified IT Professional

2013: Microsoft Certified Solutions Associate

2013: Microsoft Certified Solutions Expert

Some people doubt the benefits of certifications. True, having a certification might not be a true testament of skills or expertise just like someone who only reads or writes books is not a true practitioner. But as with anything in life, I believe that the more you put in, the more you get out. While it’s hard to measure the intangible benefits of certifications, articles, books, presentations, etc., I do believe that they contribute to the overall package and credibility. Despite the Microsoft Architect and Maestro sagas, I do think that a certification is an important differentiator (especially for consultants and fresh graduates) and something that makes you stand above the crowd. In my consulting career, sometimes I do come across RFPs that require or desire certifications and I like the fact that these customers acknowledge the certification effort. In addition, I like the fact that certifications keep me on the edge by forcing me to study and refresh my memory of product areas that I’ve forgotten or never used. Certifications are also required for achieving Microsoft partner competencies – another very important differentiating factor for consulting companies.

On the downside, while Microsoft has made a great effort to make the exams more engaging and practical (case studies, hot spots, drag and drop, etc.), you still have to adjust to the Microsoft exam mentality. This is how we want to you answer the question despite that it’s not quite clear what we are asking here or it doesn’t make a perfect sense to you. Syntax and step memorizing is another area of improvement. So are all these questions about obscure and unpopular techniques, such as SSAS remote partitions and linked objects. It will be great if the exams are actually written by practitioners as opposed to someone who probably scans Books Online to come up with elusive questions.

Meanwhile, here is my latest logo addition:

 

111313_2305_MicrosoftCe1

 

Finalist for BI and Analytics Innovation Awards

/0 Comments/in /by

Out of 26 applications, our BI solution for Recall Corporation was nominated as one of the six finalists for the BI and Analytics Innovation Awards presented tonight by the Atlanta TAG B/I society! Although we didn’t win the award, we’re honored to be shortlisted. Eric Falthzik, Co-chairman at Technology Association of Georgia – Business Intelligence and Analytics Society and Director of Customer Intelligence at Cox Communications said that this is most interesting and sophisticated Microsoft-based solution that he’s seen.

I’d like to thank Recall for giving us the opportunity to implement an innovative BI solution that changes the way they analyze their business. I’d like to also thank the TAG B/I panelists for appreciating its value.

111313_0341_Finalistfor1
111313_0341_Finalistfor2

Passing Large Report Parameters

/0 Comments/in /by

Scenario: A custom application needs to display a filter dialog to prompt the user to select accounts from a tree consisting of thousands of nodes, such as a tree that organizes the accounts in levels. Once the user makes the selection, the application passes the account list to a report parameter so that the report can show the data for the selected accounts only.

Issue: The filter list could get rather large. Even if the application follows the Excel filtering pattern to include only the parent node if no child nodes are selected, you might end up with a list consisting of hundreds, perhaps even thousands of accounts. However, Reporting Services has limits on the parameter size. When you test the report in the Report Designer, the report parameter is limited to some 32,000 characters. The Report Viewer doesn’t have this limitation so a string-type report parameter can deceptively take the entire list. However, a change was made in SQL 2012 to limit the parameter size during report rendering. Specifically, ParameterInfoCollection.ToUrl throws an exception “The value of parameter ‘param’ is not valid.” when a parameter value exceeds 65,520 characters. That’s because this method calls the .NET System.Uri.EscapeDataString method which has the 65,520 limit. Interestingly, the report query is executed but the error is thrown after the dataset is processed.

Resolution: What if you still need to pass a parameter value whose size exceeds 65,520 characters? Here are some options:

  1. Although not a solution, the easiest approach is prevention, such as to limit the user selection to 65,520 characters.
  2. Another option is to save the report value to a database and pass the table key to the report parameter. Then, the report query can join to the report parameter table. This approach may work well for relational reports but it doesn’t work so well with cube reports because you can’t join the cube to a relational table. You might try to default the report parameter to a database query that brings the report value from the database but you will still get the above error. For cube reports, one option might be to concatenate the report query text with the filter from the database. Another option could be to write an SSAS stored procedure that retrieves the filter from the database and generates an MDX set.
  3. Yet another option is to compress the report parameter. For example, MDX has a rather verbose notation. Instead of passing the member unique name, e.g. [Account].[Account Level 1].&[1000], consider collapsing the level name to |al1|.&[1000] and then expanding it on the report side by replacing the token with the attribute or level unique name. This approach may reduce the parameter list x10 and help you overcome the 65K limit.