Priti Patil

My name is Priti Patel. I have a total of 8.2 years of work experience, but when you speak about Power BI, I have a total of 6 years of work experience. I have worked in many domains, like for example, insurance, immigration, patching, and life cycle management. I have also worked in the banking domain. I have created multiple dashboards. For example, in healthcare, I have created a dashboard that includes the number of patients admitted and the treatment they are receiving. I have also included the cost of each treatment, the cost of medicines, and the average number of patients admitted per week, along with the related data. Additionally, I have included the amount pending for specific diseases and the charges doctors are making for business purposes in a hospital. All these dashboards have been created, not only by creating the dashboard itself, but also by gathering requirements, creating wireframes, building the dashboard, determining the number of pages and visuals, the calculations required, and delivering it to the end users. Apart from that, I have also created row-level security, RLS, both static and dynamic. For static RLS, I have used a DAX formula based on username, and for dynamic RLS, I have used the username principle, also known as user principal name, and DAX. I have used star schema in one of my projects and snowflake, but mostly snowflake. However, when it comes to performance, I would insist on using star schema instead of snowflake if possible. Additionally, in my organization, whenever new features and updates are released on a monthly basis, there is a report available. If you want to share the report with end users, you can share it through Teams or emails, or you can create a copy link and share it. There is also an additional feature that has been added, which is storytelling and Power BI PPT. Directly, you can share the report in PPT. Earlier, if you wanted to share a report in PPT, you would need to take a screenshot of the dashboard, put it in the PPT, but not like this. However, with the new feature, you can directly add the report to PPT, where you can share multiple visuals and perform slicing and dicing of the data. Whatever the dashboard we have built on Power BI services and published into, if you want to publish it to the cloud, we do

To enjoy the security being viewed for multiple users, 1st, first, there are 2 types of securities. One is static low-level security, and the other is dynamic low-level security. For example, if you want to share a report with an end user statically, they can view the report without making any changes. They do not want to change anything, so we can give the viewer access. And within an organization, there's a 5-member team working as developers. If anyone wants to change their specific dashboard, then we can give the elevated member or contributor level access. Apart from that, within the organization, if you restrict the data, we can implement row-level security (RLS). RLS has two types: static low-level security and dynamic low-level security. The static type involves using a DAX formula as a username to implement static role-level security. For dynamic usernames, we can use the user principal name, or we can use a DAX formula with an if-else condition. I have implemented this when I was at KBR, a client. I worked on data analytics and ratings. So, in that case, I and there were 3 different departments: analytics, ratings, and both analytics and ratings. How many people can see the data for specific departments, such as analytics, ratings, or both? In that case, I didn't use the user principal name. Instead, I used an if formula to implement role-level security.

How would you explain the benefit of using a star schema in a data warehouse to a junior developer? Okay. When you speak about a star schema, there is one fact table and multiple dimension tables. So let's take an example. There is a dataset called sales data. So, sales information, everything is there in the fact table. The dimension tables are product information, order information, and customer details. With the help of this, if any junior developer is there, I would have to explain that in the fact table, what are the number of fields are there. And in the fact table, there should not be any duplicates. Any record should be there. In a dimension table, it can be a duplicate, but sometimes we consider the fact table as the parent table and the dimension as the child table. So, whatever the data is there in the fact table, sometimes we can interlink with the dimension table. But if you would like to build a relation, it is one to many. We can use a many-to-one relationship. And if you are using bidirectional filtering in order to avoid that, we can use a cross-filtering DAX formula. Otherwise, we can use a bridge table. So we can create an indexing method and, in between two tables instead of creating a bidirectional relationship to avoid that bidirectional cross filtering, we can create a bridge table and that will improve the performance. And when you speak about a data warehouse, I have worked on a project, Informative, where the client had multiple data sources. For example, financial data, and then they had Salesforce, and then there was AWS. The data was stored in MCD, CD, and CBC. There were so many databases. So, all this data was stored in the landing zone, which we created in Azure. We used the landing zone, and all these sources were integrated into it, and that is a Gen two account we created. And also, we used a streamlined process data breaks, and we created a pipeline. Then after, I built the dashboard in Power BI by directly integrating the source as Azure, and for Azure, there are multiple sources. So, in this, we used the star schema only. With the help of the star schema, we implemented the data warehouse.

Practical approach to implement testing application security. There are first one that is production. When you deploy a pipeline in Power BI, there are three different environments. The first is development, then testing and production. So we can deploy in this case, if we have a source in directly three environments, we can integrate to this specific source. But if we do not have one source, one environment, then we deploy in the development environment. Whatever the data is done in the development stage, that would need to be published into the testing environment where the testing people can test whatever the required information is there. But here, whatever I have worked upon, I have involved in a few projects as a tester. The requirements will come through Jira clients, such as KBR clients. They give the requirements on tickets, Jira tickets. For example, if you want to modify a paginated report, if any measure is not working properly, numbers are showing incorrect, then I need to check that the alignment is not there properly. Any slices need to be modified, any visuals need to be modified. In that scenario, they give the requirements. The developer will develop a dashboard. And after that, I do the testing part. I need to check properly that the work has been done or not. If it is done, then I can mark it as done. If it is in progress or if it is not related, everything testing data, testing of the report is done on a weekly basis. There were five to ten tickets used to come. So, out of the fetch, how many tickets are done or not done in progress, that all reports need to be created and shared to the VP and director who can communicate directly to the client. This is what my role and I do. But in different organizations, it depends on the source environment. If it is dev, test, or prod, then in that scenario, we can deploy a pipeline in Power BI.

