locked
SQL Server Analysis Services - Which version to install for a pilot? RRS feed

  • Question

  • I want to do a pilot where I will use Analysis Services tabular model and connect a PowerPivot based dashboard to it. Eventually I might implement the dashboard using Power BI as well. 

    The main reason I want to use Analysis Services is so I can build user level security to the data so users are able to see data based on their respective roles and positions within the organization.  

    So in order to do a pilot (ideally try it out using my laptop) what version of SQL Server can I install and get this. 

    I've done a lot of searching and somehow not clear to me as to what to install for this. 

    Can someone please point me to an exact link that I can use to download. 

    I assume there is a SQL Express kind of equivalent of this that is free or an evaluation version. 

    Sunday, November 22, 2015 2:09 PM

Answers

  • Hi

    Analysis services tabular does not have any express edition nor is it available using standard edition so it is BI or Enterprise only. there is however a 180 day trial.

    The next thing to consider is version.

    The latest production release is SQL 2014, you can download it here

    If you are a little more adventurous you can look at the current CTP 3.0 release of SQL. It is not yet in production but if this is only a a trial you may want to be able to play with the new features that should be introduced. 

     

    • Proposed as answer by Darren GosbellMVP Sunday, November 22, 2015 11:20 PM
    • Marked as answer by VivDev Sunday, November 22, 2015 11:25 PM
    Sunday, November 22, 2015 2:33 PM
  • Just hopping in for a clarifying note. The responses above do a great job of laying out the SQL Server versions and editions that can be used for this.

    I'd like to note that if you are using SSAS, you should not also be using Power Pivot. Power Pivot is an add-in to Excel which runs a private instance of SSAS Tabular as a child of the Excel process. If you are using SSAS for the modeling and analysis layer, you should access it directly through an Excel pivot table or Power View sheet. If you load the data from SSAS to Power Pivot you will lose the dynamic row-level security available in SSAS, and will only have security enforced at model refresh time, not at report interaction time - all data visible to the user who refreshes the Power Pivot model is available to anyone who interacts with the reports.

    Similarly with Power BI. When referring to Power BI, we might mean the Power BI Desktop application which packages Power Query, Power Pivot, and Power View in a standalone executable without any dependency on the Office installation, or we might mean the Power BI web portal, where we can publish models built in Power BI Desktop or in Excel Power Pivot and Power View. If utilizing Power BI Desktop, you should not import data from the SSAS instance, but instead query the SSAS model directly with only the reports defined in Power BI, not the model.

    Both Excel Power Pivot and Power BI Desktop can import data from SSAS to a local model, or they can query the SSAS model directly. You want to do the latter if you intend to support row-level security. Reports from either Excel (pivots or Power View sheets) or Power BI Desktop can be published to the Power BI web portal, regardless of how you are accessing the source data.

    GNet Group BI Consultant

    • Proposed as answer by Darren GosbellMVP Sunday, November 22, 2015 11:20 PM
    • Marked as answer by VivDev Sunday, November 22, 2015 11:25 PM
    Sunday, November 22, 2015 5:04 PM
  • An internet connection will only be necessary for accessing content on a Power BI web portal site, or if your SSAS instance is not hosted on site.

    If you have a need for offline access, Datazen is another Microsoft reporting tool that can access any SQL Server service (SQL, SSAS Multidimensional, SSAS Tabular) as well as flat files hosted in OneDrive for Business. Datazen includes in-app caching of data, respecting security roles defined at the source, across all mobile OSes and Windows for desktop. Depending on your use case, it may fit your needs better.

    If role-based, row-level security is a requirement, then PowerPivot is not the modelling tool to use. It cannot readily support this use case.

    GNet Group BI Consultant

    Monday, November 23, 2015 3:31 AM

