Ignite News: Microsoft Fabric is Now GA (and more)!

Over the (roughly) last 6 months we all had the ability to play with Microsoft Fabric, when it went Public Preview at Microsoft Build.

Now that the keynote of Microsoft Ignite by CEO Satya Nadella has been delivered, and also the amazing in-depth Fabric session by Amir Netz, Arun Alagaratchagan: Make your data AI ready with Microsoft Fabric and Azure Databricks. Back then at Build, Satya called it:

"...the biggest launch of a data product from Microsoft since the launch of SQL Server!"

Generally available

But now, Satya went one step further and announced GENERAL AVAILABITIY of Fabric!

Also, Copilot in Fabric is now in public preview.

More updates

There's a ton of updates inside Fabric on existing features on the latest Fabric blog here.

And there are also some exciting new features:

• Seamlessly connect your data sources to Fabric
  The ability to create shortcuts was already there, where you virtualize data in OneLake without having to move or duplicate that data.. You can create shortcuts to another Lakehouse and Warehouse, but also to files on ADLS or even Amazon S3 or Google storage.
  The newest feature just announced is called Mirroring, where you can add and manage existing cloud data warehouses (and databases) in Fabric's Data Warehouse experience. The way this works, is like replication in SQL Server. Fabric replicates a snapshot of that database to OneLake in Delta Parquet files and keeps that in sync in near real time, which relies on the Change Data Capture feature of the underlying source. Initially it's supported for Azure Cosmos DB, Azure SQL DB and Snowflake, more sources will follow next year.
• Copilot in Power BI (public preview)
  Just be aware that Copilot will be rolling out in stages, 
• Smart Narrative is an existing visual in Power BI Desktop, now rebranded to Narrative with Copilot
• The November Desktop update let's you generate synonyms for your fields, measures and tables using Copilot.
• In the future, there's also going to be:
• a report creation experience
• a DAX writing experience
• Direct Lake support on Data Warehouse
  There's also an update on the size limits of your Fabric capacity and when it will fallback to Direct Query.
• Stored credentials for Direct Lake semantic model
  You can now specify a fixed identity (like a service principal) for a Direct Lake mode semantic model
• Pricing on Fabric is updated!
  Reserved pricing is now available, with a rough discount of around 41% off of Pay-As-You-Go pricing.
  
• OneLake integration for Import-mode semantic models is coming!
  This allows for a seamless (at least that's what Microsoft claims 😄) integration for your import Power BI Desktop models into OneLake. I wonder if this also implies that you can convert your import report to a Direct Lake mode model afterwards. It's not totally clear to me at this point.

[update on November 16th]

• Power BI Desktop November is also out!
  Power BI November 2023 Feature Summary
• DAX query view
• Stored credentials for Direct Lake
• Explore

Conclusion

Will I now go all-in on Fabric? "It depends", but probably not 😀

I think it still depends on a case per case basis. Is it a newish customer with not too many investments in other data platform services like Synapse/Data Factory. Then it makes sense to evaluate the requirements and see if it's worthwhile to start with Fabric, considering a lot of features are still in preview, and others are not there yet.

On the other hand, if it's an existing customer with real estate in Synapse and databricks for example, where they implemented a medaillon structure and have infrastructure running there, I'd seriously reconsider if it's worth moving to Fabric. I'm leaning towards a no for now.

Keep a look out on the official Fabric and Power BI blogs and Microsoft Learn for all new content:

• https://powerbi.microsoft.com/en-au/blog/
• https://blog.fabric.microsoft.com/en-US/blog
• https://learn.microsoft.com/en-us/fabric/get-started/whats-new

I'm updating this post live while the updates are rolling out, so come back later for more updates!😀

My Power Platform Summit Europe Recap

Last week the Power Platform Summit Europe was held in Amsterdam. I was there together with my colleagues Sander and Pim and next to the learning experience the conference gave me, it was really nice to join them!

Before starting my recap I’d like to point you to the following tool: Microsoft Office Lens, it’s available both on iOS and Android! In every session I was in there were always people taking pictures of the slides. I sometimes do it also, just because I want to make a note next to the slide or because it’s something in a demo I’d like to capture. But the point I’m trying to make here: do it with the Office Lens app, because then your pictures will look like this:

Pro tip for making pictures of the screen: please use the Microsoft Office Lens app, available on both iOS and Android! :-) #PowerPlatformSummit pic.twitter.com/zRvWwL3rNp

— Nicky van Vroenhoven (@NickyvV) 27 maart 2019

The information/sessions that stood out the most for me:

Line Krogh talked about Brons, Silver and Gold Power BI report templates:

• Gold: created by BI team on a managed Tabular model
• Silver: not created by BI team on a managed Tabular model
• Brons: not created by BI team on non-certified data sources

