Prologika Newsletter Fall 2017

/0 Comments/in , /by

Business Performance Management

 

As I’m writing this newsletter, Florida and Georgia (I live in Atlanta, GA) are in the midst of planning for hurricane Irma, which is expected to be one of the most powerful Atlantic Ocean hurricanes in recorded history. I hope that this won’t be the case and we don’t have to deal with a disaster of the magnitude of hurricane Harvey that took place just two weeks ago in Houston, TX. To avoid and mitigate this, a great deal of planning happens at every level: from government to citizens. Did you know that over 5 million of people in Florida were evacuated, making this the biggest US displacement of people to date? Speaking of planning, how does your company approach Business Performance Management? Do you use Excel spreadsheets or expensive high-end planning software? Next, I’ll share how Prologika helped a large organization improve its budgeting and planning process, powered by a cost-effective solution based on Microsoft BI.

Why Business Performance Management?

Wikipedia defines Business Performance Management (BPM) as “a set of performance management and analytic processes that enables the management of an organization’s performance to achieve one or more pre-selected goals”. A simpler BPM definition might be a methodology to help the company predict its performance. An integral part of a BPM strategy is a process for Budgeting, Planning, and Forecasting that typically has the following workflow:

  1. Every planning year, a planner in the Financial department creates a budget for a certain number of future periods. The budget is revised and multiple versions are proposed to management. Once budget is approved, it becomes fixed for the duration of the planning year.
  2. As actuals come in, the planner works on forecast scenarios. Multiple forecast scenarios are typically prepared as the planning year progresses over time.
  3. The planner monitors the variance between actuals and budget, actuals and forecast versions, and between forecast versions to improve the planning accuracy.

Business Needs

When it comes to Finance, nothing is simple and BPM is no exception. In fact, it very well might be that that your BPM solution might be the most complicated software you’ve ever developed. The temptation is to buy a prepackaged software but even that route would require a lot of customization and compromises. To gauge complexity, let’s look at some of the business requirements we faced.

090917_2102_PrologikaNe2.png

Similar to a Telco, this company has customers and service plans. As a B2B organization, their customers are other companies. Planning is done at the customer and service plan level, and across multiple subject areas. For example, the Excel report shows a subset of the measures for planning growth of the customer base. The planner can change the budget and forecast values of some measures, called drivers. For example, the planner can overwrite Gross Adds for future periods. Further, the planner can overwrite a rule in each cell to change how the driver cell value is computed. The rules are:

  • 0 (default rule) – By default, planned values are calculated as 3-mo average of previous periods. For example, Gross Adds for Aug 2017 for the first plan is 221 and has a Gross Adds Rule of 0. This means that the system will calculate the value as a 3-mo simple average of the values of the previous three months (221, 218, and 223). Note that a previous month might fall in the Actuals range. Also notice that recursive calculations are required, where the value of each month is based on the same measure for previous periods.
  • 1 (6-mo average) – Instead of 3-mo average, 6-mo average is used.
  • 2 (seasonality index) – The value is computed by multiplying the driver values for the past 12 months by a seasonality index.
  • Any other value – The planner enters the driver value manually.

The driver measures are used to calculate other (output) measures. For example, Average of Subscribers has a formula that is based on Gross Adds and other measures. Finally, the drivers and output measures are allocated across a Chart of Accounts dimension (not shown on the report) to give managers the financial perspective of how the company is doing.

The Solution

Previously, the company has used an Excel-based home-grown solution. However, the process was manual, lengthy, error-prone, and it couldn’t give management a quick and accurate picture of the company’s performance. A Business Analytics group was responsible for retrieving and consolidating actuals. Then, the Finance group would manually input actuals into an Excel template. Complicated Excel formulas were used to calculate drivers and output measures but even Excel had difficulty with more complex recursive measures. Most of the time and effort was spent on working the system than on business performance management. The company looked at other high-end financial solutions, with a starting price tag of $300K, plus yearly maintenance fees and consulting feeds. At the end, the company entrusted Prologika to implement the solution.