All replies

  • Hi

    Analysis services tabular does not have any express edition nor is it available using standard edition so it is BI or Enterprise only. there is however a 180 day trial.

    The next thing to consider is version.

    The latest production release is SQL 2014, you can download it here

    If you are a little more adventurous you can look at the current CTP 3.0 release of SQL. It is not yet in production but if this is only a a trial you may want to be able to play with the new features that should be introduced. 

     

    • Proposed as answer by Darren GosbellMVP Sunday, November 22, 2015 11:20 PM
    • Marked as answer by VivDev Sunday, November 22, 2015 11:25 PM
    Sunday, November 22, 2015 2:33 PM
  • Thanks for the details. 

    How about the Developer Edition? Looks like that has everything and is inexpensive. 

    Sunday, November 22, 2015 3:29 PM
  • Developer Edition is identical to Enterprise Edition, except licensed for development and testing only.  

    Developer Edition or Evaluation Edition will both work.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Sunday, November 22, 2015 3:32 PM
  • Hi

    As David mentioned Enterprise and Developer edition are functionally the same but developer will not allow you to use it in any sort of "production" deployment so will work great for dev and test but you will have to eventually buy an enterprise licence if you chose to use it.

    Currently with the SQL server 2016 CTP, there are no editions everything works as an evaluation and we are likely to see at least one more revision before it it released to market.

     

    Sunday, November 22, 2015 3:57 PM
  • Just hopping in for a clarifying note. The responses above do a great job of laying out the SQL Server versions and editions that can be used for this.

    I'd like to note that if you are using SSAS, you should not also be using Power Pivot. Power Pivot is an add-in to Excel which runs a private instance of SSAS Tabular as a child of the Excel process. If you are using SSAS for the modeling and analysis layer, you should access it directly through an Excel pivot table or Power View sheet. If you load the data from SSAS to Power Pivot you will lose the dynamic row-level security available in SSAS, and will only have security enforced at model refresh time, not at report interaction time - all data visible to the user who refreshes the Power Pivot model is available to anyone who interacts with the reports.

    Similarly with Power BI. When referring to Power BI, we might mean the Power BI Desktop application which packages Power Query, Power Pivot, and Power View in a standalone executable without any dependency on the Office installation, or we might mean the Power BI web portal, where we can publish models built in Power BI Desktop or in Excel Power Pivot and Power View. If utilizing Power BI Desktop, you should not import data from the SSAS instance, but instead query the SSAS model directly with only the reports defined in Power BI, not the model.

    Both Excel Power Pivot and Power BI Desktop can import data from SSAS to a local model, or they can query the SSAS model directly. You want to do the latter if you intend to support row-level security. Reports from either Excel (pivots or Power View sheets) or Power BI Desktop can be published to the Power BI web portal, regardless of how you are accessing the source data.

    GNet Group BI Consultant

    • Proposed as answer by Darren GosbellMVP Sunday, November 22, 2015 11:20 PM
    • Marked as answer by VivDev Sunday, November 22, 2015 11:25 PM
    Sunday, November 22, 2015 5:04 PM
  • Thanks Greg for further details. Kind of realized this as I was researching on Analysis Services and actually what I was looking for in my ideal state scenario. Only one thing where I don't see it working out is when users may not be connected to the internet and would need the dashboard offline. In such a case, having the data in the model itself would be more helpful. Will have to think through that and see how important it is to have that ability. 
    Sunday, November 22, 2015 11:28 PM
  • An internet connection will only be necessary for accessing content on a Power BI web portal site, or if your SSAS instance is not hosted on site.

    If you have a need for offline access, Datazen is another Microsoft reporting tool that can access any SQL Server service (SQL, SSAS Multidimensional, SSAS Tabular) as well as flat files hosted in OneDrive for Business. Datazen includes in-app caching of data, respecting security roles defined at the source, across all mobile OSes and Windows for desktop. Depending on your use case, it may fit your needs better.

    If role-based, row-level security is a requirement, then PowerPivot is not the modelling tool to use. It cannot readily support this use case.

    GNet Group BI Consultant

    Monday, November 23, 2015 3:31 AM