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.
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 small downside of using the Date data type is that you will end up with funny looking unique names for the Date members in your Date dimension in an SSAS cube. The unique name will include the time portion, such as [Date].[Date].&[2013-07-01T00:00:00]. If you construct date members dynamically, e.g. to default to the current date, you have to append the time portion or use the Format function, such as Format(Now(),”yyyy-MM-ddT00:00:00″).
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 staging tables.
Enhanced Columnstore indexes – Columnstore indexes will be updatable so you don’t have to drop and recreate the index when you need to change data. For more information about how we used Columnstore indexes to speed up ETL, read this blog.
Big Data
There is a lot of talk about big data around the following two technologies:
Hadoop – As I’ve covered before, Microsoft marketing term for Hadoop-based distributions is HDInsight. I also showed you how to program MapReduce jobs so I won’t repeat myself here.
Polybase – The latest version of Microsoft Parallel Data Warehouse (PDW) get extended to support querying Hadoop-based data via a technology called Polybase. Microsoft’s David Dewitt did a great coverage of Polybase at SQL Pass 2012.
For more coverage of the new features, read the SQL Server 2014: A Closer Look blog by the Microsoft SQL Server team. Toward the end, notice that the public CTP1 will be out in a few weeks.
https://prologika.com/wp-content/uploads/2016/01/logo.png00Prologika - Teo Lachevhttps://prologika.com/wp-content/uploads/2016/01/logo.pngPrologika - Teo Lachev2013-06-05 17:39:002021-02-16 04:29:11What’s New for BI in SQL Server 2014?
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 with SSRS integrated and SSRS document types enabled. You need a Report Data Source connection type. You cannot use BI Semantic Model Connection because you’ll need to specify which cube or perspective you are connecting and the BI Semantic Model Connection UI doesn’t have this option.
Once you open the Data Source dialog box, choose the Microsoft BI Semantic Model for Power View data source type. Specify the connection string as usual. Notice that the Cube setting is mandatory and it can specify a perspective. Depending on your security setup, you can enable Windows Authentication (requires Kerberos for double-hop) or stored credentials.
Once the connection is created, you can just click it to launch Power View connected to the cube or perspective. Microsoft did an excellent job to bend Power View to support Multidimensional concepts. The following features are supported:
As with Excel, measure groups appears on the top of the Field List and are prefixed with the sigma sign.
KPIs are supported but there isn’t a separate KPI folder (as in Excel for example). Instead, KPIs are found within the corresponding Measure Group they are mapped to during design.
Calculated members appear in corresponding measure group (table).
Cell errors are exposed as strings.
Cell properties are supported.
Both static and dynamic (e.g. scope assignments) format strings are supported.
Display folders are supported.
Hierarchies have a special icon but you can’t drag the entire hierarchy to the report. Instead, you need to select the levels you need. Parent-child hierarchies are supported but are flattened and the Field List shows all levels so the user can choose which level they want to see on the report.
Dimension attributes are mapped as table columns. Certain attribute types provide enhanced visualizations. For example, if an attribute is tagged as a City type, Power View shows a Map icon to inform the user that it can be used for geospatial reporting. In addition, Image->ImageUrl type has been added to SSDT to allow visualizing images from URL.
All relationship types are supported, including M:M and Referenced.
Perspective and translations are supported via connection string settings.
Dimension data security is supported.
DAX now supports Variant measures. A variant measure is a measure whose data type is not known until runtime, e.g. a measure whose data type is Currency but could be overwritten in a scope assignment as “NA”.
The following features are not supported:
Named Sets
Actions since Power View doesn’t support actions
Cell Security is not supported. Users belonging to a role with cell security restrictions to a cube cannot connect via Power View.
For more information about the level of support with excellent screenshots, read the Power View for Multidimensional Models – Feature Drill Down blog by Siva Harinath.
https://prologika.com/wp-content/uploads/2016/01/logo.png00Prologika - Teo Lachevhttps://prologika.com/wp-content/uploads/2016/01/logo.pngPrologika - Teo Lachev2013-06-02 20:30:002021-02-16 04:29:09Configuring Power View Connectivity to Multidimensional
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.
https://prologika.com/wp-content/uploads/2016/01/logo.png00Prologika - Teo Lachevhttps://prologika.com/wp-content/uploads/2016/01/logo.pngPrologika - Teo Lachev2013-06-01 00:14:002021-02-16 02:47:06Power View Connectivity to Multidimensional (DAXMD) Released
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, be aware that if Kerberos doesn’t work, SSRS, Power View, and PerformancePoint 2013 supports SSAS authentication using EffectiveUserName.
https://prologika.com/wp-content/uploads/2016/01/logo.png00Prologika - Teo Lachevhttps://prologika.com/wp-content/uploads/2016/01/logo.pngPrologika - Teo Lachev2013-05-24 13:20:002016-02-15 12:03:58Microsoft Kerberos Configuration Manager for SQL Server
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.”
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.
https://prologika.com/wp-content/uploads/2016/01/logo.png00Prologika - Teo Lachevhttps://prologika.com/wp-content/uploads/2016/01/logo.pngPrologika - Teo Lachev2013-05-14 01:39:002016-02-15 12:12:09Leveraging Data to Revolutionize a Mature Business by Bijal Patel at Atlanta BI Group on May 20th
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.
https://prologika.com/wp-content/uploads/2016/01/logo.png00Prologika - Teo Lachevhttps://prologika.com/wp-content/uploads/2016/01/logo.pngPrologika - Teo Lachev2013-05-05 15:51:242016-02-15 12:13:09Presenting at SQL Saturday in Atlanta
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 condition between attributes is another Power View limitation. So, users needs to be trained to use either the TodayDate or Date filter but not both.
Looking ahead, it will be nice if Power View supports VB or DAX expressions as regular SSRS reports do.
Many thanks to Darren Gosbell for suggesting the workaround.
https://prologika.com/wp-content/uploads/2016/01/logo.png00Prologika - Teo Lachevhttps://prologika.com/wp-content/uploads/2016/01/logo.pngPrologika - Teo Lachev2013-05-04 17:22:372016-02-15 12:18:46Default Parameters in Power View