We used Analysis Services 2016 Multidimension, Initially, we considered Tabular but we realized that the solution complexity exceeded the Tabular capabilities, including:

  • No support for writeback – Currently, Tabular doesn’t writeback although third-party solution such as Power Planner can be overcome this limitation.
  • No support for recursive measures – Currently, Tabular doesn’t support recursive measures, where the measure DAX formula references the same measure.
  • No scope assignments – Tabular doesn’t support scope assignments for allocations.
  • No parent-child dimensions and hierarchy functions – Tabular doesn’t support parent-child hierarchies and functions for navigating the hierarchy, such as Parent, Children, etc.

This is a classic example of using the best tool for the job. While Tabular could be a good fit for perhaps 80% of semantic models out there, Multidimensional is probably your best bet for Financial projects and projects that require massive data volumes. No tool is perfect. “When all you have (know) is a hammer, everything looks like a nail” doesn’t work for BI.

MS BI Events in Atlanta

  • Atlanta MS BI and Power BI Group: “Did Qlik run out of BI power?”presentation by Carlos Rodriquez on 9/25
  • Atlanta MS BI and Power BI Group: “Topic: TBD” presentation by Slalom Consulting on 10/30

As you’d probably agree, the BI landscape is fast-moving and it might be overwhelming. If you need any help with planning and implementing your next-generation BI solution, don’t hesitate to contact me. As a Microsoft Gold Partner and premier BI firm, you can trust us to help you plan and implement your data analytics projects, and rest assured that you’ll get the best service. Regards,

Teo Lachev

Teo Lachev President and Owner
Prologika, LLC | Making Sense of Data
Microsoft Partner | Gold Data Analytics

Power BI vs Qlik Presentation

/6 Comments/in , /by

Mark your calendar for the next meeting of our Atlanta MS and Power BI Group on September 25th when we will compare Power BI and Qlik.

Presentation:

Did Qlik run out of BI Power?

Level: Intermediate
Date:September 25, 2017
Time6:30 – 8:30 PM ET
Place:South Terraces Building (Auditorium Room)

115 Perimeter Center Place

Atlanta, GA 30346

Overview:Haters are gonna hate. Lovers are gonna love. What about you? Come decide by yourself whether you are a Qlik or a PBI guy/gal. This presentation is intended for anyone who wants to see these two BI tools in action. Carlos and Daniel will show the differences and similarities between QlikView and Power BI. They will perform side-by-side the following:

  • Extract and transform data
  • Create a data model (so to allow cross filtering)
  • Compare features side by side, including hiding/displaying visuals, creating linked objects, role-playing dimensions, what-if scenarios
  • Create a nice dashboard (which tool can actually do it?)
Speaker:Carlos Rodrigues has more than 18 years’ experience as an IT professional and has successfully proven that he is especially adept at translating complex business needs into flawlessly Business Intelligence solutions. In his current role, Director- IT with Brightstar Device Protection, he holds the responsibility of defining the strategic roadmap and overseeing the implementation of the company’s Business Intelligence. He is a recent Georgia Tech MBA graduate (go Jackets 🐝). He enjoys traveling to different places around the world, appreciating the local culture, savoring local culinary styles and mainly, taking thousands of photos using his Nikon gear.

Daniel Granadillo is a Business Intelligence Architect / Developer with more than 10 years of experience around Data Warehouse and Analytics projects. Advocate of the Microsoft BI stack and discovered love at first sight with SSIS. You can find me enjoying the outdoors when not embraced with SQL Server

Sponsor:Accelebrate is an IT training firm headquartered in Atlanta, Georgia, USA that provides on-site and online training on a wide variety of technologies for clients in North America, Europe, and worldwide. Accelebrate is a Prime Sponsor of the Atlanta Microsoft BI and Power BI Group.

Processing Tabular from Exotic Clients

/0 Comments/in /by

Processing Tabular models from SSMS or .NET client apps is pretty straightforward. For example, you can connect with SSMS and issue processing commands through the UI or via TMSL. You can also use custom apps with Tabular Object Model (TOM) and you can send XMLA commands directly if you have an app perhaps that’s already doing that.

