XLCubed is now FluenceXL. The new wiki can be found here: https://help.fluencexl.com/

Difference between revisions of "Managing Connections"

(Added relational db information)
(→‎Other Connections)
 
(16 intermediate revisions by 4 users not shown)
Line 1: Line 1:
−
The '''Manage Connections''' form is available from the {{Menu|XLCubed|Connections}} ribbon or menu item. It allows you to view, edit and delete existing connections for the workbook, and to [[Creating Connections|create new connections]] when required.
+
The '''Manage Connections''' form is available from the {{Menu|XLCubed|Connections}} ribbon item. It allows you to view, edit and delete existing connections for the workbook, and to create new connections when required.
  
−
One workbook can support multiple connections to different cubes or databases.
+
==Connecting to your data==
  
−
==Cube Connections==
+
One workbook can support multiple connections to different cubes or databases. Whenever XLCubed detects you require a connection to a cube or database, you will have the opportunity to select an existing one or to create one as described below.
  
−
*To make a connection select {{Menu|XLCubed|Connections}} and press ''Add''
+
To create a new connection open the Connection Manager from {{Menu|XLCubed|Connections}} and select ''Add''. Any connection you have made recently will be displayed for easy access.
  
−
[[Image:I5.png|20px|left]]
+
[[Image:I3.png|350px|centre]]
  
−
*The display shows any recent connections, which can quickly be re-used by selecting and pressing ''OK''
+
Alternatively, select the New Connection tab to create a new connection. Here, choose the server type and provide the appropriate detail.
−
+
 
−
[[Image:I3.png|350px|centre]]
+
Continue to add as many connections as needed.
 +
 
 +
===Cube Connections===
  
−
*For a new connection, select the New Connection tab, choose the server type and specify the appropriate detail as below:
+
For a new connection, select the New Connection tab, choose the server type and specify the appropriate detail as below:
  
 
:: ''Analysis Services Server:'' Type the server name
 
:: ''Analysis Services Server:'' Type the server name
Line 19: Line 21:
 
:: ''Analysis Services Cube file:'' Browse to locate the local cube file
 
:: ''Analysis Services Cube file:'' Browse to locate the local cube file
 
:: ''XLCubed Connection:'' Type the URL of the XLCubed Web Edition server to connect to  
 
:: ''XLCubed Connection:'' Type the URL of the XLCubed Web Edition server to connect to  
 +
 +
Once connected, select the required database and relevant cube.
  
 
[[Image:I4.png|350px|centre]]
 
[[Image:I4.png|350px|centre]]
−
 
−
*Once connected, select the required database on the left, then select relevant cube on the right to add the connection.
 
  
−
* Add additional connections to the workbook as required.
+
===Other Connections===
 +
 
 +
XLCubed supports a wide variety of datasources including relational databases, Big Data providers, and other platforms such as SAP HANA and Oracle Essbase.
 +
 
 +
These connections can be added in a similar way. Select the relevant connection type from the list on the right (the search box can be used for quick discovery).
 +
 
 +
Additional datasources supported in v9:
 +
* Microsoft SQL Server
 +
* Oracle Server
 +
* Google BigQuery
 +
* Amazon Redshift
 +
* PowerBI Cloud
 +
* PowerBI Desktop
 +
* HDInsight
 +
* Teradata
 +
* QlikSense Desktop
 +
* Vertica
  
−
==Database Connections==
+
When creating grids or tables on a connection, you may be prompted to first define the query.
  
−
*To make a new relational connection select {{Menu|XLCubed|Connections}}, switch to the {{Menu|Database Connections}} tab and select ''Add''
+
<gallery mode="packed" widths=300px>
−
*Select the server type from the first dropdown, then enter the server name in the next box (or select the file for Access connections)
+
File:ExcelRangeTableForm.PNG|Excel Range Query
−
*If necessary, enter the log in details, then click the ''Connect'' button
+
File:MLH2.png|SQL Query
−
*Select the database from the list
+
</gallery>
  
−
===Custom OLEDB and ODBC connections===
+
==Custom Properties==
 +
In the Manage Connections dialog, right click on a connection and you can set custom properties which will be applied in the connection string when the connection to the server is opened.
  
−
*Follow the instructions above, but select either {{Menu|Custom OLEDB Connection}} or ''Custom ODBC Connection'' from the first dropdown as appropriate
+
From here you can also set up the query timeout to prevent long running queries from causing Excel to hang. This is applied to all grid and formula queries which use the connection.
−
*Either type in a connection string or choose the Excel range which contains the connection string
 
−
*Click the ''Connect'' button
 
−
*Select the database from the list
 
  
−
==See Also==
+
[[File:ConnectionProperties.PNG|300px|center]]
−
* [[Creating Connections]]
 
  
 +
[[Category:Getting Started]]
 
[[Category:Connections]]
 
[[Category:Connections]]

Latest revision as of 09:15, 18 June 2019

The Manage Connections form is available from the XLCubed > Connections ribbon item. It allows you to view, edit and delete existing connections for the workbook, and to create new connections when required.

Connecting to your data

One workbook can support multiple connections to different cubes or databases. Whenever XLCubed detects you require a connection to a cube or database, you will have the opportunity to select an existing one or to create one as described below.

To create a new connection open the Connection Manager from XLCubed > Connections and select Add. Any connection you have made recently will be displayed for easy access.

I3.png

Alternatively, select the New Connection tab to create a new connection. Here, choose the server type and provide the appropriate detail.

Continue to add as many connections as needed.

Cube Connections

For a new connection, select the New Connection tab, choose the server type and specify the appropriate detail as below:

Analysis Services Server: Type the server name
Analysis Services Server 2000: Type the AS2000 server name
Analysis Services Cube file: Browse to locate the local cube file
XLCubed Connection: Type the URL of the XLCubed Web Edition server to connect to

Once connected, select the required database and relevant cube.

I4.png

Other Connections

XLCubed supports a wide variety of datasources including relational databases, Big Data providers, and other platforms such as SAP HANA and Oracle Essbase.

These connections can be added in a similar way. Select the relevant connection type from the list on the right (the search box can be used for quick discovery).

Additional datasources supported in v9:

  • Microsoft SQL Server
  • Oracle Server
  • Google BigQuery
  • Amazon Redshift
  • PowerBI Cloud
  • PowerBI Desktop
  • HDInsight
  • Teradata
  • QlikSense Desktop
  • Vertica

When creating grids or tables on a connection, you may be prompted to first define the query.

  • Excel Range Query

  • SQL Query

Custom Properties

In the Manage Connections dialog, right click on a connection and you can set custom properties which will be applied in the connection string when the connection to the server is opened.

From here you can also set up the query timeout to prevent long running queries from causing Excel to hang. This is applied to all grid and formula queries which use the connection.

ConnectionProperties.PNG
Retrieved from ‘https://help.xlcubed.com/index.php?title=Managing_Connections&oldid=9612’