Blog – Page 41 – Prologika

Happy Birthday, Power BI!

July 24, 2016 / No Comments »

Power BI is one-year old. it's hard to imagine that one year has passed but here we are. The momentum behind Power BI is huge both from development and customer perspectives. It's exiting to see a product that evolves so fast based on feedback from users. So, there will be many more birthdays. And for the nay sayers out there, no, Power BI is not immature as its building blocks (Power Pivot, Power Query, Power View, Tabular, Azure) have been around for many years. Anyway, it's time to recognize all the effort that went behind the new Power BI. Happy Birthday from the community! [embed]https://youtu.be/An_8wf4RwBw[/embed]
Atlanta MS BI Group Meeting on July 25th

July 24, 2016 / No Comments »

MS BI fans, join me for the next Atlanta MS BI and Power BI Group meeting on July 25th at 6:30 PM. Shabnam Watson will teach you MDX fundamentals. Pyramid Analytics, our sponsor, will show us why they are Microsoft Power BI preferred vendor. And I'll demo the Power BI Publisher for Excel. Rate this meeting http://aka.ms/PUGSurvey, PUG ID: 104 Presentation: A SQL Developer’s Guide to MDX Basics Level: Intermediate Date: Monday, July 25th, 2016 Time 6:30 – 8:30 PM ET Place: South Terraces Building (Auditorium Room) 115 Perimeter Center Place Atlanta, GA 30346 Overview: MDX is an industry standard query language for OLAP systems and is used to query Microsoft Analysis Services (SSAS). Custom MDX queries can be authored in SQL Server Management Studio (SSMS), Reporting Services, Power BI and custom applications. While SQL and MDX share some common keywords, they have very different syntax and concepts. This session is for anyone...
Is Your BI Strategy Cost Effective?

July 24, 2016 / No Comments »

Have you looked at your BI spend recently? Sometimes, I'm puzzled by the enormous spending in "this is how we've always done it" initiatives that probably could be replaced and done much more efficiently with modern technology and cloud computing. I had a recent conversation with a Chief Analytical Officer of one of the US largest financial organizations. They have outsourced hosting of their 5 TB data warehouse to another company that charge them more than 100K per month. And this is just for hosting on a single SMP SQL Server with no fault tolerance and SLA in place! Was he surprised to find that this data warehouse could be hosted in SQL Server Data Warehouse on Azure for well under 10K per month? Not to mention that besides x10 cost savings, they'll get a Massive Parallel Processing (MPP) system on a par with Teradata and Netezza, with the ability...
Presenting for Steel City SQL User Group, Birmingham

July 18, 2016 / No Comments »

I'm presenting in person "What's New for BI in SQL Server 2016" for the SQL User Group in Birmingham, AL tomorrow, July 19th starting at 6 PM local time. Steel City SQL July 2016 Meeting Language: English Event Type: In-Person Online Meeting URL: None RSVP URL: https://steelcitysqljul2016.eventbrite.com Join us Tuesday the 19th in Room 311 of Russell Hall on the Samford University campus for the July meeting of Steel City SQL. This meeting will feature MVP Teo Lachev presenting What's New for BI in SQL Server 2016. Food and beverages for this month's meeting will be provided. If you plan on attending this month's meeting let us know by registering at steelcitysqljul2016.eventbrite.com so we can plan accordingly. Hope to see you there. When iCal UTC : Tue, Jul 19 2016 23:00 - 00:30 Event Time : Tue, Jul 19 2016 18:00 - 19:30 Central Daylight Time Where 311 Russell Hall - Samford University Birmingham, Alabama What's New for BI in SQL Server 2016...
Aggregates over Aggregates in DAX

July 15, 2016 / No Comments »

Sometimes, you might run into a scenario that requires an aggregate over an aggregate. Consider the following report: The AvgOrderAmount has a simple calculation: SUM(Sales)/SUM(Qty). This calculation is applied uniformly. The Total line would divide 300/7 and will return 42.86. However, what is the user wants the result in the DesiredAvgOrderAmt column which produces a simple average over the details (50+40)/2. This could be accomplished by creating a base measure for the detail calculation: DesiredAvgOrderAmtBase := DIVIDE ( SUM ( [Sales] ), SUM ( [Qty] ) ) Then, the DesiredAvgOrderAmt formula would be: DesiredAvgOrderAmt := IF ( HASONEFILTER ( Customer[Customer] ), [DesiredAvgOrderAmtBase], AVERAGEX ( VALUES ( Customer[Customer] ), [DesiredAvgOrderAmtBase] ) ) HASONEVALUE would return TRUE when the calculation is performed at the detail level and FALSE in the "grand totals". In the latter case, it performs a simple average over the detail aggregates. Think of it as performing a second pass over the details to produce an aggregate over aggregates.
Upgrading Power BI Desktop Models to Tabular

