Aditi Ghosh

Hello. So this is Aditi Ghosh, and I'm from Bangalore, India. And, I have been working for Dell Technologies for the past 2.4 years. And, in this process, I have also been working on Tableau. That is, my primary skill, and I have worked for companies like Infosys. Previously, I have worked as a presales consultant for some startups and mid-level companies. So, overall, I have around 7.5 years of experience. And, I'm looking to engage myself more into business intelligence as well as the upcoming technologies since AI is booming in the market. So, I'm also planning to go for an internal course or something to upgrade myself. And, while I'm working on Dell or Intel, I have been dealing mostly with stakeholders. They comprise senior managers, directors, VPs, and other executives. And the reports that I have built have had good exposure, and I have been doing a lot of report information gathering, building data models, and also team handling experience that comprised people around 2 to 3. And, there are also people who have been working in the technical expertise or in technical areas, like principal consultants, solution architects, etc. So, this is all about me and my background. I have done my master's in instrumentation science and my graduation in physics, both from Debu University and Jatpur University. I am basically from Assam, but currently, I am in Bangalore, and I'm looking forward to more interaction with MRI. That's all I got. Thank you so much.

So, basically, I would be choosing to build a new table or view in a database for a Power BI repository. So that's a question. Now, mostly, in a report, whatever we will be uploading, that will be from a table or a view. And, whatever we will be choosing to represent or build on a Power BI report, we need to fetch the same information in the table or the view. So that's the understanding I have. And, in this perspective, data source connectivity is very much important. And, also, in order to build such reports with all the information, you have to have a table or a view to be created in the data source. Or, if you are trying to build something like a disconnected table through which you are planning to do some toggling between different matrices. So that can be done as a visualization in a Power BI report. Otherwise, whatever you need for the report can be done from the data source perspective. So instead of burdening more by creating complex calculations on a Power BI report, it's best to bring such complex calculations from the back end itself. But, of course, there are certain requirements that need to be done on the Power BI end using DAX formulas. And, if it's at all required, then definitely we have to go for it. So that's the whole scenario.

When optimizing SQL Server indexes used by Power BI reports, I consider the following factors. Definitely, first of all, I will try to reduce the volume of the reports that we print. Because when it comes to Power BI or any kind of reporting, it's very essential that the volume of the data is maintained correctly. Otherwise, it will take a lot of time to refresh it. In fact, if you try to publish this report with an extract or as a live connection, it will create a lot of difficulty in uploading it and then viewing the image because that's a direct impact on the performance. So the first thing to keep in mind is that whenever you have this kind of situation where the volume is very big, first of all, create an extract. Go with an import model of it, and reduce the volume of the information to only what's restricted to the report that you are going to build. And that's the first thing. Create as few tags or complex queries as possible. Use very limited filters that can actually impact performance. And from the SQL side, you can create a view specific to the report that will have a minimized number or only the required set of information. And instead of creating one complete denormalized table, you can create a star schema with one fact table and multiple dimension tables. Like, it means normalization. And so, that's the kind of schema that you can opt for, and it will increase the performance of the SQL Server queries onto Power BI. Thank you.

What is your approach to manage large datasets in Power BI with consideration to both performance and accuracy? Now when it comes to large datasets, first of all, it's something that we cannot help with. If the dataset is really large and all of it is required, the best practice is to create an extract or import the connection. So that will create a snapshot of the information, and it will not impact the performance a lot. So that is the first thing from the performance perspective and as well as with the accuracy. Accuracy is something that needs to be dealt with at the very beginning. It's when you're fetching this information into your Power BI report. And the first thing is, like, you have to keep on validating this information. Because when it is a very huge dataset and there are a lot of granularities on top of it becomes very difficult to understand what should be the exact results. So the best thing you can do is validate the result before you even get into Power BI. If the data is accurate, if your model is correct, then definitely your data will be accurate. And like I said before, try to get a star schema instead of getting a Snowflake schema. Or you can also build different dimension tables from where you can fetch these different types of information and then put them in a single model in your Power BI report. So that way, the accuracy is also maintained, and the validation is also a bit easier to do. So that or you can maintain the integrity of the data. That's what I can say without any loss of information. So that's what I believe would be a helpful thing in maintaining performance and accuracy. Thank you.

Explain how you would use index views to enhance performance of Power BI report? Well, I'm not sure about this particular answer because, I do not have much idea about using index views, to enhance performance. But, surely, I will look forward, on this. And, since I'm working on Power BI and with a really good amount of data, so this could be something helpful in my work also and as a learning thing. But right now, do not have any answer for this question.