For Azure AS, it’s generally a good idea to run the app sending the processing command in the same data center where the Azure AS server is. If your Tabular database gets data from an Azure database, such as Azure SQL Database, the database should also be located in the same data center for better processing throughput and to avoid data transfer fees between data centers.

But what if you want to issue a processing command from more exotic clients, such as Python? Or, incrementally process from SSIS? Start with creating a JSON processing script (assuming Analysis Services 2016 or higher, or Azure AS). For the following script, processes two tables and the latest partition of the third table with the Data Only option (loads only the data). All objects are processed in parallel and 10 is specified as a maximum degree of parallelism. Then, it processes the database with Calculate option to restore calculated columns, relationships, and hierarchies.

Next, assuming you’re processing Azure AS, use one of the Microsoft-provided libraries to authenticate against Azure AD. For example, use the Python library to authenticate and submit the JSON in a standard XMLA message.

{
  "sequence": {
  "maxParallelism": 10,
    "operations": [
      {
        "refresh": {
          "type": "dataOnly",
          "objects": [
            {
              "database": "DatabaseName",
              "table": "Table1"
            },
            {
              "database": "DatabaseName",
              "table": "Table2"
            },
            {
              "database": "DatabaseName",
              "table": "Table3",
              "partition": "Table3 Partition 2017"
            }
          ]
        }
      },
      {
        "refresh": {
          "type": "calculate",
          "objects": [
            {
              "database": "DatabaseName"
            }
          ]
        }
      }
    ]
  }
}
processing

Power BI What-if Analysis

/0 Comments/in /by

Veteran Excel users might have used the Excel What-if feature that let you try several different sets of values in one or more formulas to explore various outcomes (scenarios). The August release of Power BI Desktop introduces a similar feature. You start by defining a What If parameter from the Modeling tab.

Don’t confuse the What If parameter with a query parameter which is used in query parameter-driven properties, such as to change the data source the query connects to.

In the process of configuring the parameter, you define hardcoded minimum, maximum, and increment values. The outcome is two DAX measures. The first one generates the scale, while the second returns the selected value.

Discount Percentage = GENERATESERIES(0, 0.5, 0.05)

