1
Vote

Sync descriptions (with Views) working?

description

I'm unable to sync descriptions with views with BIDS 1.5.0 or Bids 1.6.0. I found an earlier issue (#24166) that was closed indicating that it would be fixed with the release after 1.4.2.1. I'm getting the same "Set 0 descriptions successfully" message as mentioned in the prior issue.
 
Any assistance would be appreciated and thanks for the great helper app!

file attachments

comments

furmangg wrote Mar 28, 2012 at 2:44 AM

What version of SQL Server are you using? The change we checked in two years ago was to look at sys.all_objects instead of sys.tables. Can you run the following query against your SQL database and see what it returns and what changes you need to make to the query for it to return the data you're expecting?

SELECT PropertyName = p.name
,PropertyValue = CAST(p.value AS sql_variant)
FROM sys.all_objects AS tbl
INNER JOIN sys.schemas sch ON sch.schema_id = tbl.schema_id
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
INNER JOIN sys.extended_properties AS p ON p.major_id=clmns.object_id AND p.minor_id=clmns.column_id AND p.class=1
where sch.name = 'YourSchemaHere'
and tbl.name = 'YourTableNameHere'
and clmns.name = 'YourColumnNameHere'
order by p.name

k2spam wrote Mar 28, 2012 at 10:56 AM

I'm running 2008 R2. A little background. In my case, I've built my database using the database modeling spreadsheet (attached) developed by the Kimball Group and associated with their book "The Microsoft Data Warehouse Toolkit-2nd Edition". I've used the spreadsheet to design and build my database via the sql script generated by the spreadsheet.

I ran the query on one of my tables and it returned the following values that I was expecting for the schema (dbo), table and column chosen (see the script generated by the spreadsheet): Description, Display Name, Example Values, and Source System.

What I'm trying to do with BIDS is sync the column descriptions I entered into the spreadsheet to SSAS, as recommended in the above book on page 265.

Thanks for the help!

furmangg wrote Mar 29, 2012 at 1:51 AM

I use the same Kimball spreadsheet on some projects.

When you ran the sync descriptions feature, did you check the "overwrite existing descriptions" checkbox?

On the dimension attribute in question, does it have a NameColumn specified, and is the extended property on the column used in the NameColumn?

Can you run Profiler and connect to SQL Server and capture which SQL queries BIDS Helper is running. Maybe that will help you troubleshoot what's going wrong.

The only other suggestion I have is for you to click on furmangg and contact me offline and send me the source code for your cube so I can see if there's something slightly different with your DSV setup or NameColumn bindings in your SSAS project.

k2spam wrote Mar 29, 2012 at 11:17 AM

I think I may be getting closer to figuring out the problem. In the Kimball spreadsheet, I did not enter a database schema name for each table (i.e., am defaulting to the dbo schema) and on the Home tab I used "VIEWS" as the name for the "Schema for Views" value. The generated SQL script shows that dbo and VIEWS were properly used to build the database. Yet, when running the query shown in your earlier response, I get values returned when I use dbo as the schema name but nothing when I use VIEWS as the schema name. And I see from running the profiler that BIDS queries are using VIEWS as the schema (as they should) and that is why nothing is being returned.

I'm trying to use the BIDS sync descriptions feature as I'm creating and editing my dimensions in SSAS. All tables in the one data source view I'm using are showing the following properties: Schema = VIEWS and TableType = View.

Any ideas? Am I doing something wrong?

Thanks again for taking the time to help me with this.

furmangg wrote Mar 31, 2012 at 1:53 AM

This must be a newer version of the Kimball spreadsheet than I have used. It appears it creates the table in schema A, creates the extended properties on the table, then creates the views on schema B.

I'm afraid for BIDS Helper to work, you're giong to need to have the extended properties live on the view, not on the table (or at least, in addition to on the table).

You can probably just edit the macro in the Kimball spreadsheet to customize it to your needs.

leadfoot666 wrote Feb 12 at 7:48 PM

I tried putting the extended properties on top of the views that are used in my DSV and I still get the "Set 0 descriptions succesfully" error regardless of whether or not I select the "overwrite" checkbox.

furmangg wrote Feb 13 at 2:27 AM

leadfoot666, sorry you're still running into problems. Can you run the SQL queries in the comments of this thread and tell me what they return. If they don't return anything, then that may help you figure out why your extended properties aren't being picked up.

What version of BIDS Helper are you using? Go to Help... Microsoft Visual Studio... then scroll down until you see BIDS Helper and tell us what version you're using.

leadfoot666 wrote Feb 13 at 3:00 PM

Hi,

The query returns nothing. The reason it returns nothing is on the final join, the sys.extended_properties.minor_id column is not populated correctly (should contain the number 2 for the join to work, but contains the number 0)

I'm using BIDSHelper 1.6.1.0 and SQL Server 2012 SP1

furmangg wrote Feb 13 at 6:03 PM

leadfoot666, then is your extended property specified on the view or on a column in the view?

When you run the Sync Descriptions, does it end up successfully populating the description on the dimension from the extended properties? This query is used to figure out the description on the dimension itself:

SELECT PropertyName = p.name
PropertyValue = CAST(p.value AS sql_variant)
FROM sys.all_objects AS tbl
INNER JOIN sys.schemas sch ON sch.schema_id = tbl.schema_id
INNER JOIN sys.extended_properties AS p ON p.major_id=tbl.object_id AND p.minor_id=0 AND p.class=1
where sch.name = 'YourSchemaName'
and tbl.name = 'YourTableOrViewName'

Notice minor_id = 0

That other query picked up the extended properties on columns.

leadfoot666 wrote Feb 14 at 4:37 PM

I have extended properties on the view itself, the view columns, and the underlying tables and table columns. It isn't picking up any of them.

Sign in to add a comment or to set email notifications