locked
What is Alternate of Report Model(.smdl) in SQL Server 2016 and how to Migrate .smdl Reports to SQL Server 2016 RRS feed

  • Question

  • Dear Sir,

    As  we are migrating from SQL Server 2008 to SQL Server 2016. We found that in SQL Server 2016, Report Model is going to deprecate, thus we need guidance with following points - 

    1. We have a requirement to expose data so that end user can design their own reports. We need a solution that will work across SQL versions ranging from SQL 2008 standard edition to SQL 2016 standard edition.

    2. We have been using Report Models to expose data to end users who were then able to design their own reports using report builder.

    3. I am unable to do this on SQL 2016. Is there any way? Can I at least deploy existing Report Models in 2016 or even deployment is not possible.

    4. What is the way in which I can achieve similar functionality in SQL Server 2016. On reading over the internet I see Analysis Services Cubes and Tabular Models mentioned as alternates. What is the complete list of such alternates and what are their pros and cons. We need to build several types of reports ranging from summarized MIS reports to detailed transaction reports and Document Views.  Which flavor of SQL 2016 are they supported in? Specifically are they supported in Standard version. Are they also supported in SQL 2008? What is SSDT? What is Power BI? Can it be of use to us? If yes can you point me to resources/tutorials associated with these so that I can get started.  

    5. Can we migrate  old Reports already made on Report Model (.smdl) to sql server 2016 ? Are there  any Tools available for same.

    We are very confused and need guidance.



    • Edited by WFX Friday, August 19, 2016 9:29 AM
    Friday, August 19, 2016 9:25 AM

All replies

  • Hello,

    Uploading and managing report models is a discontinued feature on SQL Server 2016 (is no longer a deprecated feature) and the workaround is explained on the following article:

    https://msdn.microsoft.com/en-us/library/ms144231(v=sql.130).aspx

    You should try to update reports and remove dependencies on report models.

    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com

    Friday, August 19, 2016 1:04 PM
  • Dear Support Team,

    After reading this article we are not able to understand what is explained workaround ?

    Actually our concern is :

    Currently we are using Report Model of SQL Server 2008 R2. Now we are migrating to SQL Server 2016.

    We want to use these Report Model in SQL Server 2016. Please suggest how we can migrate these Model in 2016 in easiest way as these Report Model is deprecated in SQL Server 2016, what are the work around to migrate these Models.


    • Edited by WFX Thursday, August 25, 2016 9:26 AM
    Monday, August 22, 2016 5:46 AM
  • Hi WFX,

    As you see in the document, the report model is already discontinued feature in SQL 2016. There's no workaround to migrate old report models into SQL 2016. Report Model is not so flexible for nowadays requirement. Because for some simple model, you just need to open connection to relational data source and use the data with expressions. And for some complicated models, report model is not strong enough like Multidimensional or Tabular model. So in this scenario, I suggest you replace all report models with Tabular or Multidimensional models.

    Regards,


    Simon Hou
    TechNet Community Support


    Monday, August 22, 2016 11:28 AM
  • Dear Support,

    Please advice in what ways tabular models are more flexible than Report Model.

    Regards

    WFX

    Thursday, August 25, 2016 11:28 AM
  • Hi,

    We are still waiting for your response:

    Please advice in what ways tabular models are more flexible than Report Model.

    Regards

    WFX

    Wednesday, September 7, 2016 6:49 AM
  • Still no reply I see. 

    We use Report Model too, our users have written a lot of ad hoc reports and I'm sure they will not like being told they have to re-write their reports.  That's the whole purpose of self-serve reports, our IT team doesn't have to write the reports, the users do.  

    Their reports are not complex, they primarily use Report Model (RM) to pick and choose the fields they want to see from the data warehouse.  RM allows simple display of field values as-is in the data warehouse.  If I understand Multidimensional and Tabular modes, they will not be able to display just the values for a month of data, they'll have to pick some aggregation of such fields, which they may not want.  Can you display the column data in one of these replacement models for a month of sales, for example, without having to aggregate it, to just display the actual data without aggregation?  When I see demos for these two replacements, it always shows Excel PowerPivot examples, and it is always "by" month, year, sales region, etc, with the values summed, averaged or whatever. Our users just want the raw data, but with the relationships that RM gives them to pick and choose which data.  They do their own rollups if needed.

    KevHou

    Monday, February 12, 2018 3:12 PM
  • Yes, they would. You can design your SSAS model to the lowest level (aka grain). You can
    pretty much do similar operations however the complexity (or sometimes
    opportunity) comes with the language factor. You no longer use SQL for manual overrides
    but rather MDX or DAX. Simple reports entirely created by wizard should work
    the same, more complex ones when manual intervention is required may be more
    challenging due to the language factor.

    On that note you can see this as opportunity especially is someone wants to start using
    time series type functions.

    As for the automatic rollup all tools do it by default (e.g. Business Objects and
    Cognos). You chose year, they would roll it up to year, rather than repeating
    the values for each lowest level (e.g. day). Again you can override the
    behaviour.

    The best idea - just give it a try and see how it works.

    Hope this helps.



    Tuesday, June 26, 2018 9:23 AM