locked
Is thorough SQL server knowledge required for Business Intelligence career?? RRS feed

  • Question

  • Hi All,
    Happy New Year to all of you..
    I am an Excel Power user, Excel trainer, BI enthusiast slowly getting into teaching Business Intelligence using Microsoft Excel's PowerPivot. A few years back I was bitten by a bug called Power-BI. I am not a  person too good at programming languages. But I am in the business of teaching and consulting of Microsoft Excel.

    Here is the problem.. For the last few years.. I was trying hard to enter into BI world. My goal is to learn BI using Excel's PowerPivot. But when I started looking at it..things are not so easy for me as I thought..ie. there are some concepts related to cubes, DBA concepts, running reports and queries to access data from different sources, tabular modeling..etc. It's a big mountain which is very steep for me. Now my question is..

    To teach or to become a hardcore Power BI Analyst (using PowerPivot, Power Query, Power Maps, PowerView etc), shall I have to master all these Database concepts too deeply?? Shall I have to master concepts related SQL server's SSIS, SSAS, SSRS services. Why because we need to master DAX code in PowerPivot which will slowly take you to SQL environment in some other way. But If I take this SQL path I will become Database guy and moreover I do not like it and I can not fit myself there. If the answer is Yes, to what extent I have to learn these database concepts. You can ask me any questions to understand my situation more clearer.

    Thanks in advance.
    Arjun M Shetty
    Friday, January 2, 2015 5:30 PM

Answers

  • Hi Arjun,

    Not a typical forum question, but I will try and help you out.

    If I understand the basics of your question, you are asking if you need to know everything there is about Power BI in order to be a hardcore Power BI analyst. In my opinion, the answer would be no. However, hardcore implies you are an expert and an expert knows a good bit about everything. And as a hardcore analyst, you know deeply how to answer a lot of different questions about most any type of data. I like to think of BI, including Power BI as a variety of roles.

    IT expert: This role manages the infrastructure for data bases, cubes, OS servers, etc... and how to meet security and compliance of the overall environment as required by the business.

    Analyst expert: Knows the data in and out. Is very good at massaging the data with the tools provided into reports and for answering the questions the business needs to answer. Is great at making beautiful reports consumed by others.

    BI Expert: Knows how to build cubes and SQL databases. Writes queries, optimizes performance and keeps up with latest trends and technologies. Is typically someone who will write code to accomplish the business needs.

    In small companies, one person can play multiple roles and even every role. In larger companies, not so much.

    I would suggest you find what are you have a passion for, then become an expert.


    Brad Syputa, Microsoft Power BI This posting is provided "AS IS" with no warranties.

    Tuesday, January 6, 2015 5:55 PM
    Moderator
  • From my perspective, SQL Server for business intelligence and PowerPivot in Excel overlap significantly. The main reasons to move from Excel to SQL Server are 1) data rows greater than 1-5 million 2) row-level security is required 3) incremental or scheduled data loads are needed. If you don't need any of these 3 things, you probably will not need SQL Server. I would recommend that you hone your skills in Excel before learning SQL Server.

    Also be aware that SQL Server is an umbrella that covers WAY more than business intelligence. When you look at SQL Server, focus on SQL Server Analysis Services (SSAS), and then specifically Tabular Model opposed to Multi-dimensional models. If you just explore "SQL Server" on the web, most of what you read is likely to be pure DBMS database info, which is entirely separate from SSAS. As you get into SSAS Tabular, you may end up expanding to SSIS, MDS, and multi-dimensional, but start with SSAS Tabular. The screens for working with data models in SSAS look just like the PowerPivot window in Excel. The additional complexity is really around security and scheduling of data loads, so just focus on being an expert in PowerPivot in Excel for now.

    To be good at Business Intelligence, you need basic data modeling skills. Please refer to The Data Warehouse Toolkit by Ralph Kimball. It goes much deeper than you need, but is the definitive guide for data modeling for business intelligence. This will help you with data modeling concepts (eg. star schema, fact tables, dimensions, relationships). Pragmatic Works has some nice free webinars too. See this for example: http://pragmaticworks.com/Training/FreeTraining/ViewWebinar/WebinarID/1704 

    You have the right books for learning DAX. It is the core skill for being good at PowerPivot. Make sure you fully understand context (Rob Collie covers very well). If you don't understand context, DAX will be extremely challenging. My experience is that even the DAX experts (other than Russo and Ferrari!) still struggle to get DAX formulas right the first time. Do not be discouraged when a formula does not work. Keep trying and learning. You are at the front end of the learning curve. Most Excel users still do not know DAX even exists. That already makes you somewhat of an expert!

    As far as databases and SQL are concerned, I would not recommend you becoming an SQL expert at this point. It sounds like SQL is a challenge to you, and with Power Query and PowerPivot, SQL is becoming less relevant a skill. In my corporate environment, our Power users don't use SQL anymore. Everything they extract from databases is being done with Power Query and any joins or modeling is being done in PowerPivot. Once you are an expert in using Power Query, a database and a flat file really aren't much different. If you are good at shaping data from a flat file, you will be good at working with data from databases.

    Here's what I would recommend as a goal: learn to take from multiple sources (Excel, CSV, database, web), shape and load it via Power Query, create a star schema in PowerPivot, enhance the model using DAX, create reports with PivotTables and Power View. When you can do that easily, then consider SQL Server as a further opportunity. Here is a good example of a project as described: https://support.office.com/en-in/article/Power-BI--Getting-Started-Guide-bd30711a-7ccf-49e8-aafa-2e8f481e675d

    I hope some of this helps! Best of luck to you.

    Mike

    Friday, January 23, 2015 11:35 PM

