Boosting Performance in Your SQL Server Data Warehouse
Microsoft Power BI
0:01 [Music] [Music] Heat.
0:18 Heat.
0:19 [Music] Hey everyone, hope you are all doing great and yes very good morning,
0:53 good Afternoon and good evening to all who all are joining us globally.
0:57 I am Paru events and program manager for Microsoft reactor India and yes I
1:03 do welcome you all for our today's session with our own beloved MVP Rajendra.
1:10 But yes before we start our today's event
1:13 uh let's go through our code of conduct.
1:19 We all are here to learn together, participate together.
1:24 So please be respectful of other people views, understanding the differences,
1:28 be kind and considerate in a way we all engage.
1:33 We do encourage you all to participate.
1:35 Please drop all your questions in the comment
1:38 section and we will pick it up from there.
1:43 I would not like to delay further now.
1:46 I have Rajendra already at the backstage and would like to welcome him.
1:52 Hey.
1:52 Hi Rajendra.
1:54 How are you?
1:55 Hi Pad.
1:56 I'm good.
1:56 How are you?
1:58 I'm all well.
1:58 Doing great folks.
2:00 Uh Rajendra is a Microsoft MVP from Microsoft
2:07 data platform specializing in PowerBI and MS fabric.
2:12 and uh currently he's serving as a PowerBI lead
2:15 specialist within the engineering and tech lead team at Bosch.
2:20 Uh he brings expense like extensive expertise in the field like he over
2:27 he like holds over eight Microsoft certifications
2:32 including uh fabric analytics engineer, PowerBI analyst.
2:38 He's also a Microsoft certified trainer.
2:41 He's a very passionate contributor to the community
2:45 and yes I do welcome him today.
2:47 Uh thank you Rajendra for hosting this event and yes the floor is all yours.
2:56 I think you are on mute.
3:05 [Music] Can you hear me now?
3:13 Yep.
3:14 Okay.
3:15 Sorry further.
3:16 Uh good morning, uh good afternoon,
3:18 good evening wherever you are located from all over the world.
3:21 And first of all, I would like to thank
3:22 Microsoft Reactor for giving this opportunity from Microsoft Reactor
3:26 team and Rashida they're continuously following and we are
3:31 conducting these events to educate to present our topics.
3:35 Thank you Pat.
3:36 Thanks for giving this opportunity and before
3:41 getting delay I would just like to give uh uh topic name boosting performance
3:46 in your SQL server data warehouse using Microsoft fabric.
3:49 So in Microsoft fabric what kind of an activities we are going to take
3:52 it forward and performance- wise what are the things that we need to take
3:56 in care these things we are going to discuss already uh path already
4:00 given an intro about myself I just like to briefly I will provide myself
4:04 and I'm Rajendraul I'm a data platform MVP and a super user at Microsoft
4:09 fabric community platforms where we will
4:11 share the knowledge and get the knowledge
4:13 from the community spot and uh if you want to get in touch
4:16 with me on the right hand side you can find me my social networking sites,
4:20 LinkedIn, Twitter, data analytic group blogs.
4:23 So you can reach me out if you have any queries
4:26 uh on any of the topics like on the Microsoft fabric
4:29 or PowerBI related stuff before like without delay I just try
4:35 to showcase the content what we are going to discuss about it.
4:38 So I will go in a slow manner.
4:39 Maybe if you have any queries, concerns, please post it in the chat box.
4:42 My sincere request so that we will take it those queries at the end
4:46 or maybe if it is agent priority part we will take it in middle also.
4:51 So understanding the fabric warehouses and how it will be
4:54 emerged and where it is and how we are going
4:57 to make use of this fabric environment with warehouse uh personas
5:01 medally and understanding about ETL
5:04 extract transform and the loading activities.
5:07 So what are the activities we will try to take
5:09 in care on util part and a special option
5:13 is there whenever if you can working on the fabric
5:15 warehouses we can also clone the existing tables so
5:19 a kind of a replica of the data tables we can do it maybe if you are creating
5:23 a dedicated schemas for the project related and also I
5:26 will showcase you the loading strategies what are the ways
5:29 so there are multiple approaches to inject the data
5:33 so into the data warehouses so I will showcase
5:35 you in the Microsoft fabric uh uh three
5:38 of the approaches how we are going to inject the data
5:40 into the data warehouses and also once our model is
5:44 ready and everything is available what kind of a semantic
5:47 models we're going to make use of it we will
5:49 discuss about it a crispy about catching mechanism in fabric
5:53 uh data warehouse concept we are going to discuss
5:56 types of uh uh in-memory catches and and so
5:59 on we are going to discuss about it and troubleshooting
6:01 options and benefits of maybe if you can start making
6:04 use of Microsoft fabric what are those benefits we can
6:07 get it uh if you can moving on to the Microsoft
6:09 fabric environment along with that maybe if I get
6:12 time permits I can showcase to you the whatever
6:15 the model we are going to build it
6:16 on the powerb layer as well from Microsoft fabric uh warehouse
6:20 to the Microsoft PowerB application in the cloud application
6:24 itself fine if you're observing I'll just try to showcase
6:27 to you as set fundamentals we'll get started
6:29 and then we will walk you through this uh demo
6:32 as well uh for the data injection part so first
6:35 of all let's try to understand the data warehouse fundamentals.
6:38 So first of all we need to keep in mind the things
6:40 like data injection what kind of a data we are trying
6:43 to move and what is available from the source side and what
6:46 kind of a processes we can try to make use of while doing
6:49 the data injections like how many approaches are there in the while
6:53 doing the data injections part that we have to keep in mind
6:56 and the data storage part how the data will be get optimized
6:59 and it will be get stored in data warehouses in Microsoft fabric.
7:02 So as I said it will be get stored in the form
7:04 of delta parket format file like a delta parket format file.
7:09 So delta is nothing but a storage layer where on the top of it parket files
7:14 the data will be get compressed and it will
7:16 get stored in the form of a calmer data.
7:19 So example what is this uh storage mechanism in the sense?
7:22 So I have a table with uh maybe 50 records and in that with a name and uh
7:28 some metric information and I want to read that particular
7:32 data information into my injection part data loading part.
7:35 Here the data will be get created
7:38 the required information like a clo columinal storage format.
7:42 So that means example if I have a four or five characters
7:46 of a word it will be all get treated in the form of a numeric
7:50 like a numbers how many it is there a structure format so
7:53 it is that that kind of an optimation technics it will be get
7:57 visible to you on the delta parket format form it's a kind
8:00 of a storage layer as I said delta is nothing but a storage layer
8:04 on the top of it these files will be get stored on your uh
8:08 source systems path and then as I said the trans data processing part.
8:13 The data will be get processed and finally it will be get ready for the further
8:18 consumptions like AML models or maybe if you can try to prepare that particular
8:23 processed data to the reporting part we
8:25 can get it delivered via different visual insights
8:28 by using the powerb desktop uh powerb
8:30 applications which is already there in Microsoft fabric.
8:34 So just a brief about data
8:36 warehouse fundamentals data injection and storage processing
8:40 and data and then get delivered to the front- end applications like PowerBI.
8:46 Let's try to understand Microsoft fabric warehouses.
8:50 So if you're observing on the uh right hand
8:52 side a simple snapshot which I have provided to you.
8:57 You can see here the data is completely get stored on the form
9:01 of an one lake is in base platform and on the top of it we can
9:06 make use of a data warehouse lakehouses
9:09 even realtime analytics if you're doing KQL
9:11 databases are there and even the powerb
9:14 application or realtime applications will be get provided.
9:17 So one lake is a storage mechanism where the complete layer or data
9:23 sources will be get incorporated
9:25 in an unified management and governance platform.
9:28 So you can create multiple workspaces in a dedicated
9:32 environment once the fabric license has been acquired
9:36 and from there either you can try to pick
9:38 it from the data from a warehouse or lakehouses.
9:41 Maybe sometimes we'll get a queries like what
9:43 is the difference between a warehouse and lakehouse.
9:47 So in a warehouse you have a fully transaction SQL you
9:52 can write it you can create the statements you can uh do
9:56 the DML statements you can also transaction level statements also we can
9:59 perform it all the trees TSQL statements will be get performed if you
10:03 can make use of the warehouse it's as simple as similar
10:06 as like uh whoever familiar with SS SMS like management studio so you
10:11 can see a similar view like a schemas and the permissions uh
10:14 login login ids and so like uh roles procedures, views and so on.
10:18 The similar view and coming to the lakehouse,
10:21 it's something like maybe if you're working with a big data source system,
10:26 big source systems like uh maybe huge terabytes of data
10:30 which is unstructured or semiructures kind of a things.
10:34 It will be have an option like a static like select statement only.
10:39 It's not going to have all the other mechanisms like uh modifications and so on.
10:44 we can just try to read only options will be there
10:46 on the top of it SQL endpoints part we can do the further optimizations
10:50 and techniques which will be get allowed on the lakeouses part so it's
10:54 a completely different warehouse and the lake houses but both of this will
10:58 be get starts on the one lake platform like that as per
11:03 the project to the project we can get it created different workspaces to like
11:07 workspace A workspace B and so on too like as I mentioned telemetry
11:10 data or real-time data get stuff will be get stored in the lakehouse
11:14 houses because it's going to be an unstructured real-time data should be
11:17 something like uh most of the times we'll get it in the form
11:20 of unstructured s systems and the business KPIs part we can try
11:24 to make use of the further applications like uh as I said powerb
11:27 desktop and uh we will try to build the data model
11:30 with the help of the data click we can fetch the data very fast
11:33 to the powerb applications too here I just prepared a simple uh concept
11:41 on the explanation the differences part how the data will be get stored.
11:45 As I said data warehouse in Microsoft
11:47 fabric is a completely storage centralized storage area.
11:51 We can try to work on like historical data.
11:54 Most of the organizations they can try to work on the SED
11:57 type tools like historical data they
12:00 want and also the current transactional data.
12:02 In such kind of a scenario we can try to make use of this centralized
12:06 mechanism approach by using which has been provided
12:08 by Microsoft fabric data warehouse concept as well.
12:12 Think of it if you are observing here maybe if
12:15 you want to get fill the complete data very huge
12:17 information and I want to get it stored not
12:20 in a lakeouses I want to get it stored the processed information
12:23 in a lakehouse where I can try to build the reporting
12:27 part as well as even I can try to take
12:29 it forward to the advanced AML models too and coming
12:34 to the concepts for how it will be get stored.
12:36 So tables it will be get stored in the form of a rows and columns
12:40 as I said and the data warehouse level it will be get completely transactional.
12:45 So we can write the statements we can uh even delete the trans
12:48 I mean that particular uh statements or data or even we can try
12:53 to write the stored processes where we can try to get it execute
12:56 for our modeling part and lakehouse as just
13:00 now we discussed lakehouse and warehouses part.
13:02 Lakehouse it is going to store unstructured or raw data completely raw
13:06 data which will be in a file source systems example if you can
13:09 consider our warehouse is completely kind of a cleansed information which is
13:15 ready to further visualize it and uh
13:17 take further decisions by using applications
13:20 like PowerBI and direct integration with powerbi as I said we can
13:25 once the data the structured information is available the data will be directly
13:29 passed uh via direct query like we will call it as a direct
13:33 query in power by desktop but in the cloud application in Microsoft
13:37 fabric we will call it as a direct lake uh the concept
13:40 part so it will be faster retrieval the data part there is not
13:44 going to store it some source system or somewhere it will be
13:47 all the information will be get stored in an one lake hub itself
13:50 and from there we can try to pull the information to the application
13:53 and we can see the realtime insights as well and as I
13:57 said elastic and scalability maybe if our as organiz Organization needs the data
14:02 is not a static it will be grow by day by day.
14:05 So it will be auto I mean the enhancements part it will be get managed
14:09 automatically in the back end part and have
14:11 an autoscale position on options as well
14:14 once we can get start with our data warehouse creations part as I said ETL
14:23 extract transform and loot whenever if you are
14:27 trying to extract the data check the connections.
14:30 So how we are going to get it extract whether you are directly
14:34 pulling into the data warehouse or maybe if you are trying to use
14:38 an individual extractions like maybe directly I can connect it to the Azure
14:42 data pipeline or maybe the Azure data flow gen 2 as well.
14:46 So we'll try to extract the data
14:48 first by connecting to the different source systems.
14:51 So Microsoft it is providing more than 100 plus source systems.
14:55 We can get connected as per the project needs and inside we will
14:59 try to perform the data cleanup or cleansing operations like uh within the so
15:03 whether you can try to inject the data via data pipelines with the help
15:06 of pipelines uh as almost similar experience as like a data factory.
15:10 We can perform the data cleansing operations like
15:13 duplicates removal or maybe if you want to generate
15:16 some new columns we can try to perform
15:18 that or maybe uh as per the business as per
15:20 the working needs if you are trying to make
15:23 use of data flow gen two it has similar
15:26 experience as like online power query editor we can
15:29 try to bring the extract the data and we
15:31 can perform inside but it's not going to be
15:33 on power desktop we'll try to perform those activities
15:36 of the cloud-based application itself data flow gen
15:40 and try to load those tables whatever the fact tables
15:43 which is going to have some important metrics
15:45 and dimension tables which is going to have some categorical
15:48 and important uh description related informations too and after
15:53 that we can start working on the performance optimization.
15:56 So our topic main agenda here boosting of the performance.
16:00 So where we are going to work on this performance related
16:03 activities once we can get start with our data warehouse building.
16:08 So I will showcase to you where we need to work on it and I
16:11 also prepared a few slides related to it which will be helpful to you.
16:17 Coming to the security if you're observing
16:20 it's completely haved once the data warehouse
16:23 my data is get available it's completely
16:25 have an advanced transcription role based actions too.
16:29 So we can try to define maybe if you
16:31 are working with a different multiple workspaces and if
16:34 you are trying to implement a role based based
16:36 on the report level in such kind of a scenario
16:39 we can use rowle security which is already
16:41 there in powerbi and even the column level security
16:44 or object level security will be get defined on powerb
16:47 applications part and coming to the granular transaction SQL.
16:51 So it's completely where we can try to write as I mentioned
16:54 right we can create the statements we can modify it we can
16:59 uh even the delete the required information is not required this completely
17:03 granular information will be get provided and coming to the data masking part
17:08 so as a realtime environments sometimes the data will be get not
17:13 showcased like example take an example like a credit card maybe I
17:17 want to share few of the information on the top and then
17:20 uh cross mark and last four is I want to get it visualized.
17:24 In such kind of a scenario we can try to mask the data by writing
17:27 the statements inside of your data warehouse like as I mentioned masked is going
17:32 to be an mask this particular email with some limited like as I showcased
17:37 to you after login the user you can see that information with the cross buttons.
17:41 Maybe I want to grant a complete access of the masked informations
17:46 to some user means we can grant it via unmasked commands to.
17:50 So as I mentioned there grant unmask a particular employee so that they can
17:55 try to view the complete information once the user will be get logged in.
17:59 So you can see the right hand side how it looks like
18:02 the once we can try to perform these activities on uh data warehouse level.
18:08 So which will be very important on the security concerns part.
18:14 Coming to the data loading strategies.
18:17 So make sure that you are trying to move
18:19 the raw data as per the business requirement.
18:23 Whether you are trying to get it extract the on premises
18:26 source system or maybe if you are trying to extract
18:30 external source systems we just try to make sure that we
18:32 have to get it load this information into the data warehouse.
18:36 So if you're observing just now we
18:37 discussed about ETL part extract transformation and load.
18:41 So whenever if you can get start with the data warehouse extraction there are
18:46 four different ways to extract the data inject the data into the data warehouse.
18:50 So in that we are going to discuss three approaches for today's uh event.
18:54 So like I will try to showcase to you creating the transaction
18:58 SQL statements like we will try to create some tables and also
19:02 we will insert the data that is one way in manual
19:05 instruction but in real time we will not do those things like
19:08 extracting I mean inserting the tables and creating directly into the warehouse
19:12 but I'm just trying to showcase to you one of the approach
19:14 which is already there in the Microsoft fabric and the another way
19:18 is we will also inject the data via data flow gen 2.
19:22 So as I mentioned which is has similar
19:24 experience as like online power query ed where we
19:27 will try to instruct in extract the data
19:30 inject it into this particular tables and we will
19:33 perform internal some transformation and load it
19:35 into the further analysis like in the data warehouse whenever
19:39 if you can start creating and loading the tables
19:42 automatically it will be triggered with the staging layer.
19:45 So if you want that option to store our data
19:48 in a staging which is a temporary layer we
19:51 can store it or we can directly also uh
19:54 load the tables into the data warehouses and another approach.
19:58 So first one is as I said manual creation
20:01 of the tables inserting the data second approach is
20:04 as I said data flow gen two and third approach
20:08 as we are going to discuss for today data pipeline.
20:11 So I'm going to extract uh data pipeline is similar
20:14 as I said it's a similar experience as like data factory.
20:18 So we are going to extract one of the sales table uh from Azure uh blob
20:24 storage or maybe I'll try just try
20:25 to showcase to you the processing part and then we will try to create a complete
20:31 after data extraction we'll create the staging layer
20:34 as well as that particular table will be
20:36 get visible to you into the data warehouse.
20:38 So how it will be and so on we will discuss.
20:40 So three approaches were discussed and what about the last approach
20:43 how we are going to inject the uh one more way.
20:46 So there is one more option is there cross data warehouse injection.
20:51 So what is this cross data warehouse injection in the sense?
20:54 So maybe I have already completed a project and the table is already available
20:58 in the same workspace and I want to reuse for the another warehouse builder.
21:04 So in such kind of a scenario I can try to reuse the same table as a kind
21:10 of a subset of the table information to another projects
21:13 too that is nothing but a cross data warehouse injection.
21:17 So uh the table is already available.
21:20 How we are going to pick it up?
21:21 Maybe if time permits I can showcase
21:23 to you the approach from the lakehouse or uh
21:26 existing warehouses part and we can try to build a lake uh warehouse as well
21:30 data warehouse part and further once the data
21:33 get visible to you and further analysis like
21:36 we can start building the reports or maybe if you want to showcase in the form
21:41 of a dashboards or advanced AML part we
21:44 can get it visualized as per the architecture
21:47 which you are currently observing but once
21:50 the structure Sure the data will be get visible
21:53 to you on the data warehouse and the proper schemas then we can start uh doing
21:58 the further advanced analysis as per the requirement
22:02 I mean on the top of the final build.
22:06 So maybe whoever familiar with uh powerb part once if you're
22:10 extracting the multiple tables and you want to prepare a data model
22:15 in such kind of a scenario whatever the tables that are available
22:18 on the downstream mechanism it should be if you want to get it
22:22 created a semantic model like a table with another table connection if
22:25 you can see my downstream snot uh with the dimension customer fact
22:31 sales order and so on it will be get created automatically sometimes
22:35 If you want to build our own semantic model with the required tables,
22:40 not all the tables, maybe few tables.
22:42 In such kind of a scenario,
22:44 there is one more option named called semantic model build.
22:47 So other than the default semantic model.
22:50 So once I will open the application, I can showcase to you.
22:53 So advantage is maybe if you can try to make use of a default semantic model,
22:57 all the tables will be get visible to you
23:00 for further uh downstream of the reporting layer.
23:03 And in the modeling part we can try to give
23:05 the connections from one table to the another table accordingly.
23:09 Maybe if we can try to choose a particular tables in such kind
23:14 of a scenario we can choose a semantic model uh designing and from there
23:19 we can also define the semantic model name and we can create the required
23:24 table information as an connection between one
23:26 to one table to the another table.
23:29 So we have an we can also create the business requirements once if you
23:33 can moving out of the data modeling part and create the new measures and so
23:37 on and the modeling view in the powerba services application once we get to open
23:42 this u data modeling activities is there
23:45 is no limitation we can create it coming
23:50 to the catcher mechanism as a part of uh performances part there are two types
23:55 of a catches if you can get start working on this fabric data warehouses One
23:59 is in-memory catch utilization which will be
24:02 get stored and it will be get access
24:03 as a faster and it will be get stored in the columner format as a set.
24:08 Another approach is a disc catchy storage approaches which is going to consume
24:12 some space and if you're working with the larger models or larger
24:16 semantic uh data sets in such kind of a scenario the these catches
24:20 designs will be get triggered automatically whenever if you can working
24:24 on the fabric uh I mean building the data warehouse application
24:27 in the Microsoft fabric that's why most of the time what we will do
24:32 in the sense maybe we can if you want to do a group
24:34 or maybe if you want to join the join with one table
24:37 to the in the table we can try to perform inside
24:39 of your uh visual level query or SQL statement option is also is there
24:44 as I said we can also use these statements internally so as I
24:51 said options for the data injection so first of three approaches we
24:55 are going to discuss now like I'm going to create some
24:58 of the tables and via data pipeline which is as similar experience as like
25:03 Azure data factory I'm going to showcase to you uh with one
25:06 of the extraction ction and one more table via dataf flow gen two.
25:10 Uh we will try to extract or fetch one of the table from the dataf flow gen 2.
25:14 How this staging layer will be get created and so
25:16 on and all will be get visible to you.
25:19 And the last part as I said cross warehouse injection cross
25:22 uh which is nothing but already the data set is existed
25:25 in the one of the workspace and I want to reuse
25:28 as a subset of the existing table in such kind of a scenario instead
25:32 of it's a traditional way of uh uh doing the data extraction
25:36 instead of duplicating the table and trying to pull so we can
25:40 make use of the existing workspace itself and make uh use
25:43 of the new uh report I mean new warehouse building and so on.
25:48 These are the approaches are uh how we are
25:51 going to inject the data into the data warehouse
25:57 f maybe last I will discuss about the benefits
25:59 of uh fabric warehouse before we'll try to start
26:02 working on as I said extractions and also I'm
26:06 going to showcase to you some new features which
26:08 are available in uh Microsoft fabric uh warehouse environment
26:13 part and then we'll discuss about the benefits as well.
26:17 Let me uh jump onto the screen.
26:21 So if you can get start working on this Microsoft fabric,
26:24 we need to have a trial account.
26:26 If you're observing, I'm currently using the fabric trial account.
26:29 So anybody can register.
26:30 We can try to enroll it with your on Microsoft accounts.
26:33 We can just try with this fabric trial for the 60 days.
26:37 So here first we need to have a trial.
26:40 After that we need to have a dedicated workspace as well.
26:44 So let me create currently if you're observing I'm in the fabric environment.
26:48 I will try to create a dedicated workspace for our current activity.
26:52 Let me create a new workspace demo.
27:06 So as I said I'll just try to showcase to you here.
27:08 I'm using trial account.
27:10 Apply it.
27:15 Once the workspace created you can see here at the top
27:18 DWHMS demo it will be look in this way.
27:22 So fine how we are going to get start with the data warehouse.
27:26 So where what is the process?
27:27 Now here at the top you can see the new
27:30 new items if you're observing here new items just try
27:33 to select it you can see the different personals which are
27:36 supported under Microsoft fabric environment like a dashboards you can get
27:39 start with it or maybe if you are not building
27:41 any data warehouse or something maybe if you want to get
27:44 start directly with the some extractions like Azure data pipeline
27:48 or Azure data flow genus we can make use of it like
27:52 by using get data options too but current our activities
27:56 we are going to build a data warehouse inside the data
28:00 warehouse as I said we will try to extract the few
28:03 of the tables via copy jobs I mean transaction SQL
28:07 wise we will write some statements and also the second
28:10 approach I'm going to showcase to you data pipeline wise I'm
28:13 going to extract it and the third approach is I'm going
28:17 to showcase to you data flow gen two wise I'm going
28:19 to showcase the data extraction and we'll see the performance
28:22 activities too on the top of it let's try to showcase
28:26 now see Here you can search from here too if you
28:29 want to find out that particular item or maybe you can
28:32 just try to observe here what are the application what
28:34 are the different tools and functionalities
28:36 that are supported anywhere Microsoft
28:38 fabric environment you can see this many options are there let
28:42 me search it maybe I'm I want to build a warehouse
28:45 uh you can try for the sample warehouses too but uh
28:48 as a part of learning I'll just try to showcase
28:50 to you the current how I'm going to create an warehouse
28:53 you have to provide a proper naming convention to So
28:56 let me provide like sales warehouse demo naming convention just
29:10 make sure that so as I said once the warehouse created
29:15 you can see the canvas and the pop-ups the complete screen
29:20 as a similar experience as like uhs SMSQL server management studio.
29:25 Go.
29:26 So on the left hand side explorer part you
29:28 can see the queries shared queries and your schemas procedures
29:33 and everything on the left hand side vertical pan like
29:36 explorer part and currently in the center it's trying to create
29:40 so it will take some time as I'm currently using
29:42 the trial license suppose maybe if you have a dedicated
29:45 license like f2 f32 and f64 the faster is something
29:49 different and maybe sometimes network speed or bandwidth also depends.
29:55 Maybe if you have any queries please post it.
30:03 See here I just created the sales warehouse demo is the name.
30:09 You can see on the left hand side is different schemas
30:12 are there like maybe by default schema is the DBO schema.
30:15 Maybe if you want to get creative with a dedicated schema
30:18 for every project like maybe some organizations will maintain staging layer
30:22 enterprise data warehouse layer pre uh load layer like a pre
30:26 PR PRDS data layer and reporting definition layer and so on.
30:31 So we can try to create these schemas at this particular schema belt.
30:36 By default you are observing DBO schema
30:39 and maybe if you want to save this particular
30:41 executed queries or created statements we can try
30:44 to save it your queries will be get visible
30:46 to you on the my queries option and if you want to share your queries with some
30:50 other colleagues or something mean we can just try
30:52 to make use of the share queries options too.
30:55 So as I said how we are going to inject the data.
30:58 So here on the get data option you can see two options like
31:02 new data flow gen two or uh new data pipeline which is as I
31:06 said uh new data flow gen 2 is as similar experience as like online
31:10 power query editor data pipeline is
31:12 as similar experience as like azure data factory.
31:15 So as I said I will start creating some
31:18 tables but in real time we will not create it.
31:20 Maybe we have to get it extract or imported
31:22 from some dedicated database systems or file source systems.
31:26 But here I'm just trying to showcase the functionality how we are going
31:29 to create the tables and how we are going to insert the data as well.
31:34 So I'm using create table SQL uh option here.
31:37 This is the view SQL query one view.
31:40 Here we can start writing the schema definition or maybe if you
31:43 want to write uh create the new tables we can write it.
31:46 So as for the time permits I have already written first the schema.
31:50 Let me execute the schema first uh staging schema.
31:56 You can directly execute it here and in the left
31:59 hand side the schema will be get created.
32:01 The staging schema you can see here
32:03 with a complete tables views functions and store process.
32:07 Maybe if you want to execute some store processor for uh
32:10 I mean like a uh CD type 2 execution like layer
32:15 will be captures something like a temporary data and it will
32:19 be get deleted once it will move on to the data warehouse.
32:22 So we can also create some processes accordingly.
32:25 So let me create one more uh table few more tables.
32:33 So very simple statements.
32:34 So here if you're observing uh customer product city
32:38 and version these are the four tables which I'm
32:40 going to load it here load it staging is
32:46 my schema name you can see only the structure of this table so once you see here
32:51 the fastest fastering pass three senses has been executed within
32:55 3 seconds and these are the tables which has
32:58 with just a structure there is no information I
33:01 just loaded with a structure it will take some
33:04 time see here ID city name and region customer
33:09 ID and customer name product and so on so
33:13 I'll just try to save this query for maybe
33:15 the future prospect right click here rename it created
33:26 tables simple tables and also I will try to load
33:30 a random data as well uh into my warehouse
33:34 house into the same staging layer with some data.
33:42 Let me take uh another query here.
33:44 From here you can take it new query SQL.
33:48 New query SQL.
33:49 Let me remove it.
33:56 Here I will try to insert some data.
33:58 If you're observing some partial data randomly I'm just trying to insert.
34:02 So you can see here the customer ID with some names,
34:05 product table with some records, product details and a city details and a stage
34:10 uh version details too with actual budget and version.
34:14 So let's try to load this information as well
34:17 and try to give a proper naming convention so
34:20 that it's easy for us to reference or maybe we
34:23 can try to take it for the future references too.
34:26 Insert or load load it data tsql the saved queries will be visible
34:42 to you at the down part here okay fine let's try to crossverify
34:52 whether the table information is available or not you can see here
34:57 product has been loaded with version with some records It has been executed.
35:01 You can see the the faster and maybe I had tried with one one
35:05 GB of data which is very very faster in my one of my realtime project.
35:09 I just want to share that information also with you.
35:12 See here these are the four tables which you are currently observing
35:14 in our warehouse that fine then how we are going to extract
35:18 the data for as I said sales table and uh another if you
35:22 are trying to working on the warehouse building date table is a mandatory.
35:26 So why datable is a mandatory mean?
35:28 Maybe if you want to crossverify the historical data along with maybe I
35:32 want to cross check the analysis for the previous years and the current year.
35:36 In such kind of a scenario we have to make sure that in data
35:40 models we have to prepare that date table should be visible to you or available.
35:44 So here I'm going to extract two tables via other
35:48 options like I'm going to use new data flow gen
35:51 2 for a sales table extraction and a new data
35:55 pipeline for another table time dimension table time time table.
36:00 So let's try to showcase to you first let's
36:02 let me showcase to you the new data pipeline wise
36:04 you have to give the naming conventions to this pipeline
36:08 uh maybe uh let's try to try with sales
36:11 itself sales different okay I'm just uh giving
36:15 the pipeline name is sales inject pipeline via pipeline I'm
36:23 trying the sales table and maybe the time table
36:26 is very simple let me execute it via data flow
36:35 is going to open an another window if you're
36:38 observing it's not the same warehouse window and the left
36:42 hand side if you're observing left hand side
36:45 a home tab this is the icon for the warehouse
36:48 and currently I have executed I have connected
36:51 to the for data injection data pipeline so you can see
36:54 the left hand side vertically sales inject pipeline so
36:58 Here what are the steps we are trying to perform?
37:00 All the steps will be visible to you on the left hand side vertically.
37:04 Choose the data source system.
37:06 Connect to the source.
37:08 Choose the destination where you want to store the data and also connect
37:13 to the data destination via maybe I want to create it with the staging layer.
37:18 I can do that activity here and review and save options.
37:22 So where we can try to review the complete
37:24 summary of where to where the source to the destination
37:28 how it is getting moved the complete information will be
37:31 visible to you in the form of an icon wise
37:33 in the last stage fine as I said let's try
37:36 to execute the data via say maybe SQL server I'm going
37:40 to connect get connect to it let me provide uh
37:44 server details so whenever if you're trying to connect on premises.
37:53 This is an on-remises source system.
37:54 Let me showcase to you the table as well.
37:57 If you're observing the table,
37:58 the sales table is visible to me in one of the database adventureworks DW209.
38:05 So here my sales table is available
38:07 which is in on premises source system DVO.SQL.
38:10 So I want to pull this onremises SQL database to the cloud environment.
38:15 So what are the things that we need to keep in mind?
38:18 So whenever if you're extracting on premises to the cloud-based application just
38:22 try to crossverify few things like gateway should be configure that is one
38:27 standard gateway or maybe if you're trying for the testing part you
38:30 can just try to with personal gateway too and another one is you
38:34 have to get it configure the gateway with your name the admin rule
38:38 maybe there is some other admin means if you don't have a permissions
38:42 to this gateway you can see some errors notifications too just try
38:46 to crossverify the gateway is having a proper access to you as well.
38:50 Okay, with your user ids maybe already some administrators are
38:54 there and make sure that the gateway is up and running.
38:56 So if you're observing my gateway is up and running.
38:59 It is ready to use.
39:01 So let's try to extract it.
39:03 I'm just trying to provide the server name server details.
39:08 Let me use the connection type.
39:11 See here on premises on premises source system click on next.
39:18 Gateway is mandatory whenever if you're trying to connect on premises
39:21 data source system whether it is an SQL server maybe
39:24 if it is a posgress whatever just try to crossverify
39:27 the gateway is up and running uh so that we can
39:29 try to pull this information into your cloud-based applications
39:37 and as I said permissions part where it is going to be
39:40 visible to you so maybe let me open a new
39:43 screen where we need to get it added this permissions part.
39:46 Let me showcase to you that uh gateway configurations part here.
40:00 Just try to cross verify you have
40:01 an proper access on premises data management gateway.
40:04 You can see here it is up and running.
40:06 Okay.
40:10 Online fine.
40:11 Let's try to find out our database adventure DW209.
40:16 Click on next.
40:18 So try to choose the database uh in that particular table which
40:22 table you want to extract the particular information into your data warehouse.
40:26 Choose it.
40:27 So as I said it has already created with the DBO schema or the sales table.
40:32 Uh let me showcase to you that DBO.
40:37 You can see the data preview on the right hand side.
40:40 small table only.
40:42 Select next.
40:44 So here on the next part connecting to the destination it will ask you do
40:48 you want to store load it onto the existing table or it is a new table.
40:52 Basically we are doing the new project.
40:54 It is not any existing table.
40:56 It's a new table.
40:57 Load it and try to change the schema
40:59 because you have defined with a proper schema name.
41:02 You can see in our schema part it's a staging schema right?
41:06 try to give the proper naming uh schema name sales
41:09 dot uh staging dot sales table and here the columner mapping
41:14 maybe if some columns like a binaries and that particular columns
41:19 maybe subtables are available you can delete that columns to here
41:22 and even we can try to change that data types too
41:26 so from the source to the destination sometimes if the data
41:29 types are different we can change the data types here
41:32 as per our requirement so let's try to select Click next.
41:37 You are observing the currently the column mapping.
41:41 Next.
41:41 So here as I said staging schema is by default it is enabled.
41:46 You can see this and I want to get
41:49 it store this particular table information also in my one
41:54 of the blob storage blob container in such kind
41:57 of a scenario I can try to get it configured.
42:00 It's not directly like you cannot select directly like
42:02 a next you have to provide the staging account connections too.
42:06 It is a mandatory.
42:08 You can see here if you can start creating a next see
42:11 here direct copy of the warehouse to the copy command is not supported.
42:15 Just try to choose uh where you want to get it stored.
42:19 So I have already configured it.
42:20 You can see here my blob account uh ms fabric storage.
42:24 I want to get it stored in my one of the blob container here.
42:29 Let me showcase to you that one of the container it will taking time.
42:37 So see here let me browse it from here the path in the meantime.
42:41 So see this this is the folders in this blob.
42:43 Maybe I want to get it stored in the database backup file
42:46 that is the path I'm trying to provide to store my information.
42:51 It will be get stored in the form of this folder wise.
42:55 Okay.
42:56 And here one more option is there.
42:58 You no need to use an any compressed option.
43:01 If you can turn it, try to turn it on.
43:03 It's already compressed information and it
43:05 is already structured data source system.
43:07 Don't use this enable compress option.
43:09 Otherwise, you will face an error with the uh while executing the data pipeline.
43:14 I'm just trying to selecting the next.
43:16 You can see the complete view.
43:18 So from where to where you're trying to transferring
43:20 the data from source to the destination here.
43:26 Sorry.
43:29 here.
43:30 So from SQL server this is the table name DBO.
43:34 This is the staging layer where it will get stored.
43:37 And last one is the destination I want to store
43:39 in my one of the warehouse staging dots sales.
43:43 Let's try to execute it.
43:52 So on the performance prospect once our uh uh model is
43:58 ready I'll just try to showcase to you few important snapshots
44:01 I mean data warehouse snapshot new concept which is available
44:05 in the warehouse okay and the performance prospect what are the con
44:10 commands that we need to crossverify okay you can see it
44:15 is still executing it is running there hence for the notifications
44:18 part let me up So currently this particular copy command
44:26 from this uh source is your SQL server to the destination.
44:30 Destination is the staging.
44:31 Sales and you can see the complete mapping details here
44:34 on this preview data preview also it will be get visible to you.
44:37 Maybe if you want to get creative with some
44:39 parameters and u some logging the data storing logging you
44:44 can also configure from this logging I mean enable logging
44:47 options and uh staging logging options too still executed let
44:52 me see this table is visible to me or not
44:55 let's try to observe your here workspace I'm just trying
45:00 to refresh here yeah you can see the table
45:06 that is sales table has been get loaded with this records.
45:11 Okay, fine.
45:13 Last one more table as I said date time date dimension table.
45:16 So I'm going to fetch it via data flow gen two.
45:20 So it just executed.
45:21 You can see at the top right hand side top notifications too.
45:24 Just keep an eye on this top notifications whether it has been succeeded or not.
45:28 So otherwise we have to navigate to your workspace and just try to crossverify
45:32 whether the I mean that particular person
45:34 has been successfully completed the task or not.
45:37 Here here also we can try to monitor here.
45:41 Okay.
45:41 If there is any errors it will be get visible to you instantly.
45:46 Fine.
45:46 One more extraction which I'm going to show to you via dataf flow gen two.
45:51 So please observe here get data data flow gen two.
45:55 This time I will try to showcase the data extraction via blob.
45:59 Okay.
45:59 While uh doing the data extraction for the uh date and time table via
46:05 data flow genu I'm going to extract it the data extraction via blob storage.
46:11 Let me showcase to you that process.
46:13 Let me give the name as extract date and time.
46:19 Okay.
46:20 Create it.
46:22 So as I said it is as similar experience as like powerbi power query editor.
46:28 So in power desktop power query editor what are the views what are
46:32 the icons you are trying to observe the similar options will be get
46:36 visible to your data flow gen two you can see here it's executing
46:43 so in the center the quick connections you are observing importing the data
46:46 from excel on premises SQL server or tsq or import from existing data
46:51 flows so as I said I'm going to extract the data via blob
46:55 storage let me showcase to you that approach as What are the things
46:59 that we need to keep in mind while extracting the data via blob storage?
47:05 So few things are very important.
47:07 We need to have an proper account uh blob account and we have to have
47:12 a necessary permissions to get connect
47:13 to this storage accounts and also here you
47:17 can see gateway is not a mandatory it is not required and connections part
47:23 we need to showcase what type of an I mean authentication method we are trying
47:27 to make use of it most of the time we will use as per
47:31 the projects SAS account shared access signature
47:35 which will be get valid like Example I'm
47:37 saying 6 months 6 months duration after the renewal we have to uh change
47:42 the password and we have to give the another SAS code SAS tokens and so on.
47:46 So most of the time we will use it
47:48 other than that organization accounts are there even the service
47:51 principle account and account I mean main person user
47:56 ids account as well we can get it connect here.
48:02 So date and time stamp uh blob uh blob details there in my locally one
48:19 minute so here I'm just trying to showcase to where we are going to get
48:23 connected edit the connections to you so as I said data management gateway is
48:27 not required SAS token is mandatory SAS
48:30 token is required to get connect to here.
48:32 So just try to paste it one more time.
48:43 Simple if you I mean by default it will not showcase to any connection.
48:47 You have to create it as a new connection and then pass those credentials.
48:51 And also uh while giving the SAS token uh I mean generating that link
48:56 you have to configure the container details
48:58 objects and so on otherwise you will not
49:01 able to get import or extract that particular table or else a container it will
49:08 showcase showcase the error as like a forbidden
49:11 uh that particular path and so on.
49:14 Okay.
49:15 So the file is available in my data files.
49:20 Here you can see here time table is the form of a CSV file format and parallelly
49:28 I'll just try to showcase to you uh clone how we are going to clone the table.
49:34 Okay.
49:35 So whenever if you are doing the data extraction via data flow
49:38 there is no option to give a I mean like as I said
49:42 we already created a staging uh schema correct so if you're extracting
49:46 the data via data flow we don't have any dedicated option like changing
49:50 the schema name for a data flow gen two so we have
49:54 to clone it by default it will be go on to the DBO
49:57 schema and then we have to clone it to the current schema
50:00 which we are currently using it as an staging schema so here I'll
50:04 just try to rename instead of calling it as a data files
50:08 time table and this is what I'm looking for time dot CSV extract
50:18 it see here I'm not able to get it configured with staging
50:22 currently our schema uh schema design is staging layer right so here it
50:28 will be get by default this particular table will be visible to you
50:31 on the TV schema now so automatically These steps will be get
50:35 added and one more important thing whenever if you're extracting the data
50:38 via data flow gen 2 just try to cross verify your data destination.
50:42 So just try to check it here like
50:44 what is your current workspace or the warehouse
50:47 information and how the data will be get
50:49 added is that an append method or replace method and one more thing maybe if you
50:53 want to get it deleted that particular by default
50:57 it is created right here also we have
50:59 an option to get provide the destination information.
51:03 So you can see here by default destination.
51:06 So maybe if you want to change the data destination
51:09 to some other workspaces or some other warehouses, we can remove it.
51:13 We can change the workspace name as well.
51:16 So what I will do in the sense as for the time concern I'm
51:18 just try to publish this uh to the current uh uh warehouse sales warehouse.
51:24 Publish it.
51:26 Let me showcase to you that uh by default this particular time table
51:31 is visible to you on the DB schema because it's one of the maybe
51:35 going forward we will expect uh changing the schema definition in the data flows
51:40 too but as of now it is not visible or else available to you.
51:45 It will get visibility on the DBO schema part tables list.
51:49 Let me check here it's executing and also
51:53 just try to crossverify the notifications part two.
51:56 Okay, the top two ways whatever you feel comfortable just try to check
52:01 it whether it has been successfully executed or is there any failure.
52:04 So see here the notification data flow has been executed.
52:08 Let me navigate back to the warehouse.
52:12 try to refresh.
52:21 So cloning the table is something like it is trying to take
52:25 the reference already table exist in the schema of DBO and if you want
52:30 to take it as a reference to some other schemas or some other
52:34 references of the tables in such kind of a scenario we can use cloning.
52:38 It's kind of replica not a any info I
52:42 mean like not like something just a replica not
52:45 a duplicate you can see here this particular table
52:47 information time table so how we are going to clone
52:51 this time table to the current staging layer it's
52:54 as simple as easy you can see here horizontal ellipse
52:57 button is there after the time select it I want
53:00 to clone this table currently in the DBO I want
53:03 to provide the destination table to the I mean
53:05 another schema that is staging layer change it give
53:09 the proper name maybe I'll just try to give
53:12 the date and time and here table stages current table
53:20 and SQL statement also we can observe it this is
53:22 a table creation staging uh clone table creation clone
53:29 it as simple as easy and the table is visible
53:32 to you in this current schema here date and Okay.
53:39 And one more important features I've just tried to showcase to you
53:42 which has been recently added last few or maybe 15 days back.
53:46 I haven't observed this in this particular management part.
53:49 They have added a new warehouse snapshot.
53:52 Snapshot as I said uh a kind of a do
53:56 replica we can consider already the tables are existed.
54:00 Maybe I want to use this current table or else a warehouse
54:04 snapshot for some months maybe some one particular month or maybe some days
54:08 in such kind of a scenario to execute it to write the statements
54:12 or to further modify the statements or to create some procedures and so on.
54:17 We can also use new warehouse snapshot.
54:21 This is a new functionality.
54:22 What happen if we can try to select this new warehouse snapshot.
54:25 You can observe here it captures the current
54:27 warehouse which I just created with some tables
54:29 with the staging and it will get create any of the stage for the past 30
54:34 days and we can start as I mentioned like we can write the statements we can
54:38 also build further fabric items on the data
54:41 pipelines or further environments accordingly within the Microsoft fabric.
54:45 So this is a by default.
54:46 Let me showcase to you one more sales snapshot one.
54:56 Okay.
54:56 Current created.
54:58 So still there are some errors which I have I
55:00 mean I have observed it once it will be get created.
55:03 Uh initially we can see at the top some errors but we can
55:07 able to execute the statements and so on by using this SQL query option.
55:11 Go to the warehouse snapshot.
55:14 This is just a replica right as I said you can see some
55:17 error notification can can't capture this date
55:20 and capture warehouse for the there
55:22 are some problem but it is already captured the table information you
55:25 can see let me capture the new state current capture it's already captured
55:31 you can see the table informations too okay so what is the benefit
55:35 of using this so as I said we can start writing and executing
55:38 the statements on this snapshot of this warehouse schema and we can
55:42 build further analysis accordingly by using
55:45 this particular snapshot of this existing schema.
55:48 Maybe if you want to build for the new projects or you want to try
55:51 with some other AML models just a kind of a replica we can proceed accordingly.
55:58 So let me uh close this one.
56:05 Fine.
56:06 As I said whenever once our tables
56:09 information everything is ready we can make use
56:12 of a default schema approach or a dedicated new
56:16 semantic schema design also we can proceed it accordingly.
56:19 So I'll just try to showcase to you here giving
56:21 the name as uh sales we can select the required tables.
56:31 So that is the advantage of new semantic model.
56:34 We can create the sch semantic model name and also we can select
56:37 the required tables that are required
56:39 to visualize to the further developments part.
56:42 So the staging part these are the tables.
56:45 We can select few tables.
56:48 Let me select customer product and sales and date and time.
56:57 Let's try to go with this.
56:59 Confirm it.
57:03 And coming to the performance part,
57:06 there are three things maybe as per the administration rule.
57:09 They can have an access to uh dynamic movement views like we can
57:15 also execute the complete what are the sessions that are executed and we
57:19 can view on this uh who have whoever want to see the complete
57:23 sessions that are executing on this who is having an access of administrator.
57:27 Other than that we have the DM views
57:33 with session wise and DM views wise request wise.
57:37 So number of sessions that are executing
57:39 on each and every request level active request
57:42 level we can observe it that's going to have an access for only other roles
57:46 like we already aware on PowerBI four
57:48 different roles other than the admin contributor member
57:51 and viewer like a request and sessionized views
57:54 uh dynamic management views we can view it
57:57 via these objects like a requesttor and session
57:59 objects what are the latest or active sessions that are executing on the top
58:03 of the engine these sessions will be get execut
58:06 visible And another one is as I said uh the administrator they can try to view
58:11 the complete number of sessions that are
58:13 executing in warehouse part it's a similar experience.
58:17 So as I said the models we have to build it on our wound.
58:20 So we have to give the connections and build the relationships accordingly.
58:26 Let me quickly provide some connections customer to customer.
58:37 So write the new tables new measures all
58:40 the DAX objects will be supported except new columns.
58:43 So by default when you are trying to give
58:47 a connection relationship you can see this one.
58:51 Let's just try to all the cardalities are supported all four types.
58:55 So let's try to save it.
59:01 Okay.
59:01 And one more connection and then I will showcase to you
59:04 one different uh I mean uh performance prosper management views.
59:18 So like this we can give the connection and then
59:20 we can start the data visualization on your reporting layer.
59:24 So from here we can get connect to the report.
59:26 So one more table is there.
59:27 Let's try to give the connection.
59:28 Why we miss this one second and date to date.
59:43 Any questions till now?
59:46 Just a few minutes and then I will take if you have any questions.
59:53 to this directly to the PowerB this data
59:56 to this PowerB application is directly connecting to the warehouse
1:00:03 and then passing the data into your uh PowerB
1:00:06 application that's what you can see at the top
1:00:08 the direct transfer directly connection the storage method
1:00:12 is so from here we can start creating
1:00:15 the new report and we can build the visualizations
1:00:19 two and the last but not the least I'll just try to showcase to you the commands
1:00:28 pop but uh what are those commands [Music] hey
1:00:37 hi Rajendra yes so we have few questions so let's take it at the end so to check
1:00:46 the performances part So if you want to execute up third one is uh one more is
1:01:00 there uh session request last connection is um last
1:01:10 a few one minute and then uh scheduleuler schedule.
1:01:19 So as we already know on the PowerBI part
1:01:23 we have an admin layer contributor member and viewer.
1:01:30 So admin is going to have a complete access what are the number of sessions it
1:01:36 will be get reward back number of sessions
1:01:38 that are executing on the data warehouse engine.
1:01:41 they can view it.
1:01:42 If they want to kill any of the ex
1:01:45 executed session means they can perform that activity.
1:01:47 Hey, Can you hear me?
1:03:31 Pat, yes, I'm audible now.
1:03:36 Sorry, there was some technical glitch, I guess.
1:03:42 Yes.
1:03:42 Yes, I can hear you.
1:03:43 Can you hear me?
1:03:45 Yep.
1:03:45 Yep, I can hear you.
1:03:46 Okay.
1:03:47 Yeah.
1:03:47 Yeah.
1:03:47 Yeah.
1:03:48 So just now it was disconnected emirate.
1:03:50 Okay.
1:03:50 Yep.
1:03:51 Yep.
1:03:51 Yep.
1:03:51 Okay.
1:03:51 Fine.
1:03:52 Okay.
1:03:53 So here I'm discussing about the best practices part.
1:03:56 Once we completed this this particular uh
1:04:00 development if you can observe this this is
1:04:02 the current model and even I just showcase
1:04:04 to the powerb application how it looks like.
1:04:06 Once from here we can try to navigate to the reporting layer.
1:04:10 The performance aspect.
1:04:12 A few things that we have to keep in mind.
1:04:14 who are the accessor part.
1:04:16 So the complete control will be on the administrator just like how we
1:04:20 will define the workspace control like
1:04:22 administrator contributor member and the viewer roles.
1:04:25 So administrator can try to schedule uh kill the ex I mean continuously running
1:04:31 sessions or maybe they can try to check the how the engine has been executed
1:04:34 the performance prospect and everything and the remaining
1:04:37 like as I said session and the requesttor
1:04:40 we can try to check it like maybe other than the admin role other
1:04:43 priority roles like a contributor member role and viewer role can try to check
1:04:47 the number of sessions that are executed
1:04:49 in each and every active number of workspaces
1:04:52 like that that it will be requested is something like it will be uh respond
1:04:57 back uh how many number of activities
1:04:59 that are executed in each and every workspaces.
1:05:03 This is nothing but DMV dynamic uh I mean moment views.
1:05:07 So these are the three different ways we
1:05:08 can try to segregate with four different roles.
1:05:11 Admin is going to maybe if the uh session is taking
1:05:14 more time he have an access to kill those roles too.
1:05:18 So I'll just try to share this uh performance pro prospect what are
1:05:22 the things that we have to keep in mind and one more thing uh maybe
1:05:25 if you are trying to make use of uh cross uh data warehouse injection
1:05:30 it just has like a cats create as a table on the existing warehouses part.
1:05:36 So we can use the create table as selected object.
1:05:40 So we can try to get it extract the subtables from the existing
1:05:43 warehouse or existing workspaces too and uh maybe if your model size
1:05:48 is very big and transaction data is very big we can try
1:05:51 to subdivide it and we can try to load it onto the warehouses too.
1:05:55 That is my sincere request which I have mean like we will face
1:05:58 it on huge data bulk loading part and so on as for the workloads
1:06:02 which will be get trigger role I mean like huge information and uh
1:06:06 we'll try to definitely face some errors too such kind of a scenario
1:06:09 my suggestion is we can just try to split or divided the data
1:06:13 sizes I mean like split the information and try to move it
1:06:16 or inject the data into the warehouses too fine I'm done here and if
1:06:22 you have any questions uh please raise your questions and let me know.
1:06:26 Hope it is clear.
1:06:28 Yeah.
1:06:29 Other than the glitch part, you there we have uh very good questions here.
1:06:38 Okay.
1:06:39 Okay.
1:06:39 Uh okay.
1:06:41 So the first question is is the storage different from uh
1:06:47 ADLS generation 2 in Azure here uh the where or the lakehouse.
1:06:54 So here ADLS is for the data extractions part where it is also
1:06:58 get resides in the one one lake hub only one lake storage layer.
1:07:02 So we are all familiar with the one drive right is
1:07:05 as similar as like a one drive is going to be on base
1:07:08 layer for you on the top of it the data will
1:07:10 be get resides on go spaces or as I said right lakeouses.
1:07:14 So what is this lakehouses means?
1:07:15 Maybe if you're extracting the data
1:07:17 from unstructured or semistructured information
1:07:20 means the data will be get resides via extraction of lakehouses.
1:07:23 If you're extracting the data like a structured
1:07:26 information warehouse wise also we can get stored.
1:07:28 The advantage of here we can write the statements create it delete all
1:07:32 the statements will be transaction fully pack
1:07:35 of transactions SQL statements will be get executed.
1:07:38 Yeah hope I have answered to you.
1:07:40 Yeah.
1:07:42 So, Vinnac has a question asking like currently
1:07:46 I am creating a snapshot of the database so
1:07:50 that the data warehouse can perform ETL from the snapshot
1:07:55 so that it does not impact the app.
1:07:58 Does Azure support snapshot creation?
1:08:02 uh currently I haven't tried database level snapshot but I have
1:08:05 just showcased to you how the data warehouse snapshot but still there
1:08:09 are some glitches which I already showcased to you have to select
1:08:12 the current state of the data I mean that particular uh warehouse
1:08:17 or maybe the particular databases means we have to select the current
1:08:20 state of the warehouse and then we can make use of it
1:08:23 in our day-to-day activities but the snapshot part two ways one is
1:08:29 from the database level snapshot Another
1:08:31 one is the data warehouse level snapshot.
1:08:33 Which one we are going to pick it up is the I mean the the right pull path.
1:08:38 Okay.
1:08:39 So and question from Supernabu asking like why
1:08:44 we need to store data in staging layer.
1:08:47 Do we have any advantages of it?
1:08:50 Advantages as I said right Supernabu this is something like temporary layer.
1:08:54 Maybe it's a kind of a simple backup.
1:08:57 Suppose if you're not choosing that option staging layer maybe I want to see
1:09:02 maybe the there is some issues the morning some execution I want to see what
1:09:06 is the issues if you are not saving that staging layer data it will be
1:09:10 get directly loaded into the data warehouse so it is going to be a complete
1:09:13 wrong information will be visible to you on the warehouse level so that is
1:09:17 the temporary part so that we are trying to configure the staging also to some
1:09:22 backup folder or maybe if you're having an access to some database we can
1:09:26 try to get it configure to that backup uh database backup files and so on.
1:09:30 But you can also delete it instead of uh
1:09:32 reloading I mean loading that same information on day-to-day basis.
1:09:36 We can also provide some kind of a processes
1:09:38 to delete the staging data time to time.
1:09:43 Hope I have answered to you.
1:09:44 Yes, I think with that again Alberto has some some similar
1:09:48 question like why would we want that tradition additional layer uh
1:09:53 if we already storage data in our warehouse as a staging
1:09:59 uh table why would we want also to store it in files
1:10:03 in a blob storage blob storage I just showcased to you
1:10:06 where we are going to take it as a backup so
1:10:08 after some days that backup file as just like I said
1:10:11 right maybe if there is anything wrong mismatch of the data,
1:10:15 wrongly entered the data to store that information only
1:10:19 we were trying to make it as a backup staging.
1:10:21 So that is a kind of a temporary folder
1:10:23 or else a layer which will be get incorporated.
1:10:26 So you cannot load directly into the structured information which is occupied
1:10:30 in our data warehouse that is the main moto to enable that staging layer.
1:10:36 Hope I have answered again.
1:10:37 Yeah.
1:10:39 So I think yes.
1:10:40 So that's all we have and uh Emanuel has
1:10:44 a question like great job where can we find
1:10:47 the copy of this best practice document I will
1:10:50 be able to answer this so this uh session
1:10:53 is recorded Emanuel and it's all over the YouTube
1:10:56 you can anytime visit the Microsoft reactor YouTube channel
1:11:01 and watch the session and with that uh let's
1:11:05 call for a wrap Rajendra so yes thank Thank you.
1:11:10 Thank you everyone for joining us today and uh thank
1:11:13 you so much Rajendra for uh thank you this session.
1:11:17 It was really exciting and uh hope uh to see
1:11:22 you all soon with our another part of the session.
1:11:25 Thank you.
1:11:25 Thank you all so much for joining us today.
1:11:27 Once again, thank you.
1:11:29 Thank you.