-
SSAS Instance Not Starting
June 17, 2013 / No Comments »
Issue: A customer reports an issue with a production SSAS Tabular instance not starting after reboot. The server has also an SSAS Multidimensional instance which starts just fine. There are no interesting messages in the Event Viewer. Resolution: During installation, the customer has configured the LogDir of both instances to point to the same physical folder. However, the Flight Recorder can't start on the Tabular instance because the log file is already locked by the Multidimensional instance. The solution to this horrible problem was to reconfigure the LogDir setting of the Tabular instance (in SSMS, connect to the SSAS instance, right-click the server, and click Properties) to a separate folder. Moral: Always separate DataDir, LogDir and TempDir folders so each instance has its own set of folders.
-
Smart Date Keys Got Smarter
June 12, 2013 / 3 Comments »
Using "smart" date surrogate integer keys in the format YYYYMMDD is a dimensional modeling best practice and venerable design technique. To make them smarter though, consider using the Date data type which was introduced in SQL Server 2008. The Date data type stores the date without the time component which is exactly what you need anyway. Using the Date data type instead of integers have the following benefits: The Date data type allows you to perform date arithmetic easier as the date semantics is preserved, so you can use date functions, such as YEAR and MONTH when querying the table or partitioning a cube. Self-service BI users can conveniently filter dates on import. For example, PowerPivot enables relative date filtering, such as Last Month, when the user filters a date column. The Date data type has a storage of three bytes as opposed to four bytes for integers. The only...
-
What’s New for BI in SQL Server 2014?
June 5, 2013 / No Comments »
The short answer is nothing much, which is probably good so we can catch our breath after the SQL Server 2012 and Office 2013 waves. As you've probably heard, Microsoft announced SQL Server 2014 at TechEd. SQL Server 2014 will be a database-focused release with no major changes to the BI "pillars" (SSAS, SSRS, SSIS) and PowerPivot, except perhaps bug fixes. However, you might find some other new additions and enhancements interesting depending on your specific projects: In-Memory Enhancements This should come to no surprise due to the continuing investment in memory backed-up storage. New In-Memory storage – Code-named Hekaton, this new in-memory storage option will allow you to host SQL Server tables in memory in order to improve I/O. While predominantly targeting OLTP applications, I believe Hekaton will be useful for narrow-case BI scenarios as well, such as for improving the performance of the ETL processes by using in-memory...
-
Configuring Power View Connectivity to Multidimensional
June 2, 2013 / No Comments »
As I mentioned in my previous blog, CU4 for SQL Server 2012 SP1 includes the release bits of Power View Connectivity for Multidimensional (DAXMD). It allows you to create Power View reports from Multidimensional cubes. In this blog, I'll show you how to configure this feature. APPLYING CU4 You need to upgrade both Reporting Services in SharePoint mode and the Analysis Services instance that hosts your cubes. If SharePoint and SSAS are on the same machine, as in the screenshot below, you need to run the CU4 setup program only once. In this case, I decided to accept the default choice and upgrade all instances on the box although the required ones are highlighted. With distributed deployments, you need to install CU4 on the SharePoint server and on the SSAS server. CREATING DATA SOURCE Once CU4 is applied, you're ready to set up a data connection in a SharePoint library...
-
Power View Connectivity to Multidimensional (DAXMD) Released
June 1, 2013 / No Comments »
Microsoft released Cumulative Update 4 for SQL Server 2012 Service Pack 1. This is more than a regular cumulative update as it includes enhancements to both Power View and Analysis Services to support connecting Power View to OLAP cubes, also known as DAXMD. For more information, read the official announcement by the Analysis Services product group. Now, business users can easily author ad-hoc reports and interactive dashboards by leveraging your OLAP investment. As a proud contributor to the DAXMD TAP program, I'd delighted with the results as I discussed in my blog "DAXMD Goes Public". I hate to dampen the spirit but be aware that only the SharePoint version of Power View has been extended to support Multidimensional. We don't know yet when the same will happen to Power View in Excel 2013. Anyway, this is a very important BI enhancement and it's time to plan your DAXMD testing and deployment.
-
Microsoft Kerberos Configuration Manager for SQL Server
May 24, 2013 / No Comments »
Anyone who has gone through configuring Kerberos knows that it's not fun. After having done a few installations, my personal record for configuring Kerberos for SharePoint, SSAS, SSRS, SQL Server, and PerformancePoint was 4 hours but I had all parties including the AD administrator in the same room. In an attempt to facilitate troubleshooting Kerberos, Microsoft released Microsoft Kerberos Configuration Manager for SQL Server. It's a simple diagnostic tool for troubleshoot Kerberos issues related to SQL Server. Once you install and start the tool, you connect to the desired instance (requires SQL Server standard authentication to a remote server). Then, the tool checks the service account and discovers what SPN's are registered for that account and what delegation options are configured in Active Directory. If it finds inconsistencies, it is capable of generating a script that your AD administrator can run or applying the fix interactively. As a side note,...
-
Best Practices for Implementing Enterprise BI Solution at SQL Saturday 2013
May 19, 2013 / No Comments »
SQL Saturday 2013 in Atlanta was a raging success. Some 555 people attended which made it the most attended SQL Saturday even ever. I've uploaded the slides from my presentation "Best Practices for Implementing Enterprise BI Solution" to the Prologika site and Slideshare. The presentation had a great attendance and reviews. Thanks to everyone who attended it and it was great meeting all of you! "Learn best practices to make your organization a center of BI excellence! I'll walk you through lessons learned during our implementation of an enterprise end-to-end BI solution. Working experience with the dimensional modeling and the Microsoft BI stack is assumed." Best Practices for Implementing Enterprise BI Solution from Teo Lachev
-
Leveraging Data to Revolutionize a Mature Business by Bijal Patel at Atlanta BI Group on May 20th
May 14, 2013 / No Comments »
Due to Memorial Day, we'll move our next Atlanta BI Group meeting to May 20th. The meeting will be sponsored by Strategy Analyzer. Our speaker will be Bijal Patel (Director of Data and Integration Services at Cox Media Group) and the topic is "Leveraging Data to Revolutionize a Mature Business". "Learn how the integration, management and analysis of data is helping Cox Media Group reinvent its business model. Leveraging SSIS, BizTalk, MDS and several other leading technologies, Cox Media Group is finding new ways to reach its customers and deliver innovative media products and services to its established customer base." This looks like a very interesting "from the trenches" presentation given the experience of the speaker and the business of its organization. I hope you can join us. Please register and RSVP on the Atlanta BI Group website.
-
Presenting at SQL Saturday in Atlanta
May 5, 2013 / No Comments »
I'll present at SQL Saturday in Atlanta on May 18th. Initially, I was planning to talk about dashboard options with the Microsoft BI stack but the organizers had a call for more advanced content. To accommodate this request, I'll present Best Practices for Implementing an Enterprise BI Solution where I'll share proven practices harvested from real-life projects. "Learn best practices to make your organization a center of BI excellence! I'll walk you through lessons learned during our implementation of an enterprise end-to-end BI solution, which is discussed in the Records Management Firm Saves $1 Million, Gains Faster Data Access with Microsoft BI case study published by Microsoft. Working experience with the dimensional modeling and the Microsoft BI stack is assumed." I'm looking forward to seeing you on May 18th.
-
Default Parameters in Power View
May 4, 2013 / No Comments »
Scenario: You need to configure a Power View report to show data for a dynamic date, such as the current date or the last date with data. However, as it stands Power View doesn't support expressions. Workaround: Add a Boolean calculated column to the Date table that returns TRUE for the date of interest. For example, if you want the report to show data for today's date, the expression might be: =if([Date]=TODAY(), True, False) where [Date] is the column with date data type. Then, use this column as a filter in Power View. Unfortunately, this workaround has a significant limitation. If the report needs another filter on the Date table, such as to allow the user to overwrite the default filter on the current date, this approach won't work because filters on multiple attributes are interpreted as an AND condition (Date is 1/1/2013 AND TodayDate=True). Inability to specify OR filter...
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.
