• Dimension Data Security and Cube Script

    May 31, 2011 / No Comments »

    If you have worked with Analysis Services dimension data security for a while, you know that MDX allowed/denied set expressions can become rather complex. You might be tempted to externalize these expressions in the cube script for brevity or reusability reasons. For example, you might decide to add a calculated member that returns a list of allowed partners that the user is authorized to see over a factless fact table as follows: CREATE MEMBER CURRENTCUBE.[Measures].[PartnerSecurityFilter] AS "Exists([Partner].[Partner].[Partner].MEMBERS, StrToMember(""[User].[Login].&[" + Username + "]""), 'Bridge Partner Security';  Then, in the allowed set you might try reusing the member definition in the allowed set expression, such as: StrToSet([Measures].[PartnerSecurityFilter]) This sounds like a great idea but it doesn't work. Specifically, you will find that it works for the first member of the SSAS role that connects to the server but it doesn't work for any other members of the same role who connect to...

  • My TechEd 2011 Sessions Available Online

    May 21, 2011 / No Comments »

    All TechEd 2011 breakout sessions are available online on MSDN Channel9 website. Here the links to mine: Top 10 Reasons to Upgrade to Microsoft SQL Server 2008/R2 Reporting Services Can Your BI Solution Scale? Enjoy and vote!

  • Business Intelligence Semantic Model – TechEd Redux

    May 20, 2011 / No Comments »

    While I've been busy at TechEd talking to BI enthusiasts, professionals and wannabes and delivering sessions, the news is out about the new BI roadmap and BISM positioning. See: T.K. Anand blog T.K. Anand & Ashvini Sharma recorded session What's New in Microsoft SQL Server Code-Named "Denali" for SQL Server Analysis Services and PowerPivot"? Sean Boon, Carolyn Chao, and Sabrena McBride recorded session Abundantly "Crescent": Demos Galore Julie Strauss' recorded session The Next Generation Design Tools for Analysis Services And the feedback from the community insiders, including Chris Webb's Good News on the Future of Analysis Services blog, Marco Russo's Microsoft Updates its BI Roadmap blog, and Boyan Penev's SSAS to BISM – Recent Developments blog. So, I won't repeat what's been already said. Instead, I'll attempt to summarize my thoughts about the new BI developments and give you some take-home points to help you plan future BI work. I...

  • TechEd Session Downloads

    May 17, 2011 / No Comments »

    The slides and source code for my TechEd 2011 North America session are available for download on the Prologika website (requires registration). If you haven't registered, please click the Join link in the upper right corner. My breakout sessions are: DBI205 - Top 10 Reasons to Upgrade to Microsoft SQL Server 2008/R2 Reporting Services Rich in demos, this session presents top reasons to upgrade to Microsoft SQL Server 2008 and SQL Server 2008 R2 versions of Reporting Services. Use this information to convince your boss that it's time to upgrade and remove past limitations. If you already use these versions, you might discover features that can help you find ways to simplify report authoring and maintenance. DBI330 – Can Your BI Solution Scale? Your Business Intelligence solution is ready for prime-time but will it perform well? Join this session to learn techniques for testing the performance and scalability of Microsoft...

  • TechEd 2011 Keynote

    May 16, 2011 / No Comments »

    Watching the TechEd keynote during which it was announced that we have some 10,000 people attending TechEd this year. On the BI side of things, Amir Netz, Distinguished Engineer at Microsoft, came on stage to unveil some of the new features coming up in Denali and demonstrate the BI continuum story in Denali (PowerPivot, SharePoint with Crescent reporting, and Corporate BI). The Corporate BI model had some 2 billion rows cached in-memory VertiPaq engine. You will be able to use Visual Studio to "take over" from Excel and build models exceeding the Excel limitation of 2GB file size. Wait for more exciting news tomorrow at sessions DBI206 - What's New in Microsoft SQL Server Code-Named "Denali" for SQL Server Analysis Services and PowerPivot and DBI211 - What's New in Microsoft SQL Server Code-Named "Denali" for Reporting Services!   On the Windows Phone area, interesting news was that there will be an upgrade later...

  • Where is Teo at TechEd 2011?

    May 10, 2011 / No Comments »

    My TechEd sessions got finalized. This would be my sixth TechEd to go to and present at (this one conveniently located in my home city). This time I am honored to have three sessions (two breakout sessions and one interactive session). You can't attend, you should be able to find me hanging around at the DBI booth. Code Session Day Time DBI205 Top 10 Reasons to Upgrade to Microsoft SQL Server 2008/R2 Reporting Services Breakout Session Primary Speaker(s): Teo Lachev Proposed Speaker(s:) Rich in demos, this session presents top reasons to upgrade to Microsoft SQL Server 2008 and SQL Server 2008 R2 versions of Reporting Services. Use this information to convince your boss that it's time to upgrade and remove past limitations. If you already use these versions, you might discover features that can help you find ways to simplify report authoring and maintenance. Monday, May 16 3:00 PM -...

  • Project Barcelona

    May 4, 2011 / No Comments »

    After dropping the Microsoft Business Intelligence Indexing Connector, Microsoft is working on an advanced content crawler, code-named Project Barcelona, that will index BI content, such as SQL Server, Excel, SharePoint, Reporting Services, Analysis Services, SSIS. Although initially aimed at DBA/ETL developers, its ambitions go well beyond that. According to the team blog, "At the end of the day, we want to build a set of tools that makes managing the modern enterprise data topology significantly easier – so we strongly believe we will need significant feedback before landing on the right design and feature set". The blog doesn't say when Barcelona will be released and how it will be implemented. I'd expect it to become a part of SharePoint and released alongside SQL 11 but this is just a speculation on my part.

  • Finding Kerberos in Forests

    April 30, 2011 / No Comments »

    I've made a decision. My next book will be Applied Kerberos BI. Jokes aside, I've been doing a lot of security work lately to get Kerberos working for internal and external users. Here is my latest success story. In this case, the customer has two AD forests. The internal users are on a DOMAINA in FORESTA. The production servers are in DOMAINB. The external user accounts are in DOMAINC. Both DOMAINB and DOMAINC are in a separate forest. There is a bi-directional trust between DOMAINA and DOMAINB and between DOMAINB and DOMAINC. DOMAINC trusts DOMAINA. After registering SPNs, etc. we've found that Kerberos doesn't work. Or, to be more specific, it works partially. If a service account from DOMAINB is used to run SSRS and SSAS, the double hop (user-SSRS-SSAS) is successful for users on DOMAINC but not for DOMAINA users. And vice versa, if a DOMAINA service account is...

  • Analysis Services Refusing to Start

    April 23, 2011 / No Comments »

    I had a laptop which blue screened and shut down. Some files got corrupted and fixed by the CheckDisk. Apparently, something got whacked during the process because the Analysis Services would refuse to start with the following error: The service cannot be started: XML parsing failed at line 1, column 40961: Illegal xml character. Unfortunately, Analysis Services doesn't support starting in recovery mode, such as SQL Server Database Engine. Usually, when you get a message like this, the error message tells you which object file, such as dimension, got corrupted. To fix, you would delete the database from C:\Program Files\Microsoft SQL Server\<SSAS Version>\OLAP\Data and reprocess the database or restore from backup. But in this case, the error message in the Windows Event Viewer didn't indicate any object. As it turned out the issue was a corrupted master.mvp file in the Data folder. The resolution path was: Copy the master.mvp file...

  • Data Driven Subscriptions and Row-level Security

    March 28, 2011 / 2 Comments »

    Scenario: A customer wants to have report row-level security and data-driven subscriptions. If you have used SSRS for a while, you will now the cornerstone of report row-level security is User!UserID which returns the user identity. But here is the catch – SSRS will refuse creating a data driven subscription if a report has an expression with User!UserID. Solution: How do we solve this horrible issue? A great hack is in order. We will disguise the User!UserID in an embedded function inside the report or in an external assembly so SSRS can't see it. (The reason why this function is named GetHashedUser although no hashing is in place will become obvious in a moment) Public Function GetHashedUser() as String return Report.User!UserID.ToLower() End Function   Believe it or not, this lets the data-driven subscription going. But there is another catch. A data-driven subscription runs in unattended mode and you need to...

Training

We offer onsite and online Business Intelligence classes! Contact us about in-person training for groups of five or more students.
For more information or to register click here!

Books

Learn Power BI at your own pace with our latest book Applied Microsoft Power BI. Targeting information workers, data analysts, pros, and developers, It is designed as an easy-to-follow guide for learning how to implement BI solutions spanning the entire personal-team-organizational BI spectrum.

Syndication