Microsoft just released Cumulative Update 3 of SQL Server 2012. Among other bug fixes, this CU includes critical fixes for columnstore indexes that we’ve reported to Microsoft. If you use SQL Server 2012 columnstore, you must install CU3. The important columnstore-related fixes are:
I’m excited to announce that Prologika is now a Microsoft Gold Business Intelligence Partner. This reconfirms our reputation as a premier BI consulting and training company in Atlanta. According to Microsoft, only top 5% of the Microsoft partners worldwide have Silver or Gold competency, and only 1% with Gold competency. Kudos to all Prologika consultants on the team who helped us to achieve this major accomplishment!
Microsoft published a case study “Records Management Firm Saves $1 Million, Gains Faster Data Access with Microsoft BI”. Prologika architected the data warehouse, OLAP cubes, and presentation layer consisting of operational reports, SharePoint management dashboard and Power View reports.
Recall, a records-management firm, needed faster access to key performance indicators and more intuitive business intelligence (BI) tools. The company consolidated four data centers into a Microsoft SQL Server 2012 data warehouse. The solution’s performance enhancements speed employee access to more detailed data. By consolidating into a data warehouse, the company saved U.S. $1 million in hardware and licensing costs…With help from Microsoft partners Prologika and Berg Information Technology, Recall started deployment in August 2011 and went into production in February 2012.
TAG BI is organizing a “Making Big Data Real” event on August 23th and I was honored to be one of the panelists. Please join us if you can and ask me the techniques we use to implement multi-terabyte data warehouses on symmetric multiprocessing (SMP) systems that deliver reports within milliseconds and maximize your return on investment.
“The Technology Association of Georgia’s Business Intelligence/Enterprise Performance Management society explores one of the hottest topics in the technology landscape, Big Data. You have likely read the articles and the books and now you need to know more. On August 23, we will bring together a panel of experts for a most important session: “Making Big Data Real”. Please join us as we engage corporate technology leaders and consultants that are on the cutting edge of this new technology landscape. We will tell you what Big Data is all about and how you can make it real and actionable at your workplace and in your career. This is surely a professional event you do not want to miss.”
I’d delegate this one to the PerformancePoint 2013 blog, where Kevin Donovan, Microsoft Program Manager, nicely addresses PerformancePoint speculation, confusion and misinformation while also introducing what is new in detail.
To demonstrate the new features with Excel web reports, I stood up an Office 365 Technical Preview site. If you have received Office 365 invitations already, make sure that you choose the one with the SKU E3. Otherwise, you’ll get access to the administrator portal only and you won’t see the SharePoint, Outlook, and other menus.
Assuming you use Office 2013, you’ll find that it integrated much better with the cloud. Gone is the Save and Send menu. Instead, saving to could destinations, such as SkyDrive and Office 365, can be now be initiated from the Save As menu by adding a new place.
As before, once the Excel workbook lands in the Office 365 SharePoint (or SharePoint 2013) land, it’s automatically available for web reporting. However, the most exciting new feature now is that the web reports are editable and end users can change the report layout with both PowerPivot and OLAP cubes as data sources! To do so, simply right-click any cell on the pivot report and click Show Field List. Just like the desktop version of Excel, you can now add or remove fields in the Field List to update the report.
The Quick Explore feature is available as well to support a “Proclarity-like” data exploration experience. You can click a cell on the report and then click on the magnifying class icon and pops up. Or, right-click a cell and click Quick Explore. Then, expand a table (dimension) and select which field (attribute) you want to drill-down to.
Unfortunately, drillthrough is still unsupported and you can’t drill down a cell to see the underlying details as you can in Excel desktop. This feature is very frequently requested and I don’t know why it didn’t make the cut. Another feature that I would love to get with both Excel desktop and web reports is the Decomposition Tree that is currently only available with PerformancePoint Services. Speaking of PerformancePoint, SharePoint 2013 brings improved branding, filter enhancements and filter search, as well as support for Analysis Services EffectiveUser connecting string setting in case configuring Kerberos is not an option.
Excel 2013 includes features that improve productivity. Here are the ones related to BI.
Flash Fill
Suppose that you have a list of customers with addresses. When analyzing the data, you might want to analyze sales by USA states but the data doesn’t include a state column. Instead, suppose you have an address column that includes the mailing address, state, and zip code. So, you decide to create a new column. Now, instead of using a formula to parse the address as you would do in the past, you just enter WA in the first row. When you start typing CA in the second row, Excel figures the pattern and suggests to flash-fill the column. Notice that Flash Fill doesn’t use Excel formulas and you won’t get formulas in the column. Excel handles flash fill natively.
Quick Explore
This feature was included to allow you to quickly generate charts for analyzing data by time with Multidimensional and Tabular models. When you click a cell in a pivot report, the Quick Explore button is shown that gives you options to create trend or cycle chart. Notice that you can select another date dimension (table) to replace the default selection of Date.Calendar Date.
Note Excel 2013 charts don’t require supporting sheets with pivot reports anymore.
Speaking about analysis by time, Excel adds a new fitter, called “timeline”, that is specifically designed for this purpose. I found it an improved version of the Power View filter for integer fields. You can click a section in the timeline to select a specific period. Or, you can drag the mouse for an extended selection, such as years 2005-2007.
Quick Analysis
Suppose you have an Excel table but you don’t know much about pivot table, charts, and formatting. You can simply select the list (Ctrl+A) and click the Quick Analysis button to open a window that examines the dataset and suggests formatting (data bars, color scales, and conditional formatting, charts, tables, and sparklines.
[View:https://prologika.com/CS/cfs-file.ashx/__key/CommunityServer.Blogs.Components.WeblogFiles/blog/7762.Geography.xlsx:550:0]
Power View in Office 2013 brings the following new features:
To showcase some of these enhancements, I created a personal BI model based on a customer dataset imported from the AdventureWorksDW2012 database (I attached the Excel workbook). To create a Power View report, click on Insert ð Power View button in the Excel ribbon. This creates an empty Power View report connected to the BI model.
My dataset includes the customer location defined as address, postal code, city, state, and country. I’ve found two ways to visualize geospatial data:
SELECT SpatialLocation.Lat, SpatialLocation.Long FROM Person.Address
If you place multiple fields in the Locations zone, Power View will allow you to drill down these fields. In my case, I put the StateProvinceName and PostalCode fields in the Locations zone. Consequently, if I double-click on a bubble, Power View will zoom in the map to show me where my customers are located by postal code within that state. I can click the “drill up” in the map right upper corner to go back to the state level.
Note Not sure if this is a bug with the pre-release version of Office 2013, but Power View seems to have a problem with drilling down to entities with the same name. For example, initially I put the City field in the Locations zone but when I drilled down Georgia, Power View switched to Columbus, Ohio because both Ohio and Georgia have a city Columbus. It appears that Power View drills down to the first entity in finds in that level instead of constraining the list to the parent’s children only.
The Power View report also shows a pie chart. If I click a segment on the pie chart it cross-filters the map. For example, if I click the 2005 pie, the map highlights customers with sales in 2005. Vice versa, if I click a bubble in the map, the pie chart gets updated to show the data for that bubble only, such as sales for customers in Georgia. The field list shows that Power View now supports KPIs and hierarchies. The Gross Sales is defined as a KPI in the underlying model and Customers by Country is defined as a hierarchy. Finally, you can change the report theme and add images from the Excel’s Power View ribbon.
Jen Underwood wrote a great summary about what’s new in Office 13 BI. Interestingly, you’ll be able to drill down to lower location in the Power View maps, e.g. from country to state and then to lowest level you placed in the location zone when configuring the map visualization.
In a current project, international users indicated that they prefer dates and numbers to be formatted using their regional settings. Localization is a big and complicated topic and we’ve been fortunate that the scope was limited to just number and date formatting … or, at least so far.
Localizing reports on a report server running in native mode is easy. You just need to use culture –neutral format strings, e.g. C for currency of P for percentage, and set the report-level Language property to =User!Language. However, this is not enough for reports running in a SharePoint integrated mode. SharePoint requires installing language packs as follows:
Once this is done, the users will see the SharePoint menus translated, and dates and numbers on the reports formatted using the selected display language.
