Can I create a view that looks at multiple Schemas but doesn't select distinct table names?

Charlie Simmons shared this problem 1 year ago
Resolved

Hi,


I would like to create a view that looks at multiple schemas and will give all tables, regardless of name. For example, we have two Dim_Customer tables on different schemas but only one is being returned when I create a SQL server non-schema specific data source.

Short of moving the joining of tables to the reports is there another way to join Schemas?

Or, if that's not possible, is there a way to turn on Schema and table prefixes in the view builder?


Thanks,


Charlie

Comments (6)

photo
1

Hi Charlie,


This functionality should work as you expect it to. What type of database are you employing here?


Regards,

Nathan

photo
1

It's nearly Kimball, due to our system we couldn't get around multiple dimensions of the same name on different schemas. When I create a view with all tables in I'm only seeing one Dim_Customer and it's not the one I actually want. We're in the process of decommissioning the secondary tables but is there something I can do in the mean time?


Thanks,


Charlie

photo
1

Hi Charlie,

I have been able to confirm what you are noticing and have raised a defect ticket to address this. (5898)

In the meantime I think that the only work around will be create which ever table is not showing up as a virtual table in which you specify the schema name in your select statement. However it is worth noting that any content created in your work around will be difficult to transfer once you are using a more sustainable solution, and that virtual tables have inherently worse performance than normal tables.

defe7d4a9c7b8f6a6ef29ff520e29448

I apologize for the inconvenience and please let me know if there is anything I can do to help.

Regards,

Nathan

photo
1

Hi Charlie,

This issue has been fixed and is available in the newest 7.4 and 7.3 builds.

Nathan

photo
1

Dear Nathan,

How about different MS SQL Server databases (e.g. take a look into this forum thread)? The thing is that using Virtual Table it is possible to make a query into another database, but I can not see those tables in the list on the left side in the View.


Thanks in advance!

R.

photo
1

Hi Raimondas,

Data source connections should be specific to the database specified on creation. Virtual tables are a bit of an exception to every rule because they are intended to be very open form, and simply run the provided statement against your datasource and return the result set.

Regards,

Nathan