Power BI Visual Embedded in SSRSPreview

Posted on

in PASS , Microsoft announced that “Technical Preview of Power BI reports in SQL Server Reporting Services now available” (https://blogs.msdn.microsoft.com/sqlrsteamblog/2016/10/27/technical-preview-of-power-bi-reports-in-sql-server-reporting-services-now-available).

Anyway we can only taste it by using Azure Marketplace. Now I have the VM ready (when building this VM, make sure using default size, be careful this is costly! so after testing, stop it.).

First impression: fantastic! Now we can really control the PBI version:).

Some screenshots

loading speed was pretty quick.

next step, I will get the VHD from Azure and load into my test machine to reduce cost.

Really excited to see this feature, or to see, a milestone for SSRS.


Probability to get a lucky draw?

Posted on Updated on

Yesterday we had annual dinner with all colleague. Of course, lucky draw must be carried out.

All of our names were written in a piece of small paper for each and thrown into a box. The lucky draw was performed about 30 rounds. Each time a paper was pulled out, the person with the name same as the one from that paper would get a gift. After that that paper was discarded (without replacement).

Everyone was excited. All hoped to get drawn out. Suddenly I was thinking, what is the chance for me to get a gift?

Yeah, I don’t need a precise number but just a rough one. So I took this formula from Excel:

BINOM.DIST.RANGE(30,1/500,1) = 5.66%

Ah ja, of course not so accurate – no consideration of “no replacement”, and some minor one. But sure enough, it should be between BINOM.DIST.RANGE(30,1/450,1) and BINOM.DIST.RANGE(30,1/500,1). That’s 5.66% – 6.25%. Not too bad:)

And, I got one. Bingo! Lucky enough!

Power BI Desktop October update

Posted on Updated on

Microsoft Power BI team is really amazing! This month they brought up 23 new features. Among them, I ticked the best features (I thought):

PowerBI Oct

More details:


SQL Server Analysis Service 2016 CTP 2.3 DirectQuery in action (by Kasper de Jonge)

Posted on Updated on

In my previous blog post “What’s new in Microsoft SQL Server Analysis Services Tabular models in SQL Server 2016 CTP 2.3” I introduced some of the enhancements coming to SQL Server Analysis Services Tabular –and many more will be coming in the next CTPs. In this article, I’d like to show you what the combination of Super DAX and improved DirectQuery could mean to you.

For illustration, I loaded a dataset with 220 million rows in a fact table and several other dimension tables into a relational SQL Server 2016 CTP 2.3 database, I then enabled columnstore indexes on all the tables.  The hardware was a Lenovo W530 laptop, so no server hardware by any means. Your mileage might vary, but I would imagine much better performance on a proper server with lots of RAM.

Next, I built a Tabular model in DirectQuery mode on top of this relational database by using SSDT for Visual Studio 2013 (this version also works with SQL16). For comparison, I installed two instances of Analysis Services, SQL Server 2014 and SQL Server 2016 CTP 2.3, and deployed the same model to both. All running on the same laptop.

Finally, I built a report by using Power BI Desktop. I connected to the SQL Server 2014 SSAS instance and started building my report:

Looks beautiful doesn’t it :). I added some realistic visuals like a matrix as well as these are usually the worst for performance.

Now, let’s take a look at a SQL profiler trace to see what is generated when I refresh the entire report:

The complete refresh of the report took 3 minutes and 34 seconds. 15 source queries were sent to SQL Server.

Let’s see what happens when I use the same report against my SQL Server 2016 CTP 2.3 instance:

The same report refreshed in 10 seconds instead of 3 min and 34 seconds, a whopping 20 times faster! And Analysis Services only generated five SQL queries!

That is a significant improvement you can start experiencing today. You will see the most improvement when using Power BI Desktop and Power BI service together with the Analysis Services connector but improvements can also be expected when using Power View in Excel and SharePoint. And as mentioned in my previous blog post, we are planning to ship even more performance updates in the next SQL Server 2016 CTPs.

So start using SQL Server 2016 CTP2.3 today and see for yourself! Here are the steps you can try out:

Use SuperDAX and the new DirectQuery with an existing model:

  1. Download SQL Server 2016 CTP2.3 here.
  2. Install Analysis Services from the 2016 setup on your development machine. You can install it as a new instance so you can run it side by side with SQL Server 2014.
  3. Backup the database you want to use and restore it to the SQL Server 2016 CTP instance.
  4. Point Power BI, Power BI Desktop, or any other client tool to the restored database.
  5. You can now enjoy improved performance with your tabular model.