The Art of Gathering Effective BI requirements by Mico Yuk

• An interactive session where she also shared her spreadsheet to be used in discussion with end-users

Nikhil Gakwaed showed us some nice improvements in the Power BI Service:

• Comments on dashboards
• Organizational branding for Power BI Home

Callum Green did a session where one part about data profiling was interesting in particular:

• Data profiling in M (Table.profile)
• He also did some tweaking afterwards to get the profiling on the column and value level

The Value of Self-serve BI in a Large Organisation by Rishi Sapra:

• What-if scenario builder, which he will explain in-depth at the virtual Power BI Days

Demo of splitting the report and the data model by Just Blindbaek:

• This will be even better when Shared and Certified datasets are available, hopefully later this month!

Power BI Performance by Kasper de Jonge, David Magar and Bhavik Merchant:

• Avoid Bi-Di crossfiltering: you will go to hell! (according to Alberto Ferrari)
• Instead turn crossfiltering on in DAX: CROSSFILTER(Dim, Fact, OneWay/Both)

• Use variables, especially with IF
• Don't use IFERROR
• Use DIVIDE
• Model Star-schema/Snowflake
• Filter on Dimensions

A great session on Aggregation Tables by Reza Rad:

• Import mode, Direct Query and Dual
• Use DaxStudio to analyse the queries send by DQ

At the end of day 2 we had a nice view of the entrance to the conference:

Nice ending of a great 2nd day at the #PowerPlatformSummit! So many good sessions today! Good meeting so many friends again and also making new ones who share the same love for #PowerBI pic.twitter.com/pzvLZPypcc

— Nicky van Vroenhoven (@NickyvV) 28 maart 2019

In the morning I found some time to go running with Sander in the Amstel Parc just across the road from our hotel. Although it was very chilly, it was worth the early wake up!

Just came back from a 6K run with @SLKerstens, getting ready for the last day of #PowerPlatformSummit! pic.twitter.com/wWkkKr5zZz

— Nicky van Vroenhoven (@NickyvV) 29 maart 2019

Let me know if you agree with me or if you think you liked other sessions more at the Summit.

My next post will be a recap of the Power BI User Day 2019 in Utrecht where I also gave a presentation together with Jeroen Schalken.

@NickyvV

Bits of BI – June 29

In accordance with Thomas LaRock I decided to also start my round-up of news from around the block of SQL Server, Technology or anything vaguely related to BI. And I might include some other things as well if I feel like it.
My plan is to do a weekly round-up, let’s see how that works out.
My latest post was 2 weeks ago, but here’s a new one! As we’re starting to go into summer and holidays, I might skip one edition in August also.

But for now, here’s the next edition of Bits of BI!

• SSMS 17.8 is now available by Pam Lahoud (sqlgoddess)
  Download SSMS 17.8 or review the Release Notes to get started.
• Does It Matter Which Field Goes First in an Index? by Brent Ozar (@BrentO)
  Some index 101 knowledge, but thoroughly explained. And as Brent says: index field order matters a LOT.
• Biml Express 2018 is here!
  It includes support for VS 2017, SSIS package import and many other things. Cathrine (@Cathrine) and Andy (@AndyLeonard) also blogged about it. Go download Biml Express 2018 or check the change log for all updates.
• Announcing the GroupBy September Lineup by Brent Ozar (@BrentO)
  The next (online and free) GroupBy conference is on Friday September 7, and will contain mainly SQL sessions this time.
• Stairway to Azure DataBricks by Leila Etaati (@leila_etaati)
  Leila already started with Part 1 and Part 2, Part 3 will follow later. She starts with adding a Databricks environment with a Workspace and a Cluster. In Part 2 she goes more in-depth with uploading data to Azure Data Lake Store, clean the data with Scala and use R to visualize it and do predictive analytics. In Part 3 Leila will show the result in Power BI.

That concludes this edition of Bits of BI, feel free to share it with others!
@NickyvV

Bits of BI – June 15

In accordance with Thomas LaRock I decided to also start my round-up of news from around the block of SQL Server, Technology or anything vaguely related to BI. And I might include some other things as well if I feel like it.
My plan is to do a weekly round-up, let’s see how that works out.
My latest post was 2 weeks ago, but here’s a new one! As we’re starting to go into summer and holidays, I might skip one edition in August also.

So here’s the second edition of Bits of BI!

• 24 Hours of PASS: Summit Preview 2018 was held on June 12 and 13.
  24 1-hour sessions on Performance Tuning, Azure Data Lake, Digital Storytelling, Advanced R, Power BI and more. The recordings will be posted on the Learning Center.
• New Stack Overflow Public Database Available (2018-06) by Brent Ozar (@BrentO)
  There is a new (and bigger) version of the Stack Overflow database available for export which you can use to play with, tune queries or do demo’s on.