All replies

  • Hello Everybody,Hi Everyone,

    Please answer this question as I have been waiting to be clarified for the last couple of years. Is something wrong with the question i.e. posting etiquette and rules not followed or it just not attracted anyone's interest. Please let me know. Thank you so much all.

    ArjunMShetty

    Tuesday, January 6, 2015 5:00 AM
  • Hi Arjun,

    Not a typical forum question, but I will try and help you out.

    If I understand the basics of your question, you are asking if you need to know everything there is about Power BI in order to be a hardcore Power BI analyst. In my opinion, the answer would be no. However, hardcore implies you are an expert and an expert knows a good bit about everything. And as a hardcore analyst, you know deeply how to answer a lot of different questions about most any type of data. I like to think of BI, including Power BI as a variety of roles.

    IT expert: This role manages the infrastructure for data bases, cubes, OS servers, etc... and how to meet security and compliance of the overall environment as required by the business.

    Analyst expert: Knows the data in and out. Is very good at massaging the data with the tools provided into reports and for answering the questions the business needs to answer. Is great at making beautiful reports consumed by others.

    BI Expert: Knows how to build cubes and SQL databases. Writes queries, optimizes performance and keeps up with latest trends and technologies. Is typically someone who will write code to accomplish the business needs.

    In small companies, one person can play multiple roles and even every role. In larger companies, not so much.

    I would suggest you find what are you have a passion for, then become an expert.


    Brad Syputa, Microsoft Power BI This posting is provided "AS IS" with no warranties.

    Tuesday, January 6, 2015 5:55 PM
    Moderator
  • Hello Arjun,

    Thank for posing this question. I am sure many others are faced with similar dilemma.
    Since this was also posted on our site, please read responses there from me and another site user (Anne Walsh)

    http://www.powerpivotpro.com/2014/08/power-pivot-a-revolution-in-slow-motion/#comment-94686 


    Regards, Avi www.powerpivotpro.com PowerPivotPro

    Wiki:How to ask a Power Pivot Question to get a prompt, accurate and helpful response


    Tuesday, January 6, 2015 9:18 PM
  • Hey Brad and Avichal, I am so happy that somebody looked at my muddle and consoled me.

         I want to be an Analyst in BI who can showcase the process of digging up the data and bring out interesting insights using Microsoft Power-BI tools and build nice Dashboards and KPIs to help the Microsoft Excel brotherhood all over the world (since I am a training professional and not working for any corporate).

    I have been studying the following paperbacks just to start with. I fully enjoy studying all the concepts related to PowerPivot and its real power, DAX code and the magic it can do, the insights we get from it etc.

    1. DAX Formulas for PowerPivot - Rob Collie (Completed)
    2. Powerpivot for Data Analyst - Bill Jelen, (Completed)
    3. Microsoft PowerPivot for Excel 2010: Give your data meaning - Alberto Ferrari & Marco Russo (Half way)
    4. Practical PowerPivot & DAX formulas for Excel 2010 - Art Tennick (Not yet)
    5. Microsoft Excel 2013 - Building Data Models with PowerPivot -Alberto Ferrari & Marco Russo (Not yet)
    6. Microsoft SQL Server 2012 Analysis Services: The Bism Tabular Model-Alberto Ferrari & Marco Russo (Not yet)

    And I have already exposed my self to concepts like measures, hierarchies, calendars, dimensions, facts etc plus many interesting and powerful, never imagined things coming on the way as well, and even churned out the data using the BI conceptual knowledge just learnt.

    My strategy to master BI:

    While I study all these books, I try to build my own databases and apply all these concepts and follow some videos for more clarity, post any questions if I get on my way, teach somebody the same. But even though the authors are teaching very clearly like standing in front of me and talking, it is like a real daunting task for me to understand some Database and SQL concepts on the whole because of some lackings mentioned below.

    A) I have no chance to work on SQL server and do not know where to start. (Actually installing the right version of SQL server itself is a puzzle for me as there are many versions)

    B) I have no practical exposure how exactly the Databases behave in a real DB world.

    C) If I have to understand SQL, what extent it should be i.e. Is it a one mile wide and one foot deep OR one foot wide and one mile deep.

    I want to get the kind of mastery on SQL server which I have on Excel (I do not know whether I am gluttonous here), but still do not know where to start and what extent to learn. I have been living with this feeling for the last 2/3 years or so. Is it looking silly for you?? It's kind of a feeling that SQL world has progressed so far that it can take ages for me to master it. I like the Database world but at the same time it is hard to tame. Because I did not know the real depth. I am not having any kind of confusions like these about Excel because I know the depth of the subject and what people can do with it.

    Is my current strategy is right OR if not, what is the right plan of action in the given situation?

    Please suggest me if you feel any changes in my strategy. I give a hearty welcome to your suggestions.

    Thank you so much. Ask me questions if you feel have any queries about my situation.

    Arjun

    Wednesday, January 7, 2015 7:07 AM
  • From my perspective, SQL Server for business intelligence and PowerPivot in Excel overlap significantly. The main reasons to move from Excel to SQL Server are 1) data rows greater than 1-5 million 2) row-level security is required 3) incremental or scheduled data loads are needed. If you don't need any of these 3 things, you probably will not need SQL Server. I would recommend that you hone your skills in Excel before learning SQL Server.

    Also be aware that SQL Server is an umbrella that covers WAY more than business intelligence. When you look at SQL Server, focus on SQL Server Analysis Services (SSAS), and then specifically Tabular Model opposed to Multi-dimensional models. If you just explore "SQL Server" on the web, most of what you read is likely to be pure DBMS database info, which is entirely separate from SSAS. As you get into SSAS Tabular, you may end up expanding to SSIS, MDS, and multi-dimensional, but start with SSAS Tabular. The screens for working with data models in SSAS look just like the PowerPivot window in Excel. The additional complexity is really around security and scheduling of data loads, so just focus on being an expert in PowerPivot in Excel for now.

    To be good at Business Intelligence, you need basic data modeling skills. Please refer to The Data Warehouse Toolkit by Ralph Kimball. It goes much deeper than you need, but is the definitive guide for data modeling for business intelligence. This will help you with data modeling concepts (eg. star schema, fact tables, dimensions, relationships). Pragmatic Works has some nice free webinars too. See this for example: http://pragmaticworks.com/Training/FreeTraining/ViewWebinar/WebinarID/1704 

    You have the right books for learning DAX. It is the core skill for being good at PowerPivot. Make sure you fully understand context (Rob Collie covers very well). If you don't understand context, DAX will be extremely challenging. My experience is that even the DAX experts (other than Russo and Ferrari!) still struggle to get DAX formulas right the first time. Do not be discouraged when a formula does not work. Keep trying and learning. You are at the front end of the learning curve. Most Excel users still do not know DAX even exists. That already makes you somewhat of an expert!

    As far as databases and SQL are concerned, I would not recommend you becoming an SQL expert at this point. It sounds like SQL is a challenge to you, and with Power Query and PowerPivot, SQL is becoming less relevant a skill. In my corporate environment, our Power users don't use SQL anymore. Everything they extract from databases is being done with Power Query and any joins or modeling is being done in PowerPivot. Once you are an expert in using Power Query, a database and a flat file really aren't much different. If you are good at shaping data from a flat file, you will be good at working with data from databases.

    Here's what I would recommend as a goal: learn to take from multiple sources (Excel, CSV, database, web), shape and load it via Power Query, create a star schema in PowerPivot, enhance the model using DAX, create reports with PivotTables and Power View. When you can do that easily, then consider SQL Server as a further opportunity. Here is a good example of a project as described: https://support.office.com/en-in/article/Power-BI--Getting-Started-Guide-bd30711a-7ccf-49e8-aafa-2e8f481e675d

    I hope some of this helps! Best of luck to you.

    Mike

    Friday, January 23, 2015 11:35 PM
  • Hey Mike,

    Thanks for the guidance. It is so useful and I have a better picture on what to do.

    Arjun M. Shetty

    Sunday, January 25, 2015 1:31 AM