To develop a new model using the new DAX functions or DirectQuery functionality:

  1. Download SQL Server 2016 CTP2.3 here.
  2. Install Analysis Services from the 2016 setup  on your development machine. You can install it as a new instance so you can run it side by side with SQL Server 2014.
  3. Use SSDT for Visuals Studio 2013 and point the workspace database to the newly installed SQL 2016 instance.
  4. Open SSDT and then open your Tabular model solution.
  5. Start using the new DAX expressions.
  6. Deploy to your newly installed SQL Server 2016 CTP instance.
  7. Be advised that the new DAX expressions will not work with previous versions of Analysis Services, SQL Server 2016 is required.

I hope you are excited about the upcoming SQL Server 2016 release. This is just the beginning. More will come over the next couple of months, but I’m repeating myself. Stay tuned!

From <http://blogs.msdn.com/b/analysisservices/archive/2015/09/08/sql-server-analysis-service-2016-ctp-2.3-directquery-in-action.aspx>

new feature in Excel 2016: Forecasting

Posted on Updated on

Just read from Microsoft Blog, that Excel2016 will have forecasting feature! Previously I have only seen this feature was once in Office365′ PowerBI service though was quickly “mysteriously” disappeared – now it’s the first time at least for me – come back, in Excel?

New forecasting capabilities

We also revamped the forecasting capabilities provided in Excel 2016. In previous versions, only linear forecasting had been available. In Excel 2016, the FORECAST function has been extended to allow forecasting based on Exponential Smoothing (i.e. FORECAST.ETS() …). This functionality is also available as a new one-click forecasting button. On the Data tab, click the Forecast Sheet button to quickly create a forecast visualization of your data series. From the wizard, you can also find options to adjust common forecast parameters, like seasonality, which is automatically detected by default and confidence intervals.

Business Analytics 3 - b

Limit of 10 Reports per Dataset/Data Model

Posted on Updated on

NOTE: This information is good as of 7/22/2015 and is subject to change.

*** Update ***

I had posted earlier that this was specific to Free users, but it has nothing to do with Free or Pro. Reposted to reflect the accurate behavior.


I was looking at the Power BI Community Site and someone had indicated that they cannot save a report.

Unable to save report

When looking into that Activity ID, it appears that we have a limit of 10 reports.  This has nothing to do with if you are a Free or Pro user, but rather a resource limitation based on your Dataset/Model.

When you try to save the 11th report against the same Dataset, you get the error above.  If you create an 11th report against a different Model it will work.

Adam W. Saxton | Microsoft Business Intelligence Support – Escalation Services

Windows 10 Upgrade: What This Means for Your Endpoint Security

Posted on Updated on

On July 29th, Microsoft will be releasing their next major operating system, Windows 10. Unlike previous OS upgrades, this version does not require users to purchase the new OS. The existing Windows 7 and 8 end users might initiate an upgrade directly on their computers, making it more difficult for IT admins to enforce version control.

At Symantec, we are committed to ensuring our customers: Symantec Endpoint Protection 12.1, Symantec Endpoint Protection Small Business Edition (hosted version) and Symantec Endpoint Protection Small Business Edition (on-prem), remain fully protected across all operating systems.

For Symantec Endpoint Protection 12.1, a maintenance patch release will be available on July 29, 2015. Customers will need to be current on maintenance to receive the maintenance patch update. For more information, visit our SEP 12.1 Windows 10 Knowledge Base.

Customers using Symantec Endpoint Protection Small Business Edition (hosted version) will experience an automatic system update on computers via Live Update targeted for July 29th. From July 29th onwards, any new endpoint installs of the hosted version of Symantec Endpoint Protection Small Business Edition will automatically have their machines protected should they wish to upgrade to Windows 10. Similarly, from July 29th onwards, any existing Symantec Endpoint Protection Small Business Edition customers will receive targeted patch updates to protect their machines on the new Windows 10 platform. These patch updates will automatically be rolled out later via Live Update to all endpoints, whether they are moving to Windows 10 now or at some point in the future.  This effectively allows IT  Administrators to proactively protect endpoints that move to Windows 10 at a later date. Existing customers must be on a current subscription to take advantage of these patch updates. Symantec Endpoint Protection Small Business Edition will automatically notify IT Administrators if any users decide to upgrade to Windows 10 without informing IT. In these instances, a new patch for Windows 10 will be pushed out to protect these user’s machines within a 1-2 hour window.  Symantec will also provide guidance for IT Administrators who wish to avoid this 1-2 hour window and push out the patch update immediately – this guidance can be found on Symantec Knowledge Base.

For customers currently running Symantec Endpoint Protection Small Business Edition 12.1 (on-prem), which has now reached End-of-Life and will not support Windows 10, you will need to migrate to the hosted edition in order to receive protection for Windows 10 systems.  Endpoint Protection Small Business Edition (on-prem) customers can migrate to the hosted edition.  For information on our automated migration process, visit: go.symantec.com/sbemigration