Blog – Page 35 – Prologika

MVP For 14 Years!

July 11, 2017 / No Comments »

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 »

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 »

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 »

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 »

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...
Power BI Excel Publisher Doesn’t Load

June 1, 2017 / No Comments »

Issue: You want to use the Power BI Excel Publisher but it doesn't load. You look at the Excel add-ins and you see that it's deactivated. You try to activate it and then you get an error. Solution: Copy the following Office PIA files to the add-in folder (C:\Program Files\Microsoft Power BI Publisher for Excel\bin). Microsoft.Office.Interop.SQLIS.12.0.nupkg OFFICE.dll Unfortunately, I don't know where these files are located. They were given to me by the Microsoft Support and I can't redistribute. If you can't find them, you can call MS support and reference support case: 117041015581533. Now when you open Excel, you should see the Power BI ribbon tab.
“Understanding Power BI Premium” Events

May 31, 2017 / No Comments »

I'll present "Understanding Power BI Premium" at three public events in June. June 8th, Carolinas Power BI Group in Greensville, SC at 6 PM June 24th, SQL Saturday Chattanooga, TN at 1:15 PM June 26th, Atlanta MS BI and Power BI Group, Atlanta, GA at 6:30 PM My calendar has the details. Microsoft Power BI has enjoyed a lot of attention and success since it became generally available in July 2015. But its licensing model and cloud-hosted limitations barred wide adoption, especially with larger organizations. The recently introduced Power BI Premium will change all of this. Join this session to learn how Power BI Premium will allow your organization to achieve: • Flexibility to license by capacity • Greater scale and performance • Extending on-premises reporting with Power BI Report Server • Embedded analytics
Understanding Writeback Target Allocation

May 19, 2017 / No Comments »

I'm working on architecting a financial planning solution powered by Analysis Services Multidimensional. One thing that might not be obvious is how Multidimensional selects the target of writeback allocation. In this case, planning will be done at Customer and Product level. With the default equal allocation when writing at the customer level, it might appear that writeback doesn't work correctly. You'd expect that only the cells that contribute to the aggregated value (10 in the screenshot below) will be affected by writeback. However, if the Customer and Product entities are in different dimensions, writeback will affect all products! The reason behind this becomes obvious if you right-click the pivot table, and from its options enable "Shows rows with no data". Then, you'll see all products appearing with each customer (customers are crossjoned with products). Recall that by default, the pivot table uses NON EMPTY in MDX query to exclude combinations...
Atlanta MS BI Group Meeting on May 22th

May 19, 2017 / No Comments »

MS BI fans, join me for the next must-attend Atlanta MS BI and Power BI Group meeting on May 22th at 6:30 PM. My esteemed friend, Stacey Jones from Microsoft will explain the predictive analytics capability of Cortana Analytics Suite. He'll demo a predictive analytics solution that was developed in a single day! It leverages Azure Machine Learning, Azure Data Factory, HDInsight, Spark, Power BI, and Intelligent Apps to deliver a solution that predicts the likelihood of flight delays for a customer. I will do a quick demo of the latest Power BI cool features. Our premium sponsor, TEKSystems, will sponsor the meeting. Presentation: Cortana Intelligence Suite End-to-End Level: Intermediate Date: May 22nd, 2017 Time 6:30 – 8:30 PM ET Place: South Terraces Building (Auditorium Room) 115 Perimeter Center Place Atlanta, GA 30346 Overview: The Cortana Intelligence Suite provides tools to cover all types of business intelligence needs, from compelling dashboards...
What Does Power BI Premium Mean for You?

May 3, 2017 / No Comments »

I'm sure we've heard the announcements today about Power BI Premium. In fact, Power BI Premium is so important that Microsoft has positioned it as a new product under the Power BI marketing umbrella name instead of a new licensing model. Microsoft and industry experts covered the announcements well so I won't reiterate the obvious. You may wonder what these changes mean for you. Let's summarize. Power BI Portfolio In a nutshell, Power BI Premium targets larger organizations which have faced two issues with the current Power BI licensing model: No "reader" license. If a report has a Power BI Pro features, all users accessing reports would need Power BI Pro license. So, if you a report that used Power BI Pro features, such as gateways or live connections and you won't this report to be available to 1,000 users, you had to foot $10,000/month bill because everyone required Power...