Discount Percentage Value = SELECTEDVALUE(‘Discount Percentage'[Discount Percentage])

 

Next, you can use the “Value” measure as any DAX measure. Typically, you would create a target measure that somehow depends on the What If parameter, e.g.:

Sales After Discount = SUM([SalesAmount]) – (SUM(InternetSales[SalesAmount]) * ‘Discount Percentage'[Discount Percentage Value])

Now you can see how the target value changes when you change the What If value.

Power BI What If parameters allow you to parameterize you DAX measures. Continuing on the same path, I’d like to see expression-based properties and filters, as well as the ability to parameterize fields on the report, such as to present the user with a list of measures, and rebind visualizations after the user selects which measure they want to see on the report.

Power BI vs. Pyramid

/0 Comments/in /by

I’ve never thought I’d see this one coming but today I got a marketing email from Pyramid Analytics (previously an ardent Microsoft partner) citing a head-to-head comparison with Power BI. Et tu, Brute?

“Let’s start with a clear pricing model – one that doesn’t require an online calculator to figure out annual costs. Let’s add in fundamental capabilities and features like dashboarding, KPI’s, dicing, time intelligence, parameterization, and asymmetric reporting. Let’s close out with a complete lack of narrative reporting capabilities. The Pyramid Analytics platform provides an enterprise class on-premises BI solution that delivers all of the above plus simple advanced analytics. It’s available now and at a price you can easily understand. There’s a clear choice. Click here for a head-to-head comparison and a limited time offer”

I pity any vendor that competes with Power BI, especially the ones that compete “head-to-head”. Pyramid must feel the heat to come up with this “The Choice is Clear” marketing banner. But to me it’s not very clear and to keep ’em honest, their costing comparison is fabricated. According to Pyramid, you have to cough up $66,000/year for 100 users. But wait, the asterisks below tells us “*If data becomes too large for a single P1 node, additional nodes can be purchased for an additional $60,000/yr”. The choice is clear, right?

081617_1705_PowerBIvsPy1.png

Actually, it’s not and you don’t need the online calculator. It’s true that if you go with Power BI Premium, you’d pay $66,000 but why would you for 100 users (or 50 viewers to be more accurate)? If you choose instead the per-user pricing model ($10/user/mo), then your cost would be $12,000 per year not considering any additional discount you might get from Microsoft. So, that’s $3,000 lower than Pyramid. And, you’ll get a cloud solution that improves every month, plus a free Power BI Desktop tool. Or, if you want to save even more, you can go on-prem by deploying to Power BI Report Server which might be covered by an existing SQL Server Enterprise Edition license. Then, your software cost might be zero, plus one or more Power BI Pro $10/mo licenses for report authors.

Aside the Power BI Premium-only features, from a pure costing perspective switching to Power BI Premium makes sense above 500 users. It’s simple math.

About the second “Head-to-Head Comparison” page that shows that Pyramid outshines Power BI in every possible way, it doesn’t mean much without substance. I’m looking forward to the extended banner with more detail that explain how Pyramid exceeds the Power BI dashboarding, KPI, dicing, time intelligence, parameterization, and asymmetric reporting (whatever this means) capabilities. As far as “complete lack of narrative reporting capabilities”, this is not true either. Power BI has a Narative Science custom visual and Pyramid should know this.

Creating PBI Detail Reports

/0 Comments/in /by

Related to the previous blog, let’s show how to create a detail report in Power BI that mimics an Excel PivotTable report in a flattened layout. Consider the report below. To create it, use the Matrix visual and add the necessary fields to the Rows bucket and measures to the Values bucket. By default and similar to Excel, Matrix would use a Stepped layout so that each time you expand a field, the next field would use the same column to minimize horizontal space. In the Format tab, in the “Row headers” section there is a setting “Stepped layout” that is easy to miss. When you switch it to Off, Matrix is now configured to mimic the Excel Tabular (flattened) report layout. Now right-click on the report each field that you want to expand, and then click “Expand to next level”.

081217_2015_CreatingPBI1.png

There are currently two limitations that I hope will be lifted soon. There isn’t an option to expand all fields in one step so you have to expand one field at the time. More importantly, currently there isn’t a way to disable specific totals. The “Total row” option in the Format tab toggles on/off all subtotals. Having report subtotals might lead to a performance issue (see my previous blog for more detail). UPDATE 8/14/2017 – The latter limitation has been lifted in the August release of Power BI Desktop, which now supports configuring row subtotals per level (turn the “Per row level” to on in the Subtotals section of the Format tab) .

Considerations for Detail Reports

/0 Comments/in /by

Nobody likes watching a report spinny. Interactive detail reports that perform well from an Analysis Services semantic layer have been the bane of my BI career. A “detail report” is a report that requests data at a lower level, e.g. policy in the insurance business, customer in Sales, etc. A detail report typically has many dimension attributes, eustomer Name, Account Number, Product Name, Product Number, etc. And, the more columns you add, the slower the report gets. The reason why such reports don’t typically perform so well when generated from a semantic layer is that Analysis Services is not SQL Server.

Multidimensional is an attribute-based model and the server cross joins the member values when you add attributes to the report. Don’t be misled by the “relational” nature of Tabular either. Its database engine (xVelocity) is an in-memory columnar database that still cross joins the column values. That said, Tabular should give you a performance boost for two reasons. First, its in-memory nature is generally faster than Multidimensional. Second, Excel has been optimized for Tabular, as I explain in my “Optimizing Distinct Count Excel Reports” blog, thanks to the undocumented PreferredQueryPatterns settings (although not listed in the msmdsrv.ini, it defaults to 1).

In a recent project, I migrated a customer from Multidimensional to Tabular. Besides other benefits, such of elimination of snapshots, reducing dramatically ETL time and analyzing data as of any date (not just at the month end), the customer wanted to improve performance of Excel detail reports. Previously, some of the detail reports would never return. After the migration, these reports would execute within a minute, and in seconds if the Excel subtotals are disabled.

Here are some tips you might find useful if you’re tasked to produce detail reports:

  1. If the detail reports don’t require too much interactivity, consider implementing them as SSRS paginated reports that connect directly to the database. The chances are that this approach will give you the fastest performance as the Database Engine is designed to retrieve data in sets by rows and the number of columns won’t probably affect report performance (unless of course many joins are required).
  2. If it’s desired to connect these reports to a semantic layer, consider Tabular because it’s better for wide & flat results.
  3. If Excel is used as a front end, consider Excel 2016 as Microsoft has made various performance improvements for detail reports.
  4. Consider disabling Excel subtotals, as explained here. In my scenario, a detail report with no subtotals would execute under 15 seconds. However, if I enable just one column subtotal, Excel switches to a completely different query pattern (with many nested DrilldownMember levels) and the report query would take a minute. That’s because now the query needs to obtain the subtotals for each group from SSAS. Since we’re at the mercy of the Excel MDX query generator, I hope the Excel team finds a way to produce more optimal MDX queries. Ideally, a future Excel release would allow binding Excel native tables directly to SSAS with ability to define subtotals and optimized queries to load the data.
  5. In my experience, Power BI reports that generate DAX don’t perform necessarily any better. To make things worse, while the new PBI Matrix visual can be configured to a flattened layout, it doesn’t currently support disabling specific subtotals (currently, you can remove all row subtotals or have them for each and every column). And asking for subtotals for every column might not only contradict business requirements but it could also severely affect performance. UPDATE 8/14/2017 – The August release of Power BI Desktop supports configuring row subtotals per level.

Many thanks to Akshai Mirchandani for the SSAS product group for not losing patience throughout all these years from my complaints on this subject.

081217_1845_Considerati1.jpg

MVP For 14 Years!

/0 Comments/in , /by

Got awarded for Microsoft Most Valuable Professional (MVP) – Data Platform again. This will make my 14th consecutive year to be recognized by Microsoft for my expertise and contributions to the community! Apparently, it became difficult (or not cost effective) to count years, so the ring is for 2017-2018 but the tower doesn’t lie.

What’s ROI for BI?

/0 Comments/in /by

I often ask prospects if they’ve done any estimates of the value that the envisioned BI solution would bring to them. The Microsoft Solution Framework actually recommends partners do this during the planning phase. Alas, the answer is almost always negative. Then I ask what criteria would be used to evaluate if my price (I give fixed value-based prices as I don’t sell time) would yield a good ROI and then there is an awkward pause. Instead, the buyer typically uses a top-down approach for selecting a consultant. The buyer either has some budget constraints and solicits multiple quotes to find the “best” fit. There is nothing wrong with this, of course, given that ROI for BI is notoriously difficult to determine.

Consultancies and advisory firms have some sort of a “seat-of-the-pants” methodology to help the customer come up with ROI if this is deemed important. But you might not have to go through this exercise as research companies has done the math for you. For example, Nucleus Research has estimated that BI delivers 1300% ROI!

“In analyzing Nucleus ROI case studies on analytics, we found the average returns from analytics have been increasing, reaching $13.01 for every dollar spent in 2014 from just $10.66 in 2011.”

Often, the benefits of an effective and insightful BI solution can only be realized after the customer has started using the solution. I’m always delighted when customers report that our solution has paid for itself in the first year or so. For example, an insurance company that has found that customers underreport their risk exposure and justifiably increased premiums. Or, another client that has found that data analytics transformed the organization’s data into a key strategic business asset, empowering employees like never before.

What’s your ROI for BI?

Running ETL on AlwaysOn Primary Replica

/0 Comments/in /by

Scenario: Consider a SQL Server configured for AlwaysOn Availability Groups where a set of databases can fail over to another node. You have SSIS jobs and you created them on both nodes. However, you need to check if the current node on which the job is running is the primary node. If you don’t do this, the job fails because the database isn’t accessible.

Solution: In every SQL Server Agent Job, add a first step to check if one of the replicated databases in the AlwaysOn availability group is the primary replica:

IF
sys.fn_hadr_is_primary_replica
(‘<replicated dataset name here>’)
= 0

BEGIN


EXEC msdb..sp_stop_job
N’DW Daily Load’;

END

This script checks if the database is a primary replica. If this is not the case, the script stops the next step, which in this case is the “DW Daily Load” step.