• Use Outlook Shortcuts to organize your favorite Power BI Reports and Dashboards by Chris Finlan (@cmfinlan)
• Advice from a tech Mom on getting teens to learn to code by Jen Stirrup (@jenstirrup)

That concludes this edition of Bits of BI, feel free to share it with others!
@NickyvV

Bits of BI – June 6

In accordance with Thomas LaRock I decided to also start my round-up of news from around the block of SQL Server, Technology or anything vaguely related to BI. And I might include some other things as well if I feel like it.

My plan is to do a weekly round-up, let’s see how that works out.

So here’s the first edition of Bits of BI!

• Query Folding in Power BI
• Power BI Desktop June Feature Summary
• Subscribe others to email subscriptions in Power BI
• Microsoft + Github = Empowering Developers
• Announcing: The SQL Server Execution Plan Reference

I hope you found this edition of Bits of BI useful, feel free to share it with others!

@NickyvV

The Dynamics 365 Tech Conference 2017

I was very excited when I received an e-mail from my employer a few weeks ago which stated I am one of the 5 people that have the chance to go to the Dynamics 365 Tech Conference 2017! I immediately checked my agenda and I would be home just in time for my wife’s birthday, so that’s a go! The location will be the same as last year: Seattle. The conference will be held from March 12-15 and I will be going with 4 other colleagues from Pulse and 3 from AXtension.


There will be a business consultant, two functional consultants and a developer joining me, so we’ll make up a nice mixed group. We will have a short tuning session before we leave to make sure we aren’t all in the same rooms together to make the most of the sessions.
I am particularly interested in the Reporting and Analytics track about different reporting tools and Power BI, but also in the Entity Store, Continuous Delivery and the Common Data Model (or Common Data Service) in general.


In the next months I hope I find the time to blog some more about the upcoming conference, I will definitely record my findings during and after the conference!

The flights have been booked and the ESTA application process has been completed. I just hope there will not be any more restrictions like the recent ones the @POTUS declared. We will arrive on the Saturday prior to the event so we’ll have a little spare time left to discover Seattle. I heard the Boeing Museum and the Space Needle are good for a visit. If you have any other recommendations please let me know.
If you’re also going, do let me know in the comments or on Twitter (@NickyvV), I hope to see you there!

Open Live Writer On GitHub

A few days ago an open source fork of the Microsoft Windows Live Writer code was announced. It is now called: OpenLiveWriter. It makes it easy to write, preview, and post to your blog. The current release has version 0.5.

As I host my blog on Blogger I encountered some issues when I wanted to login to the application while installing it. According to the issue tracker at GitHub this was a known issue and is being dealt with in the next minor release 0.5.1. I hope they are able to fix this soon as I'm eager to try it out!

Have a look at the project on Github or see what they have in mind for next releases at the roadmap!

MSTechEd Europe 2012 Day 1: Optimizing your BI Semantic Model for performance and scale

I joined my first session on tuesday by Dave Wickert. Here's my short version of the session.

VertiPaq is all about Performance, Performance, Performance!
The VertiPaq engine chooses query performance over processor performance.
What the VertiPaq engine does:

1. Encoding (per column): creates a dictionary with value or hash encoding (self-deciding)
2. VertiPaq storage
• dictionary
• column segment
• hierarchy
• relationships
• partitions
3. Processing
• Process Full
• Process Data
• Process Recalc (includes calculated columns)
4. Advanced Processing
• Process Defrag: updates the dictionary
• Parallel processing
• Error handling
5. Server Memory Map
• Databases
• Formula engine (DAX, MDX)
• VertiPaq caches
• Storage engine

Defaults that can be adjusted (be careful):
VertipaqMemoryLimit - 60%
TotalMemoryLimit - 80%
VertipaqPagingMode

MSTechEd Europe Keynote Day 2

We started day 2 with another keynote session from Antoine LeBlond with a focus on Windows 8.Antoine showed some cool demos of what Windows 8 is capable of, including a demo on a tablet and laptop. Features included were Swipe in, semantic (smart) zoom, quick cycling through apps (the old alt+tab).
A new feature for the laptop is that the touchpad on it also has smart touch capabilities! You can also use semantic zoom and swipe in on the touchpad for example!

Another cool thing was taking your work environment on a 32 GB USB-drive and run it on your desktop at home, where a reboot and login with BitLocker showed a secured and protected environment, locked away from the physical hard drives of the desktop. Then suddenly he unplugged the USB-device, you then have 60 seconds to plug it back in to not loose any data. The video he was playing just continued after a flick of a second.. Nicely done!

