Manohar Jadhav

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

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

Yeah, basically, when there is a requirement, changes or user, business owner or end user request for, to add certain views or basically, yeah, it is at 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 may be a scenario where a table has millions or billions of records. And when we want to import, when we import all these records, that time a report may get heavier and it won't perform as per the expectations. So that time, we can create a view on our table or a new table from the source and where we can keep only required fields, required data and we can 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 is a major change or major update provided by the Microsoft. So basically, we keep a sheet in my previous organization. I used to keep a sheet, the Google sheet, which is placed in an organization's SharePoint where we used to keep track of version controls. I had created columns like what was the original version of the Power BI at the time of development of that particular report or dashboard, and what is the latest or ongoing version we are 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, where manager has full access and the same access to me, where I used to enter these records and maintain the versions of Power BI. Thank you.

Yeah, SQL Server index is a very good feature available in the SQL, I can say, MS SQL, where we can optimize the performance of the database. And same thing, if we use indexes at SQL Server, then definitely, it will improve the performance of the SQL Server. And the same data when we import in the Power BI, it will not affect performance on the Power BI report. Basically, it will improve the performance when we use indexes. And yeah, so there are basically indexes. I can say there are two main types of indexes are there, clustered and non-clustered. Whenever we create a primary key in the SQL, a unique clustered index will get created on the primary key. And at the same time, we can only create only one clustered index on a table. And if you want to use multiple fields, we can use a composite cluster. And same way, we can use multiple non-clustered indexes on multiple fields on a particular table. And yeah, basically, I can say it will improve the performance by using the index in SQL database.

Yeah, yeah, we can improve query performance by optimizing it. So basically, wherever we are using 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 have not set or applied indexes on most of the fields, then it will impact on the performance. So I usually tries 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 yeah, and sometimes we have to use star because we cannot apply indexes on all the fields due to storage issue. Whenever we add indexes, it will take additional storage space. So only selected or important fields we can consider for indexing purpose. And yeah, so with and even along with indexing, we can look into the joins and we can optimize on the joins part where we have used in the queries. And we should mention the fields which we have used for joins. Those have the indexes, either clustered or non-clustered index. 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 and sales and yeah so on the customer ID we can join this instead of ID. So we have for sales table we have used customer ID and for customer table we have ID. So instead of ID in from customer table we should use customer ID. Yeah, hope this ID is nothing but the customer ID from customer table. Thank you.

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

Yeah, basically, I never dealt with the self-service reporting with this particular thing. So I can suggest that we should consider, we should take care of all security policies and what I can say, the other guidance provided by the client or business users that we should follow. But I would like to brush up myself on this particular area. Thank you.

Basically, I would like to, I would suggest my team to follow and maintain one structure like nomenclature and of all the variables and other stuffs like suppose if we are giving an index name, then it should follow certain format. If we are giving, if we are calculating some, one column in Power BI, so we should follow the nomenclature. Same way with the DAX expressions where we can follow unwanted loopings, these all steps we can, I would recommend and I would like my team to follow the format.

Yeah, so here, if database is under maintenance and we have used import mode, then it won't affect on the Power BI reports, since we will have all the data imported in the Power BI and 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 is paused. Then I would recommend we can take some screenshots of the reports before database goes under maintenance. We can apply required filters or slicers in the Power BI reports and we can take screenshots and we can keep it in a PDF format or PowerPoint format for temporary usage. So this is one thing I can recommend. Thank you.