Although you can publish your Target tables directly to Power BI Service or through a SharePoint Folder (for example) in the cloud, your company might want this data to be made available to Power BI through either an internal SQL Server instance or through an Azure database in the Cloud. XLPublish can accommodate this.
If you click on the DB Server link here:
You get this dialog:
In this dialog you can add the name of your Target Server (like xxxxxxx.database.windows.net for an Azure Server) and then you MUS type in the Driver you will be using on this machine(which is probably consistent across your Corporate environment).
In our XLPublish machines we use SQL Server Native Client 11.0, therefore the Driver name is SQLNCLI11… This name is available if you create a native Database Connection to a SQL Server in Excel and in the Connection Properties you should see which Driver you are using.
Frankly, if you are going to use Azure or SQL Server your DBA’s will be able to give you this information… or you can reach out to us of course and we can walk you through it.
If your company has implemented AD or AAD (Azure Active Directory) as the protocol for connecting to your Servers then your Publishing to Azure or SQL will be managed automatically based on your personal Membership in AD Groups. In this case the Login MUST be entered as “AD”. You can enter “NA” as a password or leave it blank as the Password is inherent based on your Windows login anyway. Keep in mind that anyone who is going to publish to this Server for XLPublish will need Full Owner rights to allow them to create, modify or delete databases, tables, fields in the Server.
Alternatively if for your XLPublish Target databases you have decided to use a SQL login, you enter that login and password accordingly here and go “Connect”. This login has to be an Owner of that Server so it can perform any process against the Server.
In this way XLPublish does all the work for you creating Databases, Tables and fields as you Published data dictates. Therefore we STRONGLY advise that your XLPublish implementation should have its OWN Server assigned.
Note that in Azure you do not need a very big or capable Server to host just your XLPublish data and these basic servers cost pennies (almost literally) per month to use. Data there will simply be imported as a whole into the Power BI Server as appropriate so it is our belief that Indexes, etc. will be unnecessary. In fact, any indexes you create there will get deleted automatically any time you change the structure of any Table or database anyway.
- When you publish a new Source file to a new Azure or SQL Server the first time, XLPublish will create a new Database there using parts of the name of the Target Folder.
- Then for each Target File and Table within that file, XLPublish creates a Table in that database with a Concatenated name like “filename_Tablename” with extraneous characters (like “.xlsx”) removed.
- Then for each field in that table XLPublish creates a new field simply using the name of the Columns/Fields in the Table and XLPublish will already decided based on the data you put in each field whether it will be a string, value ,date, or Boolean fieldtype.
- Because XLPublish does not differentiate between any numeric field and a Currency field we suggest you consider adding your Currency symbol to the column/field names in your Source file (I.e. Budget$). You can then format the final values in your Power BI Desktop implementation (if you are going to use Power BI Service alone then we suggest you make sure that your values are rounded to the level that you wish to display in your Source file before Publishing).
If you use this option in your Source File you will see the process commenced and “Complete” when done. If the Publish to the Server fails (for whatever reason) it will say “Failed” in the Publish Dialog. At that time you should check the name of your Server, the Driver, the Login details and then try again. Remember that by default XLPublish replaces the data in the Target database anyway. We suggest that you use a Snapshot during your dev process and THEN add the “Continuous” option AFTER all is working as expected.