• Processing Tabular from Exotic Clients

    September 7, 2017 / No Comments » / 1559 Views

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

  • Power BI What-if Analysis

    August 28, 2017 / No Comments » / 1539 Views

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

  • Power BI vs. Pyramid

    August 16, 2017 / No Comments » / 6599 Views

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

  • Creating PBI Detail Reports

    August 12, 2017 / No Comments » / 5644 Views

    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". There are currently two limitations that I hope will...

  • Considerations for Detail Reports

    August 12, 2017 / No Comments » / 2363 Views

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

  • MVP For 14 Years!

    July 11, 2017 / No Comments » / 64288 Views

    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?

    June 29, 2017 / No Comments » / 18699 Views

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

  • Running ETL on AlwaysOn Primary Replica

    June 28, 2017 / No Comments » / 8341 Views

    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.

  • Power BI vs. Qlik

    June 22, 2017 / 9 Comments » / 2676 Views

    After Tableau and SiSense, Keeping 'em Honest continues with Qlik. Now, after they sold the company and took a hit in the latest Gartner's quadrant, I didn't expect much of a completion from Qlik but every now and then I run into a customer considering Qlik and willing to share Qlik's feedback on how Qlik outshines Power BI in every possible way (can't share these documents due to NDA although they are interesting and entertaining stories). But to be fair to Power BI, I'll enumerate next a few reasons that customers have shared of why they prefer Power BI over Qlik and from my experience in in helping several customers transition to Power BI from Qlik. I'll focus on Qlik Sense, which is the Qlik's latest tool that competes head to head with Power BI. Chasing the perfect tool (hint: there is none)? Instead, focus on architecture and data quality....

  • Scaling out SSIS in SQL Server 2017

    June 11, 2017 / No Comments » / 8014 Views

    SQL Server 2017 introduces the ability to scale out SSIS. The primary scenario is to enable customers to scale out their SSIS execution at the package level. Imagine a retail business that has about 300 packages to run at the end of the day to push transactional data/finance data from multiple stores to the centralized data warehouse for reporting. All the 300 packages need to run at night but they also need to finish by 6 AM in the morning before new business day starts. As business grows, data size grows, and it takes longer time to run the 300 packages and it is becoming more difficult for the ETL processing window to finish before 6 AM. With SSIS scale out feature, you can now set up the scale out cluster with 1 master and N workers, so that the master can automatically distribute the packages to the workers based...

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.

Syndication