When a file is reused, saved and shared, stored in a cloud service like Dropbox, SharePoint, or OneDrive, or if cube connections are pasted or copied, the file may become stale or unusable. Oftentimes a file can become usable after fixing its cube connections and queries.
This articles contains the following topics:
Description
What are Queries and Connections in Excel?
The connection is just that. It connects your workbook to a data source. Like a highway connecting two cities. A query is the request for actual data that you spell out, calling from your workbook (via the connection) into the data source. The data source then sends the data back (via the connection). The mechanics of asking for, receiving, and manipulating the received data (for e.g. cleaning it up, storing it in the workbook) is what the query does, but it can't do this without the connection. The query is the actual traffic on the highway.
EBM connections
How do these apply to Catalyst and EBM?
- All EBM-connected cube files connect to the EBM Data Lake using MDX or Tabular cubes. Each data type has its own cube, such as Financial data, Profitability data, Journal Entry data, Inventory data, etc. By default, the two primary data types are Financial and Profitability.
- When a user downloads a cube file, you can see which type of data it's referencing by toggling to the Queries & Connections panel under the Data tab in Excel.
- You can then right click into these connections to understand where they're referenced, their properties, and their locations.
Best practices
Ideally, you wouldn't need to configure or reconfigure these connections, but some use-cases below provide some best practices.
- Don't rename these connections. If you do, they'll stop working. Instead, feel free to edit the description by right-clicking the connection and selecting properties. All connection names should follow the following pattern: "EBM_Financial", "EBM_Profitability", etc.
- Limit your number of connections. Ideally and most commonly you will only need one connection per data type. But occasionally you may find that you need more than one of the same type (e.g. EBM_Financial1; EBM_Financial2).
- You can limit the number of connections by deleting the extraneous connections and then running your file through the cube updater to restore the primary connection to the dataset.
- To understand where you cube connection is being used, right-click the connection and select Properties > Used In.
External links
External links can be found in the same section under Data > Queries and Connections, and often impact the usability of a file, depending on what those links connect to.
In some files you may find it necessary to link to other files, tabs, or websites. Know that this may result in a broken file over time as these links and connections become stale and especially if they reference old files that may have changed locations or that have been removed. We recommend limiting the number of external links to a minimum.