Attribute Category Views not in BBDW

Jennifer Borri

We have several attribute categories marked to “Include in Warehouse” through the application. These attributes are being populated in FACT and DIM tables in BBDW, but we're missing BBDW views for most of them (BBDW.V_QUERY_BBDW.ATTRIBUTE[GUID}). This means that we cannot use the BBDW ad hoc queries in the application to query on these attributes...we get an invalid object error looking for the view.

Has anyone experienced this problem and found a resolution? BB Support has no answers yet. Thanks!

Mitchell Gibbs

I'm not seeing them either. Our users don't use the DW ad hoc query nodes actively, but we're expecting they will in the future, so this is one we're going to want to get a handle on.

The query views do show up in the QUERYVIEWCATALOG table and in the UI, but don't get created in the actual BBDW. They also don't show up in the Catalog Browser, which I'd expect them to.

Jeff Bauer

While waiting for BB to fix this standard functionality you could always create a custom query view spec to do it yourself. Here's an example from our BBDW. It uses a combination of standard BBDW tables and custom fields from our own extensions to some of those tables but should be modifiable to your environment: FSU Constituent.xml

Jennifer Borri

Thank you, Jeff!

Jennifer Borri

Thanks, Mitchell. If you open a BB support case on this too…we can get more attention. We have had some luck with redeploying the BBDW in our Dev environment..then changing the “Include in Warehouse” flags back and forth between refreshes. Some of the views were created when we did this (including ones whose flags we didn't toggle). Some were not. We are not sure why this partially worked. Previous to our testing for this issue, we redeployed the BBDW in all environments in April after our last upgrade and 2 out of 3 environments had 0 BBDW attribute views. The third only had a small percentage of expected views.

Mitchell Gibbs

That is interesting. We've got 13 separate BBDW warehouses in three different environments and none of them have the missing views. For Prod, Staging, and QA - everything is on separate servers. For my development environment, I've got everything on a single machine and it is exhibiting the same behavior. We've got a fairly strong automated deployment process and regularly rebuild some of the non-production environments. I regularly blow away and redeploy development and don't have the views.

It looks like this same issue impacts Smart Fields.

I'm asking one of our folks to document it with Blackbaud to push the issue. Waiting on Blackbaud for fixes like this can lead to very long waits - between their release calendar and our testing and waiting for others to discover issues before we adopt a release. So, we're also looking to see if this is something we can work around using loadspec or something (no joy with a few simple attempts).

In the short term, I suspect Jeff's approach is the most efficient.

Jennifer Borri

Thanks Mitchell! Good to know about Smart Fields...we haven't had a business use case for this yet in BBDW queries....but I'm sure we will. Thanks for documenting the issues and bringing up to BB Support. Strength in numbers!

Mitchell Gibbs

It appears we've found the issue for our situation. We have a set of attributes from conversion with a Category Description of “Raiser's Edge History”. That single quote was causing some dynamic SQL associated with the business process to fail. Removing the single quote fixed it and we now have Views for all of the Attributes and Smart fields.

I'd still categorize this as a bug but, at least for us, the workaround is simple enough. Is anything like this in your environment?

Jennifer Borri

This is great info, Mitchell! I'm glad you found your issue! We don't have any quotation marks in attribute category descriptions...but we do have several with dashes and one with a question mark that we'll look into just in case. Thanks!

Mitchell Gibbs

We also figured out that we needed to update the SQL agent jobs that were running the ETLs. We were just kicking off the bbetl.bat from the SQL Agent steps and completely missing the business processes around the ETLs, like creating Views. When we fixed the single quote issue and shifted to using scripts based on Blackbaud's generated powershell scripts when you schedule a business process, everything seems to be working as intended. Best of luck!

Jennifer Borri

Thank you! Great info, Mitchell! Do you have an example of the Dynamic SQL getting generated that allowed you to discover the quotation mark issue?