Manohar Jadhav

My name is Manoj Zadok. I started my career in 2009 as a junior Java developer in a manufacturing industry. The company, where I worked, was an expert in design and development of SPMs, special purpose machines, like CNC and VMC. And I joined as a junior Java developer. I worked on an in-house product called the ITMS, the insert and tool management system. A tool used in CNC operations is called an insert, which is expensive and very delicate to handle, and very difficult to keep track of. So we worked on the product where it automatically retrieved the insert. Whenever the operator wanted the insert, he had to log in to the machine through his credentials or biometric. He could get a new insert, and at the same time, he had to deposit the older one. If he wanted to retrieve it for the first time, then he had to contact the admin or his supervisor. Then, I learned their database design. I worked there for around 7 years. Then I moved to the ready mix industry where I worked as a software developer and project coordinator. I worked on an ERP system for the industry, which was specific to ready mix. For that implementation, I visited Bahrain. The client was a national concrete company. Then, during the pandemic, I switched to Inetframe Technologies. It's a software company that deals in the healthcare domain. The client was BCBSRI, a Blue Cross and Blue Shield for Rhode Island, USA. I worked here on various projects like email lead generation, then HKN Hospital Quality Management. I led this project. I was a project lead, and then I worked on a few migration projects, such as Tableau to Power BI and MS to Snowflake database. I worked as a data analyst, now as a senior data analyst, then as I mentioned in IHQ, I led the project. I was the project manager, and from requirement gathering to implementation of the application, it was my responsibility. And in all these projects, the reporting part was my responsibility. I was the main developer. And whenever I led the Power BI team, which was 5 to 6 members. I have team management experience as well as experience in requirement gathering and dealing with the client.

So all these experiences I have. Okay. So, basically, the while to troubleshoot the Power BI report, Of we have, we had embedded a Power BI report in a node JS application. The project name was Hospital Quality Management. And we had embedded both the link as well as the iframe code. Nowadays, Power BI provides

Yeah, basically, when there's a requirement, change or user, business owner or end-user request to add certain views or, it's basically at the database for Power Bear. Okay. When would you choose to build a new table or view in the database for Power Bear? Okay. Basically, when we don't want to pull all the data from the source table from the database. Suppose there's a scenario where a table has millions or billions of records. And when we import all these records, that time a report may get heavier and won't perform as per expectations. So, that time, we can create a view on our table or a new table from the source and keep only the required fields and data. We can then use that newly created view or table as a source for our Power Bear report. Yeah.

Yeah, basically, the version control - I don't usually change the versions immediately unless there's a major change or a major update provided by Microsoft. So, we keep a sheet in my previous organization. I used to keep a Google sheet, which was placed in the organization's SharePoint where we kept track of version controls. I had created columns like the original version of Power BI at the time of development of that particular report or dashboard, and the latest or ongoing version we're using. So, I used to maintain that sheet, and it was shared to my manager and all the team members. Team members had only read-only access, while my manager had full access, and I also had full access, where I used to enter these records and maintain the versions of Power BI. Thank you.

SQL Server indexes are a very good feature available in SQL, specifically in MS SQL, where we can optimize the performance of the database. And similarly, if we use indexes at SQL Server, it will definitely improve the performance of SQL Server. When we import the same data into Power BI, it will not affect the performance of the Power BI report. In fact, it will improve performance when we use indexes. There are basically two main types of indexes: clustered and non-clustered. Whenever we create a primary key in SQL, a unique clustered index is created on the primary key. Additionally, we can only create one clustered index on a table. If we want to use multiple fields, we can use a composite cluster index. Similarly, we can use multiple non-clustered indexes on multiple fields on a particular table. Overall, using indexes in a SQL database will improve performance.

Yeah, we can improve query performance by optimizing it. So basically, wherever we're using a star, like in a select query, when we use a star, that time it tries to fetch all the fields from the table. And suppose we haven't set or applied indexes on most of the fields, then it will impact on the performance. So I usually try to include the fields in the select query where all the indexes are applied. So it will be the data fetching from the query will be faster. And sometimes we have to use a star because we cannot apply indexes on all the fields due to storage issues. Whenever we add indexes, it will take additional storage space. So only selected or important fields we can consider for indexing purposes. And so, with indexing, we can also look into the joins and we can optimize on the joins part where we've used in the queries. And we should mention the fields we've used for joins, those have the indexes, either clustered or non-clustered indexes. So it will definitely optimize the query performance.

Yeah, now here we have a use the ID from customer table in the join tables statement. Instead of ID, we can use customer ID in the last line and it will improve the performance and utilization basically. So yeah, we have to just replace this ID with customer ID since I assume that customer ID is the common field in both the tables sales as well as customer table and so on. On the customer ID, we can join this instead of ID. So we have used customer ID for sales table and ID for customer table. So instead of ID from the customer table, we should use customer ID. Yeah, I hope this is the customer ID from the customer table. Thank you.

Here we have given the hard-coded value of the data set. For the year 2022, which we can use as a parameter, we can make it dynamic. Yeah, that's the one change I can recommend here. Thank you.

Yeah, basically, I never dealt with the self-service reporting for this particular thing. So I suggest we consider taking care of all security policies and follow the other guidance provided by the client or business users. I'd like to brush up on this particular area myself. Thank you.

I would like to suggest that my team follow and maintain one structure, like nomenclature, for all variables and other items. For instance, if we're giving an index name, it should follow a certain format. Similarly, when we're calculating a column in Power BI, we should adhere to the nomenclature. The same principle can be applied to DAX expressions, where we can avoid unnecessary loopings. I would recommend and like my team to follow this format.

Yeah, so here, if the database is under maintenance and we've used import mode, then it won't affect Power BI reports, since we'll have all the data imported into Power BI and the same thing gets published. So that will not impact. But in case of live connection or direct query connection, whenever we refresh the report, it always tries to connect to the database and fetch the data. And during maintenance activities, it will not fetch if the database is shut down or it's paused. Then I would recommend that we take some screenshots of the reports before the database goes under maintenance. We can apply required filters or slicers in the Power BI reports, take screenshots, and keep them in a PDF format or PowerPoint format for temporary usage. So this is one thing I can recommend. Thank you.