Can we automate the BidsHelper- Sync Description functionality

Sep 29, 2014 at 5:34 PM
Edited Sep 30, 2014 at 2:35 PM
Hi Team

Do we have an option w.r.t Sync Description (through a command/ script) to copy Table/ Column descriptions from the Underlying Data Source (MSSQL DB- sys.extended_properties table) into Tabular Model DB without using the BIDSHelper- Sync Description functionality, which requires us to manually click on the options, which is a great option, but we may not be able to do it on a daily basis and would be useful if we could automate it some how through a script.

As an alternate approach I did try directly updating through AMO, but it does not seem to save the Dimension Attribute Description updated:
.......
$cube = $database.Cubes.GetByName("TabularModel")
$cube.Dimensions.GetByName('Headline').Attributes.Find('Type').Attribute.Description='TEST'
$cube.Update()
<this does not work - description not saved>



Thanks & Regards
Nilima
Coordinator
Sep 29, 2014 at 7:34 PM
I think automating this outside of SSDT-BI is out of scope for BIDS Helper. But of course since BIDS Helper is open source, you can use snippets of our code in a tool you build. Here's the relevant source code:
https://bidshelper.svn.codeplex.com/svn/SSAS/SyncDescriptionsPlugin.cs

In terms of your PowerShell script, try something like the following. I haven't tested, but off the top of my head that may work.

$database.Dimensions.GetByName('Headline').Attributes.GetByName('Type').Description='TEST'
$database.Update([Microsoft.AnalysisServices.UpdateOptions]"ExpandFull")
Coordinator
Sep 29, 2014 at 9:02 PM
I agree with furmangg that operating outside of SSDT-BI is not really in scope for BIDS Helper.

But I think the problem with your script is that you are changing properties on the dimension, then calling the update method on the cube. The Update method acts on the object itself as well as any contained minor objects, Dimensions are major objects so you either need to update them directly or possibly use furmangg's suggestion of updating at the database level with the "ExpandFull" option.

I have not tested this, but I think something like the following where we are calling the Update() method on the dimension should work.

$cube = $database.Cubes.GetByName("TabularModel")
$dim = $cube.Dimensions.GetByName('Headline')
$dim.Attributes.Find('Type').Attribute.Description='TEST'
$dim.Update()
Coordinator
Sep 29, 2014 at 9:36 PM
Seeing as this is Tabular, I believe the only supported way (AS2012 and AS2014) to update the server is to update the whole database:
$database.Update([Microsoft.AnalysisServices.UpdateOptions]"ExpandFull")

Do you agree, Darren?
Coordinator
Sep 29, 2014 at 10:55 PM
Yep, now that you mention it I believe that is correct. For tabular models Microsoft only support doing Database.Update() with the expand full option. It' only multi-dim models where you can call Update on other major objects.
Sep 30, 2014 at 2:34 PM
Thank you so much furmangg and dgossbell!! This is very helpful.
I tried with the ExpandFull option and it worked perfectly!

(FYI- I was able to update Cube and Dimension Descriptions through cube.Update() then, but attribute description update wasn't working. After implementing your solution everything works as expected. Thanks Again!)