SQL BI Knowledge Checklist July 23rd, 2013

Vinod Kumar

Being part of MTC (Microsoft Technology Center) is so much fun because we meet so many people and there is obviously opportunity to learn from others too. Recently, one colleague pinged me asking they were applying for some BI related roles internally and wanted to know if I had something on the lines of SQL DBA Checklist.

After searching my blogs, I didn’t find anything on this subject. So I thought, let me pen down some ideas when preparing for BI based interviews. This by any means is not exhaustive but let me categorize them into Basic, Intermediate and Advanced. So based on what role you apply, the depth on each topic might vary and based on organizational requirement there can be requirements on specific topics which you will need to know in depth.

Basic knowledge

Understand basics of solution area (i.e., foundation knowledge of data warehousing and Business Intelligence, overview of different technologies available in the stack and their features) and have some hands-on experience in using them. Know where to start looking and how to start experimenting if you don’t have knowledge in certain area.

  • Data Management
    1. Articulate what Databases and respective components are.
    2. Articulate how to use SQL Server Management Tools work.
    3. Articulate the OLTP systems.
    4. Consider basic implementation that works with both OLTP databases and data warehouse databases (i.e.: denormalization, indices, partitioning etc).
  • Data Warehousing
    1. Articulate the theories behind different data warehousing design approaches (Inmon’s and Kimball’s).
    2. Articulate the basic concepts and theories behind Multidimensional Modeling (Ralf Kimball’s approach)
    3. Articulate OLAP and differences between OLAP and OLTP systems.
    4. Differentiate and know the different Microsoft Appliances for DW (PDW, Fast Track and others)
    5. Basics of implementing an ETL (theoretical and implementation-wise).
    6. Articulate the concepts and considerations of implementing Master Data Management (theoretical and implementation-wise).
  • Business Intelligence
    1. Articulate the concepts and considerations of implementing a BI solution on top of SharePoint.
    2. Articulate the concepts and considerations of implementing a BI solution in the cloud.
    3. Articulate the concepts and considerations of implementing a Data Mining Solution.
    4. Implement Dashboards- Analytics and Data Mining – Report Generation and Distribution using appropriate Tools.

Intermediate Knowledge

Must know how to apply a topic in new ways to address complex, large-scale, and unusual customer situations with in-depth understanding of developing, implementing and deploying end-to-end BI Solution.

  • Data Management
    1. Write SQL Queries and use built-in functions and create sub-queries.
    2. Create and execute stored procedures as well as user-defined functions.
    3. Implement error handling.
    4. Program with T-SQL.
    5. Improve query performance.
    6. Install, configure, deploy and patch manage SQL Server and its tools.
    7. Upgrade DB Servers.
    8. Migrate databases to new environments and new editions of the SQL Server product.
    9. Implement partial database containment.
    10. Implement user-authentication techniques.
    11. Create user-defined server roles and use them. Implement security.
  • Data Warehousing
    1. Implement solutions using functionalities of SSAS, SSIS, SSDB, MDS and DQS.
    2. Choose the right configuration approach in each tool.
    3. Know to secure the Data Warehouse and security basics.
    4. Know to Debug, troubleshoot and deploy each product individually.
  • Business Intelligence
    1. Implement solutions using Microsoft BI Tools features and functionalities like SSRS, PowerPivot, PowerView, PowerQuery, PowerMaps, PerformancePoint, Azure Reporting, Windows Azure SQL Server, BI Features in SharePoint Online and O365 etc.
    2. Choose the right configuration approach in each tool.
    3. Must know to create and manage organizational performance by using four perspectives for the balanced scorecard: Financial perspective, includes metrics such as revenue, cost, and profit.
    4. Build KPI Details reports to use in conjunction with scorecards in a dashboard. Use KPI Details reports to show additional information about the KPIs and other items that are in a scorecard.
    5. Use a scorecard as data source for trend analysis chart.
    6. Operate how an Analysis Service server instance in SharePoint integrated mode loads, queries, and unloads data, including data processing if the workbook is configured for PowerPivot data refresh.
    7. Identify when to use Reporting Services, Report Builder, or Power View; design/implement context transfer when interlinking all types of reports (SSRS, Report builder, PowerView, Excel, PowerPivot); implement BI tools for reporting in SharePoint (Excel Services versus Performance Point versus Reporting Services); know how to make a subscription strategy or Data Alerts for organizational needs.

Expert Knowledge

Be familiar with integration and interoperability scenarios, custom code, scripts, application solution development, data model design, and end-to-end solution troubleshooting and debugging.

  • Data Warehousing
    1. Design an optimized data model based on industry requirements.
    2. Track data issue from the report up to the data source to identify the problem.
    3. Choose the best SSIS components to provide best performance possible.
    4. Create SSAS stored procedures and extensions.
    5. Create SSIS custom components and scripts.
    6. Implement a sliding window on the DB engine level.
  • Business Intelligence
    1. Extend SQL Server Reporting Services via code.
    2. Implement workarounds to overcome PerformancePoint limitations.
    3. Choose the right SSAS Model (Tabular or Multidimensional).
    4. Advanced MDX skills
    5. Advanced DAX skills.

So here is the brain dump of activities that come to my mind. The idea here is to do a self check if we know the end-to-end story when it comes to Microsoft BI offerings. Do let me know your thoughts.

Tags: , , , , , , ,

This entry was posted on Tuesday, July 23rd, 2013 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.


17 Responses to “SQL BI Knowledge Checklist”

  1. Linda says:

    This is a really nice overview of business intelligence for business analysis and reporting…

    Thanks!

  2. Rakesh says:

    Hi Vinod sir ,

    i have small doubt in sql server ,can u tell me whether our all dml operations will be permanently stored in log file and even data also in log file or will it just stores the address in log file . For example if i deleted 100 records from student table so is it possible to read 100 records from log file by using any query. Actually i had noticed that even after deleting the 100 records by doing auto comitt still i am not getting the space in mdf file so i thought that the data will be permanently available in mdf file not in ldf file .can u kindly please clarify me.

  3. Prathyusha says:

    Excellent post.

    Bookmarked!!!

    Now I know where to concentrate to become a good BI Professional

    Many thanks !

  4. manasdash says:

    This is an excellent article and will guide the BI enthusiast to land up in the correct path. Thank you so much for the gyan.

  5. Sagar Koti says:

    Thank You Vinod for your excellent write up on BI. You are a true polyglot

  6. Phil Scalo says:

    Vinod, thank you for taking the time to write this up, very helpful. I have been watching the indexing videos on Pluralsight, well done.

  7. Anish Shenoy says:

    Thanks a lot for this sir. This page is to be book marked :)

  8. Sandip Pani says:

    Thank you for sharing Vinod.

Leave a Reply