July 10, 2016 / 2 Comments »

One great feature of Microsoft BI has been the continuum from personal BI to organizational BI. For example, a business user can start small with an Excel Power Pivot model which IT can restore at some point to a scalable Tabular model. In fact, if you know Microsoft personal BI (Power Pivot or Power BI Desktop), you already know 80% (or even more) of SSAS Tabular. Unfortunately, currently there isn't a supported way to restore Power BI Desktop models to Tabular or create a Tabular project from a pbix file. The reason is that because Power BI Desktop is changing on a monthly basis, it's ahead of Tabular and currently Power BI Desktop doesn't support backward compatibility. However, the following approach worked for me to upgrade Power BI Desktop to Tabular 2016. Power BI Desktop has a Tabular child process which is the workhorse for all data crunching you do...
Implementing Qlik-style Variables in DAX

July 7, 2016 / 4 Comments »

A large publicly-traded organization is currently standardizing on Power BI as a single BI platform as a replacement of Qlik and Tableau. They analysts have prepared a gap analysis of Power BI missing features. On the list was QlikSense variables. The idea is simple. The user is presented with a slicer that shows a list of measures. When the user selects a measure, all visualizations on the report dynamically rebind to that measure. For example, if SalesAmount is selected, all visualizations bound to the variable would show SalesAmount. However, if the user selects TaxAmt than this measure will be used. As it stands, Power BI doesn't have this feature but with some DAX knowledge, we can get it done by following these simple steps: In Power BI Desktop, click Enter Data and create a table called Variable with a single column Measure. Here, I'm hardcoding the selections but they can...
Presentation for SQL Saturday Chattanooga

June 19, 2016 / No Comments »

SQL Saturday Chattanooga will take place on June 25st at UT Chattanooga EMC Building, 784 Vine Street, Chattanooga, Tennessee, 37403, United States. Your humble correspondent will sponsor the event and present "What's New for BI in SQL Server 2016" scheduled to start at 3:45 PM (the organizers decided to keep the best for last J). Join me for an hour of coverage of my favorite SQL Server 2016 new BI features. Get your questions answered. SQL Server 2016 delivers many data analytics and information management new features and enhancements. Join this session to discover what's new for BI in Database Engine, SSRS, SSIS, SSAS, and MDS. I'll also explain the SQL Server 2016+ near-future roadmap that will help you implement novel solutions and cut cost.
Power BI Quick Calculations

June 19, 2016 / No Comments »

DAX is very powerful but it could entail a steep learning curve for novice users. Power BI Desktop has started on the road of delivering pre-packaged calculations. The first one is Percent of Grand Total. Once you add a measure to the Values zone, you can expand the measure drop-down and click Quick Calc. This is similar to how you would pick one of the default aggregation functions. Interestingly, Power BI Desktop doesn't add a new calculation when the Quick Calc feature is used. Instead, it probably creates an implicit DAX measure and you can't see the formula. The original measure is renamed to %GT <original measure name>. Prepackaged calculations are a frequently requested Power BI feature. Expect additional quick calculations to light up soon. As a recommendation for improvement, I think that it would be nice to be able to see the DAX formula behind the quick calc.
ADOMD.NET Connection Pooling

June 1, 2016 / No Comments »

I helped a customer to implement ADOMD.NET connection pooling with the goal to increase significantly the query throughput. Their predictive service platform had to scale to thousands of queries per minute. However, as it stands, ADOMD.NET doesn't have connection pooling. Normally, this is not an issue (see the implementation details of how SSAS handles connections here), but in this case the application was constantly opening and closing connections under a dedicated (trusted) account to send singleton DMX queries. Interestingly, performance counters wouldn't show pressure on the SSAS server but for some reason the client has blocking for long periods after a certain number of open connections (could be related to TCP/IP port exhaustion). The solution was to implement custom ADOMD.NET connection pooling. You can find the .NET source here. The code should be easy to follow. It uses a Queue class to cache up to 50 connections. When the client...