Your Data Warehouse in the Cloud?

/0 Comments/in /by

I spoke with a customer today that has implemented Salesforce.com. For those who are not familiar with Salesforce, it’s a popular cloud-based, customer relationship management (CRM) product. As it usually happens, just when you’re done with the Salesforce implementation, you’re immediately faced with the challenge of consolidated reporting. It won’t be long before the Vice President of Sales asks you to integrate sales data residing in Salesforce.com and your on-premise data sources. In this case, the customer went to Dreamforce (the Salesforce premier conference) in search for integration options and was advised to solve the report consolidation issue by … migrating their multi-terabyte data warehouse to Salesforce.com!

I’m sure that this approach makes perfect sense to Salesforce.com, but it’s hardly in the customer’s best interest. First, although Salesforce is extensible and you can add custom objects (tables), Salesforce.com is not designed to host relational databases. As far as I know, it doesn’t have ETL tools, an analytical layer, and comprehensive reporting capabilities. Second, even with the enormous recent strides in cloud computing and ever decreasing storage prices, it’s hard to imagine anyone moving a data warehouse to the cloud. It’s just cost-prohibitive to do so. Third, there are data logistics challenges to populate a cloud-based data warehouse, such as to upload gigabytes of data from on-premises databases to the cloud over Internet.

At Prologika, we advise our customers to keep data where it belongs: operational data in the on-premise data warehouse and sales data in the Salesforce.com cloud. By design, the data warehouse is an enterprise repository for storing and consolidating data from operational data sources. We design Integration Services packages to retrieve data by integrating with the Salesforce.com web service and importing the data into the data warehouse. This opens all kinds of interesting data analytical possibilities, such as implementing forecasting reports that combine actual and opportunity revenue.

When the customer requires reports that source data from the data warehouse, we implement a web service end point residing on the customer’s premises that allows Salesforce.com to pull data from the data warehouse and cubes. Or, if it’s acceptable for the sales people to be logged to the customer’s network, we extend Salesforce to pass parameters to operational reports hosted on premise, such in SharePoint. This bi-directional integration allows our customers to keep data where it belongs but allows each system to obtain data from the other system.

Sometimes, it’s better to keep your head and data out of the cloud no matter how modern and exciting this might sound.

101112_0155_YourDataWar1

MVP for Eight Years!

/0 Comments/in /by

Just got the news that my MVP (SQL Server) award got extended for another year! This is my eight renewal.

Fix for “Wide” MDX Query Performance Degradation

/0 Comments/in /by

We ran into a situation where a wide MDX query requesting many regular and semi-additive measures side by side can degrade the Analysis Services server performance. As I explained in this connect bug report, the customer had a Reporting Services report that includes many measures (close to a hundred). Users insisted on having this report to be able to export all results in Excel and pivot on them. Of course, one of the advantages of having a cube is to make this exact requirement unnecessary but old habits die hard. We’ve noticed that when the report includes 75 measures from a single measure group, the query executes within 20 seconds. However, when adding more measures from the same measure group, the query performance degrades to minutes. The measures are either regular (persisted) measures or simple calculated member wrappers and don’t use any intensive formulas or scope assignments. The important point is that the query requests both additive and semi-additive measures (in our case the semi-additive measures use the LastChild function).

Microsoft determined that the performance degradation occurs when the number of overlapping calculations for a part of the cube space exceeds a threshold causing the server to switch from block mode to cell by cell evaluation for the affected query space. Microsoft was quick to provide us with a private fix that addresses the issue by distinguishing when the overlapping calculations are due to semi-additive measures vs. scope assignments in the cube script or query scope calculations (the reason the original limit was present). It will also allow the administrator to configure the threshold by adjusting the MaxCoverItemOverlapCount setting in msmdsrv.ini file although raising this number too high could result in negative performance on some cases where the cell by cell evaluation of highly complex calculations is faster than the block evaluation method. The official fix will be included in CU4 for SQL Server 2012 expected in mid-October 2012. I’m not sure about if the fix will make it to the older versions of SQL Server.

SQL Server 2012 CU3 Available

/0 Comments/in /by

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:

http://support.microsoft.com/kb/2703193

http://support.microsoft.com/kb/2708786

Prologika is Microsoft Gold Business Intelligence Partner

/0 Comments/in /by

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!

081712_1443_Prologikais1

Microsoft Case Study for Recall and Prologika

/0 Comments/in /by

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.

Making Big Data Real from TAG BI

/0 Comments/in /by

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.”

What’s New in Office 2013 BI: Part 5 – New Features in PerformancePoint 2013

/0 Comments/in /by

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.

What’s New in Office 2013 BI: Part 4 – New Features in Excel Web Reports

/0 Comments/in /by

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.

080612_1336_WhatsNewinO1

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.

080612_1336_WhatsNewinO3

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.

What’s New in Office 2013 BI: Part 3 – Improved Productivity

/0 Comments/in /by

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.

073012_0145_WhatsNewinO1

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.

073012_0145_WhatsNewinO2

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.

073012_0145_WhatsNewinO3

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.

073012_0145_WhatsNewinO4