Given this debts formula snippet used in Power BI report, can you explain why the performance might be impacted and how you would optimize it. Calculate some sales filter. So, basically, what like, the use of this particular information is to get the sum total for that entire, set of information that you have fed into the, Power BI report. And, also, there is a filter condition that is restricting the year to 2022, and it's an all function. So all basically, it will avoid any of the filters, or the fields that is present on the view. So it is like a kind of filter function or a table filter function, you can say, in which the all is used, basically to avoid that particular, scenario. So if, say, if that is the condition, if this is this is what your, if this is if this condition is implemented, then definitely you have excluded only 1 particular year out of it. But what if it has 10, years of data? So out of this is definitely not going to be helpful, and it is going to take a lot of time because the sum is like, it is doing a complete sum of the entire setup table and whatever be the, you know, the granularity of the report. So this is something which is going to impact, the optimization of the, you know, in, performance of the report. And if in case if you really want to exclude this particular, year, from your calculation, just use instead of using, what to say, that filter condition inside the calculation, use it outside. So that is that's how you are actually restricting the information on the view itself. There is no point in using the filter function in this way within the calculation. It is not just, making, to say, it's it's not going to give you the actual result that you are, looking forward to. And moreover, that will it is, like, excluding just 1 filter and that too on top of the view. So if that is the, what is the result that you want, you can use it onto the filter shelf instead of using it in onto the query. So that's how I would be optimizing it. Thank you.

Review the SQL queries. The pipe below, there is a potential performance issue with it. Can you identify the problem? And suggest how would you rewrite the better performance? So let's start from order in a join. Well, in this case, Well, the first thing that I would do in order to optimize it's a inner join, and the condition is drawn at the end of this query. So my first approach will be get the information where Germany is excluded from the customer's table at the very beginning itself. And then you can create a inner join with the orders table to fetch the information that you need. And, yeah. So that would be my, way of increasing the better performance. So that, even before you start, the actual execution, whatever you need for the query is present, and then you can execute it at your end. Or you can also create a function for this because functions perform better than the, you know, than the views or this kind of a data manipulation, or we can create a separate query where, Germany is not included and then use the same thing, or a view which does not have Germany in it and, then use the same thing in your query for doing that inner join. So that way, it can be optimized. So that's what I would have done. Thank you.

What approach do you take for an app links of service reporting that is in line with corporate governance and security policies? Well, I'm not really sure about the answer for this. However, like what exactly would be the approach to enable self-service reporting. But definitely, if we need to give security, then cybersecurity has to be taken into account. We have to mask it. We have to establish certain firewalls before we let it out to the users in case there is any kind of threat. And, of course, every company will have its own security policies and governance rules, so those will be documented. If you follow them, and we see that the data we have and the process we are implementing or if there is a single sign-on established, then that would be great. Like, based on the kind of person, whoever is part of the corporate, should be given access. And that kind of security, we can establish on the report so that not just it will help the user to look at the information, but it will mask it from anybody else or any third party to see the information. So that way, we can prevent the report from a security perspective as well as following the governance rule. That's my approach. Thank you.

What strategies would you apply to ensure Power BI reports remain accessible during database maintenance activities? Well, in this kind of scenario, most of the time, if Power BI reports are involved in data governance activities, make sure it schedules on top of it, then they don't conflict with the database maintenance activities. If it's an import of information or import connectivity, you can actually stop the schedule for that particular instance of time. So, if at any point of time there's anything going wrong with the database, it won't directly impact the dashboard and show you wrong information or show you blank information on the Power BI report. That's the first strategy I'd take. If it's a live connection, for example, then for the time being, if there's any way of disconnecting it from the database or not showing that particular information, the best practice is to inform users that do not have any information uploaded or any new information to be checked on the report as some data governance or some data maintenance activities are going on. So, that's one scenario where you have to stop accessibility. The best practice or the best strategy is to get an import connectivity for the Power BI report because you can disconnect the schedule for the timing until the maintenance activity is going on, and that will not impact the information that's already existing on the report.

Power BI works well with Power Automate, in this kind of a situation, it's always best to have an archival workspace altogether, which doesn't get overwritten. If there's a way to use Power Automate to upload a new file with the current date to have a historical information or a file uploaded in that particular workspace, then that's the best strategy I would think Power BI can have for restoring the historical reports and archiving it. So that at any point of time, if a user wants to go back and check what was happening at the previous time, when there was a previous version, the previous month or the previous quarter or the previous year, then that would be very helpful. But for that, you need a completely separate space. And there has to be a way, maybe through Power Automate, to archive or generate a particular file, which can be used as a historic file. However, since I have not worked on Power Automate yet, I won't be able to tell you more about this strategy. But this is what I have in mind that there should be some automated way of archiving this information in Power BI in a different workspace altogether. And with the date with the name of the file and along with the date. Because otherwise, how would you know that this is historic information?