Welcome to the Qlik Community and thanks for your question.
Which Data Connection are you using - I assume OLE / DB - as there is a driver provided by MS for this:
If that is the case - I believe the proper way to do this would be to use the data load editor instead of the wizard and issue the appropriate MDX to pull back the columns and data. It is not ideal in this situation, but we usually recommend going after the source tables in MS SQL Server (if available) instead of the MS OLAP cubes.
let me know your thoughts and how you do.
When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.
If you download and install the August 2016 release of SQL Server Data Tools (SSDT) for Visual Studio 2015, you can find a new feature in SSAS Tabular projects, called Tabular Model Explorer, which lets you conveniently navigate through the various metadata objects in a model, such as data sources, tables, measures, and relationships. It is implemented as a separate tools window that you can display by opening the View menu in Visual Studio, pointing to Other Windows, and then clicking Tabular Model Explorer. The Tabular Model Explorer appears by default in the Solution Explorer area on a separate tab, as illustrated in the following screenshot.
As you no doubt will notice, Tabular Model Explorer organizes the metadata objects in a tree structure that closely resembles the schema of a tabular 1200 model. Data Sources, Perspectives, Relationships, Roles, Tables, and Translations correspond to top-level schema objects. But there are also exceptions, specifically for KPIs and Measures, which technically aren’t top-level objects yet child objects of the various tables in the model. However, having consolidated top-level containers for all KPIs and Measures makes it easier to work with these objects, especially if your model includes a very large number of tables. Of course, the measures are also listed under their corresponding parent tables, so that you have a clear view of the actual parent-child relationships. And if you select a measure in the top-level Measures container, the same measure is also selected in the child collection under its table—and vice versa. Boldface font calls out the selected object, as the following side-by-side screenshots illustrate for selecting a measure at the top level (left) versus the table level (right).
As you would expect, the various nodes in Tabular Model Explorer are linked to appropriate menu options that until now were hiding under the Model, Table, and Column menus in Visual Studio. It no doubt is easier to edit a data source by right-clicking on its object in Tabular Model Explorer and clicking Edit Data Source versus opening the Model menu, clicking on Existing Connections, and then selecting the desired connection in the Existing Connections dialog box, and then clicking Edit. This is great, even though not all treeview nodes have a context menu yet. Namely the top-level KPIs and Measures containers don’t yet have a menu while the Perspectives container does but its child objects do not. We will add further options in subsequent releases, including completely new commands that now make perfect sense in the context of an individual metadata object.
The same can be said for the Properties window. If you select a table, column, or measure in Tabular Model Explorer, SSDT populates the Properties window accordingly, but if you select a data source, relationship, or partition, SSDT does not and leaves the Properties window empty, as shown in the next screenshot comparison. This is simply because SSDT never had to populate the Properties window for the latter types of metadata objects before. Subsequent SSDT releases will provide more consistency and enable even more convenient editing scenarios through the Properties window. We just did not want to wait another one or two months with the initial Tabular Model Explorer release.
The initial version already goes beyond what was previously possible in SSDT Tabular. For example, assume you have a very large number of measures in a model. Navigating through these measures in the Measure Grid can be tedious, yet Tabular Model Explorer offers a convenient search feature. Just type in a portion of the name in the Search box and Tabular Model Explorer narrows down the treeview to the matches. Then select the measure object and SSDT also selects the measure in the Measure Grid for editing. It’s a start to say good bye to Measure Grid frustration!
But wait, there is more! The Tables node and the Columns and Measures nodes under each table support sorting. The default is Alpha Sort, which lists the object alphabetically for easy navigation, but if you’d rather list the objects based on their actual order in the data model, just right-click the parent node and select Model Sort. In most cases, Alpha Sort is going to be more useful, but if you need Model Sort on other parent nodes as well, such as Hierarchies and Partitions, let us know and we’ll add it in a subsequent release.
Note also that the Tabular Model Explorer is only available for the tabular 1200 compatibility level or later. Models at compact level 1100 or 1103 are not supported because Tabular Model Explorer is based on the new Tabular Object Model (TOM).
And that’s about it for a whirlwind introduction of Tabular Model Explorer in SSDT Tabular. We hope you find this new feature useful, especially if your models are complex and contain a very large number of tables, columns, partitions, measures, and other metadata objects. Give it a try, send us your feedback through Microsoft Connect, community forums, or as comments to this blog post, and let us know what other capabilities you would like us to add. Import/export of selected objects? Drag and drop support? And stay tuned for even more capabilities coming to an SSDT Tabular workstation near you in the next monthly releases!