Further demoing Windows 8 development with Windows Runtime (WinRT).
Multiple monitor support has much improved. You can set different backgrounds or just stretch it over 2 monitors. The taskbar can also be adjusted, e.g. when you drag and drop an application over to the 2nd monitor the shortcut on the taskbar also moves with it.

More sessions later on today on SQL Server and Spatial Data and of course the famous BI Power Hour, can't wait to have seen that one!

MSTechEd Europe 2012 Pre-Con: SQL Server Performance Tuning and Optimization - It all depends...

Yesterday I was at the Pre-conference Day at the MSTechEd Europe. I learned a lot during the session and tried to make some useful notes so I can use them for later reference. Specially the part on DMVs was very useful, I didn't use them before and certainly am checking them in the next weeks!

By Denny Cherry and Thomas LaRock

1. Identifying performance bottlenecks

Tools used:

- Performance Monitor

- Activity Monitor

- DB Tuning Advisor (DTA)

- Data collection

- Profiler

- SQLTrace

- Dynamic Management Views (DMVs)

- Extended Events (xEvents)

Blocking processes / wait events (running, runnable, waiting) aren't shown in Profiler (= SQLTrace + visualization). Those can be shown with the xEvents Wizard under Management in SSMS. Always tune it to your needs, never use a default (See PAL).

Summary:

- Define the problem

- Decide for tool with the best metrics and least (perf.) cost

- Consider the time needed to analyze

- It depends..

2. Index optimization

1. Indexing

- Started at design time

- Data types matter!

- Changes over time

- First line of defense for (quick) perf. Tuning

Do we have the right indexes?

- It depends..

- Follow best practices

- Use DMVs for missing indexes

- Focus on Logical IO (Inside SQL Server 2005: T-SQL Querying by Itzik Ben-Gan)

SQL Diagramming > table with highest selectivity of data

Index seek is better than Index scan.

If an index has only writes and no reads > delete the index

2. Execution Plans

Look for:

- Correlation with wait events

- Examine first operator (Select | Update | ..)

- Warnings (missing index)

- "More costly"  operators: LIKE > Spool uses tempdb

- "Fat fingers": thick arrows means lots of rows

- Looking at the #rows in

□ An index > update statistics

□ Hash match > input vs output rows (do nothing)

3. Filtered Indexes

An index with a filter (where clause), but no extended logic (e.g. OR)

4. ColumnStore Indexes

- Based on the xVelocity in-memory analytics engine (a.k.a. Vertipaq)

- CREATE COLUMNSTORE INDEX [COLUMNS]

- Downside: the table is read-only, workarounds:

□ Delete index > load data > create index

□ Use 2 tables, 1 read-only and 1 read/write table

3. Storage optimization

1. Table partitioning

- Reduce maintenance window

- Improve query response time

- Reduce recovery time

Create filegroups, a partition function and a scheme referencing the function.

Business (logic) knowledge is essential before starting partitioning!

When moving data: meta data (page headers) is updated

When deleting data: 

- Move partition to another table > then truncate (faster and save)

- Also getting data back that shouldn't be deleted is faster. Let the data stay in the other table for a few days before actually deleting it.

2. AlwaysOn (Enterprise feature)

- No need for shared storage

- Offload backups

- Scaling out read-only activity

4. Troubleshooting

- Are all queries running slow?

- Use xEvents with DMVs

- Is a subset of queries running slow?

- Use Executions plans with DMVs

Useful DMVs:

- Sys.dm_exec_query_stats

- Sys.dm_exec_query_plan

- Sys.dm_exec_cached_plans

STATISTICS IO ON

STATISTICS TIME ON

Using GO 10 after a SQL statement will execute the statement 10 times, useful when inserting data.

Resources mentioned can be found @ http://mrdenny.com/res/teched-2012-precon

Ready for MSTechEd Europe 2012

I'm getting ready for the TechEd in Amsterdam RAI next week. Registration is completed, the hotel is booked and I've build up my agenda with the most important sessions I certainly do not want to miss.

I've watched several videos on Channel 9 from the MSTechEd North America, those were all very promising for what's to come. Earlier I also wrote a summary of the Day 1 Keynote by Satya Nadella.

I hope I can update my blog next week with new and exciting things from the TechEd!

The Future of Middleware and the BizTalk Roadmap

Tony Meleg said some interesting things about BizTalk Server at the Microsoft Worldwide Partner Conference in LA. You can see his whole presentation / video here.

Another blogpost I found interesting was from Richard Seroter, which is called Is BizTalk Server Going Away At Some Point? Yes. Dead? Nope.
His most important conclusion is "Is BizTalk dead?  No.  But, the product named BizTalk Server is likely not going to be available for sale in 5-10 years.  Components that originated in BizTalk (like pipelines, BAM, etc) will be critical parts of the next generation integration stack from Microsoft."