I was worried that you could only create the PowerBi dashboard from the Azure Synapse portal but I am sooo happy that you can connect to your data warehouse directly from PowerBI desktop app.
An amazing feature would be to cache the on-demand SQL as well as the pool so no formal DW is necessary. Leverage views on top of raw data in blob or a data lake and do all translations with that ANSI SQL. I get that SQL Pool (Legacy SQL DW Gen 2) has this capability, but I don't want all of the heavy lifting. Let's tie into ADLS directly, do transformations with ANSI SQL and cache those results so we only hit that expensive workload once. Kimball methodology at the highest value. That's a major game changer....
There is some caching of the files there today. You'll notice your queries will get even faster if several different queries are accessing the same files. Everything I showed in the video was from a cold cache. Remember that the product is still new, and it will get even better from here as we incorporate customer feedback.
Cautiously optimistic, but prior versions of this have been too pricey for companies I've worked with either with slot/concurrency issues on the DW or egress charges. Very interested to see the relationship of the performance to the pricing and the differences between the Spark-y stuff in Synapse over Data Lake, vs. Azure Databricks on Delta as well as the details on how "serverless" works and is billed, including when Power BI embedded is added to the mix. The integrated interface is definitely slick, though.
I agree. In my option reading huge datasets from DLGen2 as data source is not a best practice. Technically is ok but I would never recommend. I could list many reasons to avoid that.
Shhhh🤫 they don't like when you tell the truth. There are truths to the power of synapse. But its all using the same sort of engines that databricks use. And dropping files with no etl, great if the data is in the right format, it never is.
Yeah, I'm not expecting "no ETL". However, sometimes it would be nice to simply ingest cleansed source data and park it somewhere read only in partitioned parquet files and figure out if/what we want to do with it later.
Why would you save your power bi reports on azure synapse and not publish them to power bi service? As we have users who would access the power bi dashboards we created via the Apps due to the licence they have
How to build a tabular model for power bi in synapse, or we have to use Azure analysis services integrated with synapse. and how power bi use Synapse with data modeling?
Hello Patrick, thanks for the nice video. Just wanted to request you for a topic. There is very limited stuff is available on Power BI Sentiment Analytics using Azure Cognitive Services. It would be best if you could create and share a video on that.
Hey Josh, does Azure Synapse supports Japanese. I really need this info. I know Azure cognitive services does. But I have to name Database or table or name in Japanese. Could I do those in Synapse or I had to handle this in Power BI alone. Please help.
It all depends on which pricing method you use. If you go with the serverless on-demand option that I showed, you are charged per TB scan when you run queries. When you are not running queries, you are not being charged. There are also provisioned options where you purchase capacity. You can check you the different options here: azure.microsoft.com/en-us/pricing/calculator/?service=synapse-analytics
Awesome, guys. I should try it. It consists of several services and practically all of them can be done at the Synapse Analytics portal (except Power BI dataset). Is the any service from Synapse Analytics which can be developed in Visual Studio?
Sad to see Josh leave the Power BI team, but AWESOME to see the work he's doing now on Synapse Analytics. This product has really come together seamlessly like nothing we've seen before in Azure.
Awesome ! Thank you so much to finally fully integrated Data warehouse and Machine Learning together in a nice unified Azure work space experience. True unlimited scale ! Great enterprise analytics solution ! Beautiful Data Orchestration ! Thanks for sharing ! ETLing data from all those different clouds keep me hallucinating with all those endless possibilities ! Love that Data Lake + Data warehouse integration ! AI + ML Python code generator ! Wow ! Thank you for bringing Predicting Model (Machine Learning) combined with SQL ! Awesome orchestration ! Thanks for creating Azure Synapse Analytics and sharing this explanation. Handy to have an end-to-end, single work space environment. Very, very handy. Thanks for making ETL, BI, AI and ML much more simple. Bringing the power SQL to directly query serverless the DataLake is very interesting ! Any SQL Developer can bring a rich set of analytics by just writing SQL statements !
When we create external table or view pointing to the data file with delta format in the data lake, it will return all the versions of each record, which means synapse does not support delta format files.
Good question! From my experience getting data from SQL server DB to Power BI is very slow, and most SQL DB Admins freaked out when someone is connecting directly to SQL DB, rather it is better to use SSAS, Cube, or Azure DB as a bridge to Power BI as they offer Four's V: Value, Velocity, Variety, and .. I forgot the last one... Please correct me if I am wrong
Hi Guys, Great explanations. Are you able to please further explain why no data transformation. I am assuming the transactional data starts in the CRM App and then the raw data is loaded to datalake Gen2. In almost all Power BI Report development instances, I have needed to format the column and/or clean the data etc. In my case using query editor in dataflows to clean data. Why is there no need to do any transformations in the sample data in the video. Thanks in advance.
Demo was going great, until for some unknown reason, you have to go back to PBI desktop!!! Why would you want to go back to the desktop application??? Importing 1.6 billion records from the cloud to setup the PBI connections on the desktop only to push it back to the cloud... makes absolutely no sense
We never actually pulled 1.6 billion rows in the desktop. We queried them from the desktop. I happen to use direct query in this example. Had I used import, I would have aggregated and trim the rows in Power Query just like other data sources.
@@JCap383 having to go back to the desktop in order to build the dataset instead of having connect directly on the cloud, seems like a missed opportunity for a very significant feature change. Pulling and pushing datasets to/from the cloud is one of the biggest obstacles I find when using PBI.
How much are those jobs costing to run over that all those 1.6B rows of data? If no ETL/ELT is required, so no curation/standardisation being performed over that RAW data, is that being done each and every time and having to be replicated by every PBI job/model that runs over that data?
Correct me if I wrong, every time when user is working with PB report and changing some filters, SQL query will work on Azure Synapse resources? How about performance in case 100 users will work with report? I'm interested in performace.
Can anyone tell me why in Powerbi they entire data is downloaded? How is it safe from a security perspective and also how long should a user wait to download so many billions or rows on his own Machine
Is synapse available today? Can I query data from a PowerBI dataset in synapse, using dax? Can I connect to an azure sql sever, write an view that gets incrementally refreshed, and store it in synapse? Thanks for this video, I've been looking forward to synapse for data cleansing for PowerBI since I saw it last summer!
I didn't know Ashton Kutcher was doing work on Synapse!!
lol :)
😂
Ashton Kutcher with Will Smith 😝
Nice tools guys! I love to see this maturity across the products.
Agreed. Synapse is a great evolution of Azure SQL Datawarehouse.
Bananas scenario is bananas. ;-)
Thanks for the video. It's super timely!
hahaha thought you would appreciate that 👊
Love the role play! Non techie People really want and need to be able to hear the “message” in their own language.
I like that you had a guest on that was outside the scope of PBI, but was definitely in scope with getting data to PBI; which is a regular battle.
I was worried that you could only create the PowerBi dashboard from the Azure Synapse portal but I am sooo happy that you can connect to your data warehouse directly from PowerBI desktop app.
An amazing feature would be to cache the on-demand SQL as well as the pool so no formal DW is necessary. Leverage views on top of raw data in blob or a data lake and do all translations with that ANSI SQL. I get that SQL Pool (Legacy SQL DW Gen 2) has this capability, but I don't want all of the heavy lifting. Let's tie into ADLS directly, do transformations with ANSI SQL and cache those results so we only hit that expensive workload once. Kimball methodology at the highest value. That's a major game changer....
There is some caching of the files there today. You'll notice your queries will get even faster if several different queries are accessing the same files. Everything I showed in the video was from a cold cache. Remember that the product is still new, and it will get even better from here as we incorporate customer feedback.
Cautiously optimistic, but prior versions of this have been too pricey for companies I've worked with either with slot/concurrency issues on the DW or egress charges. Very interested to see the relationship of the performance to the pricing and the differences between the Spark-y stuff in Synapse over Data Lake, vs. Azure Databricks on Delta as well as the details on how "serverless" works and is billed, including when Power BI embedded is added to the mix. The integrated interface is definitely slick, though.
Yeah, the price becomes a major roadblock for smaller clients with huge data sets.
I agree. In my option reading huge datasets from DLGen2 as data source is not a best practice. Technically is ok but I would never recommend. I could list many reasons to avoid that.
Shhhh🤫 they don't like when you tell the truth. There are truths to the power of synapse. But its all using the same sort of engines that databricks use. And dropping files with no etl, great if the data is in the right format, it never is.
Yeah, I'm not expecting "no ETL". However, sometimes it would be nice to simply ingest cleansed source data and park it somewhere read only in partitioned parquet files and figure out if/what we want to do with it later.
Omg we r so look ok over budget on our project.
Very interesting topic. could you make a video on azur analysis service and power BI!
Great stuff!
Nice video. Please help me understand if this demo was about Azure Synapse Analytics OR Azyure Synapse Workspace? Thank you
What if i would like to connect with power bi report builder. How to connect synapse with power bi report builder
Will it support Query folding?
Why would you save your power bi reports on azure synapse and not publish them to power bi service? As we have users who would access the power bi dashboards we created via the Apps due to the licence they have
How to build a tabular model for power bi in synapse, or we have to use Azure analysis services integrated with synapse. and how power bi use Synapse with data modeling?
I can't find anyone to answer that
I am psyched.
Hello Patrick, thanks for the nice video.
Just wanted to request you for a topic.
There is very limited stuff is available on Power BI Sentiment Analytics using Azure Cognitive Services. It would be best if you could create and share a video on that.
Newbie at SQL...what is the difference between Synapse and previously used SQL storages?
Synapse is an evolution of Azure SQL DW (Old on premise PDW).. It's based on MPP
Hey Josh, does Azure Synapse supports Japanese. I really need this info. I know Azure cognitive services does. But I have to name Database or table or name in Japanese. Could I do those in Synapse or I had to handle this in Power BI alone. Please help.
A little estimate of the Azure cost of this deployment?
It all depends on which pricing method you use. If you go with the serverless on-demand option that I showed, you are charged per TB scan when you run queries. When you are not running queries, you are not being charged. There are also provisioned options where you purchase capacity. You can check you the different options here: azure.microsoft.com/en-us/pricing/calculator/?service=synapse-analytics
Good video
Lol so the parquet file is clean.
Awesome, guys. I should try it. It consists of several services and practically all of them can be done at the Synapse Analytics portal (except Power BI dataset). Is the any service from Synapse Analytics which can be developed in Visual Studio?
We do support SSDT for your databases.
I need to give Synapse more attention!
Sad to see Josh leave the Power BI team, but AWESOME to see the work he's doing now on Synapse Analytics. This product has really come together seamlessly like nothing we've seen before in Azure.
Congratulations! More videos like this Patrick. Please.
Awesome ! Thank you so much to finally fully integrated Data warehouse and Machine Learning together in a nice unified Azure work space experience. True unlimited scale ! Great enterprise analytics solution ! Beautiful Data Orchestration ! Thanks for sharing ! ETLing data from all those different clouds keep me hallucinating with all those endless possibilities ! Love that Data Lake + Data warehouse integration ! AI + ML Python code generator ! Wow ! Thank you for bringing Predicting Model (Machine Learning) combined with SQL ! Awesome orchestration ! Thanks for creating Azure Synapse Analytics and sharing this explanation. Handy to have an end-to-end, single work space environment. Very, very handy. Thanks for making ETL, BI, AI and ML much more simple. Bringing the power SQL to directly query serverless the DataLake is very interesting ! Any SQL Developer can bring a rich set of analytics by just writing SQL statements !
Just jumping here to say something about that YOOOO!!! from @Josh ! ;) Great video! Awesome intro to the whole new modern dw world! Keep going!
Hi @Guy, What are main reasons for using synapse database/view like this VS connecting directly to DataLake from Power BI?
2:17 Man, I lost it at "Little baby data"
When we create external table or view pointing to the data file with delta format in the data lake, it will return all the versions of each record, which means synapse does not support delta format files.
Resultset Cache layer in Synapse will also be a layer before materialized view !! isnt it ?
Please, please, please, more videos on Synapse Serverless & Power BI
Any recommendations on when to use Synapse over SQL Database (thinking about data size/data formats/frequency of loads/etc.)?
Good question! From my experience getting data from SQL server DB to Power BI is very slow, and most SQL DB Admins freaked out when someone is connecting directly to SQL DB, rather it is better to use SSAS, Cube, or Azure DB as a bridge to Power BI as they offer Four's V: Value, Velocity, Variety, and .. I forgot the last one... Please correct me if I am wrong
...volume
Can we connect Azure Synapse SQL Pool with Power automate? I am getting a bad gateway error when i try to connect.
Does it cost if I add power bi in my workspace??
Hi Guys, Great explanations. Are you able to please further explain why no data transformation. I am assuming the transactional data starts in the CRM App and then the raw data is loaded to datalake Gen2. In almost all Power BI Report development instances, I have needed to format the column and/or clean the data etc. In my case using query editor in dataflows to clean data. Why is there no need to do any transformations in the sample data in the video. Thanks in advance.
Exactly!!!! Lol just open rowset.
Thanks for the video ❤
awesome stuff @patrick & @josh! one nitpick-->geez, josh, get a better microphone!
Greg Kramer that is a good idea.
Demo was going great, until for some unknown reason, you have to go back to PBI desktop!!!
Why would you want to go back to the desktop application???
Importing 1.6 billion records from the cloud to setup the PBI connections on the desktop only to push it back to the cloud...
makes absolutely no sense
We never actually pulled 1.6 billion rows in the desktop. We queried them from the desktop. I happen to use direct query in this example. Had I used import, I would have aggregated and trim the rows in Power Query just like other data sources.
@@JCap383
having to go back to the desktop in order to build the dataset instead of having connect directly on the cloud, seems like a missed opportunity for a very significant feature change.
Pulling and pushing datasets to/from the cloud is one of the biggest obstacles I find when using PBI.
Does Azure Synapse Analytics effectively replace OLAP multidimensional cubes as they are basically faster cloud OLAP cloud services?
How much are those jobs costing to run over that all those 1.6B rows of data?
If no ETL/ELT is required, so no curation/standardisation being performed over that RAW data, is that being done each and every time and having to be replicated by every PBI job/model that runs over that data?
Correct me if I wrong, every time when user is working with PB report and changing some filters, SQL query will work on Azure Synapse resources? How about performance in case 100 users will work with report? I'm interested in performace.
Looks like Synapse Analytics is an all-encompassing analytics tool. You don't need anything else for data engineering or analytics!!
Can anyone tell me why in Powerbi they entire data is downloaded? How is it safe from a security perspective and also how long should a user wait to download so many billions or rows on his own Machine
Thanks, nice movie, nice products, and more 🤩😍😘
I hope more movies
Great video! Seems he has not faced Camera much,
We can use dax requests on Synapse ?
Can you please give us the location of NYC Taxi data to play with Synapse Workspace along with the queries that you showed in this demo?
It's not publicly available to our knowledge. It's a large scale dataset that has cost associated with it.
Hi Patrick. From your scenario how is the aggregation table created. Thanks.
It's just an imported table. Can do it with Power Query, or have it pre-created on the data source side.
Can someone please tell me in what format is data stored in Azure data lake? ie: csv, json
You are free to use the format you like.
Parquet is probably the best format because it is compressed and quicker to query
Is synapse available today?
Can I query data from a PowerBI dataset in synapse, using dax?
Can I connect to an azure sql sever, write an view that gets incrementally refreshed, and store it in synapse?
Thanks for this video, I've been looking forward to synapse for data cleansing for PowerBI since I saw it last summer!