4 Replies Latest reply: May 26, 2016 1:45 AM by Martin Jelinek RSS

    Change data source

    Martin Jelinek

      Hello,

      is the any possibility how can I change data source from Excel to ODBC?

       

      I'm just working on PoC and currently I have all data in Excel, but now we want to continue with data from SQL database and I do not see any possibility how I can do that.

       

      Thank you for help.

        • Re: Change data source
          Theo Klemming

          Hi Martin

           

          Yes you can.

          In brief, the procedure consist of adding a second data source to the data table, and delete the first data source after.

           

          You currently have a Data Table.

          The Data Table contains (presumably) 1 Data Source.

          The Data Source is using the Excel connector.

          To change the Data Table so that it gets data from a database via an ODBC DSN, do this:

           

          Add a second Data Source in your Data Table. Use the Database connector. Use the OLEDB Provider for ODBC Drivers. Select your ODBC DSN, etc.

          Make sure your new, second data source is delivering a data set with the same column names, column order and data types as the first data source.

          When you have verified that, you DELETE the first data source. This will make the second data source the only one in the Data Table, and the data delivered by the second data source will be the content of the Data Table.

           

          Your dashboards and visualizations will not know the difference.

           

          best regards, Theo Klemming

            • Re: Change data source
              Martin Jelinek

              Hi Theo,

              thank you for this information. Very helpful.

               

              But when I have a different column names I cannot do that and I must recreate whole dashboard. Am I right?

               

              Best regards,

              Martin Jelinek

                • Re: Change data source
                  Theo Klemming

                  Hi

                   

                  The visualizations on the dashboard work like this: if a column used in a visualization cannot be found, the column is ignored in the visualization. The columns are looked up by name. If your ODBC source has column names different from the Excel source, and you have built stuff on the Excel source, your best option would be to use a SQL statement with your ODBC source where you give each column a name alias (so SELECT colXYZ AS colABC) that matches the column names you had in Excel.

                   

                  Or, you can rename the columns after they have been loading into Designer, by right-clicking a column and selecting "Change Title".

                   

                  If there is a column name difference on just a few columns, you can "repair" the dashboards by removing the non-existant column from where it is used, and instead add the one with the new name.

                   

                  // Theo