What is your strategy for version control and deployment of powered by reports into the team environment? Question control is crucial in this sense. Let's take an example from KBR. Every two months, they decommission the dashboard. For instance, the first name was pipeline taxonomy dashboard version 1. Then, after two months, version 2, version 3. We can name the PBX file on the desktop and publish it to the cloud. In the Teams environment, if anyone is decommissioning a dashboard, we need to inform the organization. This dashboard will be decommissioned, and it will be available for use for a specified duration, twice a month. We can create a PBX file, publish it to the cloud, and create a subscription for any dashboard that is going to be decommissioned. We can send an email to those people at the specified time. This dashboard will be decommissioned. Apart from that, we can create alerts. With the help of these alerts, when a dashboard is decommissioned, we can clear the subscription and set up new alerts for upcoming dashboards. If we want to create alerts, we need a premium license. However, if we go for a subscription, a pro license is sufficient. With the help of this, we can achieve this.

What method would you use to evaluate enhanced efficiency of data retrieval in Power BI from various databases like MS request, server, and Oracle? So, in that case, what if we have a Power BI report server level, so in that case, what I would do is, whatever the number of sources are there, I would create a data mart to which I can indicate a dashboard. This is one method. The second method is, I would use import if I have been allocated as a premium licensing, so in that case, the import method allows us to create a maximum 100 GB PBIX file. So in that case, I would import all the data to the Power BI desktop level and start creating a dashboard. I prefer the import method because when you go for direct query, we cannot create calculated columns or measures into the source level for direct query. Instead, if you go for the import method, we can do the modification on the data level instead of the source level. This is the second method. And data retrieval in the sense, we can cross verify. I have done proper verification. I have used SSMS, and I have checked that I have been given access to various databases, including CD, credit driver data, CBC, and MCD. So, these are the sources set, and through API, NetSuite, and Salesforce. I have cross validated. One scenario is, where when I was working with a client, they had already developed a dashboard in customer 365, 360 view dashboard. But, in the source level itself, the field "industry type" was entirely changed to "partner" and "partner ID." So, on the SQL query, based on that calculation measures, everything that dashboard was broken. I did the analysis on SSMS properly, and I identified what all things were changed and how many views needed to be modified. I modified the views. Then, within two working days, I made the changes in the view. And then, after calculations, I've done the changes with the help of Aliyah's name. Then, the numbers are reflecting properly on the dashboard, and this is the way I presented it to the management. When you go for the import method, we can do the cleaning and cleansing in the inbuilt Power Query editor, through which we can do the merge, append, and replace values, and then use the first row as a header. This way, we can do the transformations.

Orders dot customer ID is equal to customer dot customer dot country is "jam". So, in this case, what we can do, we can instead of joining these two tables directly, we can import the data into the Power BI desktop level. And once we're in the feature that is the Power Query Editor, there we can use the merge option. This will take much less time compared to the performance-wise source level merge. Corrected transcript text: - Removed filler words: '', '', and 'like' when used as a filler/pause. - Fixed grammatical errors: changed "customer dot customer dot country is jam" to "customer dot customer dot country is 'jam'" to indicate that 'jam' is a string value. - Improved sentence structure for better clarity and readability. - Removed the part about importing data into Power Query Editor as it's not directly related to the SQL query issue.

Calculate sum of sales amount. Filter all sales for the year. Even tax formulas used in power report, can you explain why performance might be impacted, and how could you optimize this? So in this case, instead of going calculate here, we can use the function, call it as sum x. Sum x, if instead of going calculate function, what sum x will do, we can select the table, give the and the expression, and performance wise, it will get improved for the specific year.

We take an approach to enable self-service report that aligns to the corporate driven security policy by integrating all sources, doing the cleaning and cleansing part in Power, and building the Power Query Editor. After that, we create a model that starts with the help of tasks, schemas, and a Netflix schema. Once the model is studied, we can give this to the end users. They can just drag and drop to create as per their requirement. If they need more in terms of clarity and alignment, like cosmetic aesthetic changes, alignment, and some calculations required, in that case, the developer can work upon it. Security-wise, we cannot give access to download the file or export the file. That access cannot be given. Just for drag and drop, placing visuals, and making any changes, we can do it.

How do you incentivize your team to maintain good quality in their development of text expression and SQL query? How do you incentivize your team to maintain code quality in development of tags, expression, and SQL verbs? First, you need to understand the requirement. Instead of creating a calculated column, we can create a measure of scalar value, which would be beneficial. The second method is possible if the management has given access for source level changes. Whatever transformation is done at the source level, if access is given, then we can directly make changes at the source level. After that, we can take data into Power BI desktop level. If that's allowed, then performance will get improved. When you speak about aggregate values, if you need a scalar value, then that's the most useful method. Where I've been used, there are many cases when you speak about time-intelligent functions, such as QTD, YTD, MTD. If you want to calculate sales for the same period last year or parallel period, I've used a lookup value related to this text and aggregate function, like sum, sumx, count, countx, countax. For Boolean functions, if you have a Boolean 2 and 4, and if you want to count, then we have to use the countax function. We can also use variable functions with the help of variable functions, calculate, assign the variable 1, variable 2, and give if-then logic also we can apply. These tax functions have been used.

What strategy would you apply to ensure Power BI report remains accessible during database maintenance? What strategy would you apply to your Power BI where you put Siemens acceptable during database maintenance activity? Okay, if it's maintenance activity, so in that case, schedule a refresh. In the scheduled refresh, if it's in maintenance activity, we can go for the import method, and we can give this a subscription and alerts also when it has been refreshed and what time it is in maintenance. We can create alerts. With this strategy, reports will be accessible during maintenance. The second thing is we can go for the import method instead of direct query. If it's still in maintenance and there are no issues, we can show the dashboard with the data available for a specific duration. Apart from that, if it's in maintenance, we can create a scheduled refresh, and the dashboard will be refreshed at that time. With this can be achieved.