Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Dataset SQL Question

In the very near future, I will be migrating away from flat-file supported dashboards, to connecting PowerBI to a data store in SQL.  We expect this will eliminate much oif the manual file movement a...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    9 years ago

    Anonymous,

    When you connect to SQL Server database in Power BI Desktop, any time you make schema changes(for example, add new columns), you can click “Refresh” button in Power BI Desktop to bring the changes.

    And after you publish PBIX file to Power BI Service, you can set schedule refresh for your dataset, this way, when data are changed in SQL Server database, the dataset will be updated automatically based on  the schedule. However, please note that refresh in Power BI Service is purely data refresh, it will not load your schema changes. In other words, after you publish PBIX file to Power BI Service, if you make schema changes(for example, add new columns) in the SQL database, you will need to firstly refresh the dataset in Power BI Desktop, then re-publish the PBIX file to Service.

    Regards,
    Lydia

  • Anonymous's avatar
    Anonymous
    9 years ago

    SQL wont have that issue.  Column names are the important factor there.

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Anonymous,

When you connect to SQL Server database in Power BI Desktop, any time you make schema changes(for example, add new columns), you can click “Refresh” button in Power BI Desktop to bring the changes.

And after you publish PBIX file to Power BI Service, you can set schedule refresh for your dataset, this way, when data are changed in SQL Server database, the dataset will be updated automatically based on  the schedule. However, please note that refresh in Power BI Service is purely data refresh, it will not load your schema changes. In other words, after you publish PBIX file to Power BI Service, if you make schema changes(for example, add new columns) in the SQL database, you will need to firstly refresh the dataset in Power BI Desktop, then re-publish the PBIX file to Service.

Regards,
Lydia

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Anonymous

 

Thanks for your feedback.

 

To draw a contrast however, when using an Excel or .csv hitting "Refresh" in desktop does not allow the new data to be recognized. A user has to account for the additional columns in "Query".

 

Is the distinction solely based on the source type, and could it really be as simple as your feedback explains when using SQL?

 

Thanks.

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Anonymous,

    I am not able to reproduce your issue. After I add rows or new columns in Excel file/csv, newly added rows/columns are updated in Power BI Desktop after I click “Refresh” button. Could you please describe more details that how you operate in Power BI? And what error message do you get?

    In addition, the process that I describe in my first reply also applies to the Excel/CSV data source.

    Regards,
    Lydia

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Anonymous

      This will largely depend on how you structure your data and what you are relying upon.  There are a number of methods of bringing data in from sources, including excel, that help handle your data in a flexible manner.  It might be best to illustrate to us the exact issue you are having and we might be able to provide advice on how to resolve it.

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Thanks all for being so engaged on this topic.

         

        There have been instances that when using a .csv file and expanding the dataset over time, I needed to go to "Edit Queries", Source and Change the Column count from 25 to 26 so that PowerBI will recognize the new column(s).  Columns=25

         

        Simply "Refreshing" the data through Desktop does not allow the new column to be recognized.

         

        I am asking if when I start using SQL will the expanded dataset (new columns) be automatically recognized?

         

        Thanks.