Biml, Microsoft Technologies, SSIS

Biml for a Task Factory Dynamics CRM Source Component

I recently worked on a project where a client wanted to use Biml to create SSIS packages to stage data from Dynamics 365 CRM. My first attempt using a script component had an error, which I think is related to a bug in the Biml engine with how it currently generates script components, so I had to find a different way to accomplish my goal. (If you have run into this issue with Biml, please comment so I know it’s not just me! I have yet to get Varigence to confirm it.) This client owned the Pragmatic Works Task Factory, so we used the Dynamics CRM source to retrieve data.

I was ultimately creating a bunch of packages that looked like the below.

There are two pieces to using the Task Factory Dynamics CRM Source: the source component and the connection manager. The code for both is below.

Things to note for the source component:

  •  This is pulling data for an entity in Dynamics 365. If you were to populate this by hand, the Task Factory UI would ask you which entity. There are several entities that are visible, but do not allow you to retrieve the data. You can find a list of internal entities here.  You won’t want to stage data from those tables. I generated all of my packages at once with Biml, using a Blacklist to exclude the internal entities.
  • The entity name that should be the value on line 21 is the entity logical name.
  • I’m iterating through columns in tables in a separate file and holding them in memory, hence the references to Table.columns. You’ll need to write some code to get the entity metadata to feed that, or find another way to provide the table names and column names and data types.
  • You must set the ErrorRowDisposition and TruncateRowDisposition to “NotUsed” as I did on line 10 for each column or your package will not work.
  • In OutputColumns and ExternalColumns collections, I just have if statements to check data types. There may be a more elegant way to do this, but this worked for me and I went with it. Feel free to leave suggestions in the comments if you have a better idea.
  • The Connection element on line 145 should keep the name “DYNAMICSCONNECTION”. If you change it, the connection won’t work. In my actual project, my connection manager is in a separate file and is named “TF_DynamicsCRM”. You can set the ConnectionName property to whatever you want, as long as it matches your connection definition (Line 153 in the gist).

Things to note for the Connection Manager:

  • I tried reverse engineering the connection manager in Biml Studio, and that got me close, but it didn’t get the ObjectData property quite right. I ended up having to create one manually, view the code for it in SSDT, and copy it into my Biml file. I have my packages and project set to EncryptSensitiveWithPassword, which is why you see the p4:Salt value on line 158. Your connection manager will have different values for lines 158, 159, and 160. If you set your project to EncryptSensitiveWithPassword, this value will stay consistent across developers. But if you use EncryptSensitiveWithUserKey, the value may change, which will be fun for regenerating with Biml. So make sure you plan for that if you have multiple developers or multiple computers on which you are working.
  • This connection manager is set to connect to Dynamics 365, hence the ServerHost=disco.crm.dynamics.com. If you have an on-prem or hosted environment that isn’t 365, you’ll have to find the correct ServerHost value and put it in line 165.

I hope that helps someone looking to generate the Task Factory Dynamics Source with Biml.

Conferences, Data Visualization, Microsoft Technologies, Power BI

Data Visualization Panel at PASS Summit

Next week is PASS Summit 2017, and I’m excited to be a part of it. One of the sessions in which I’m participating is a panel discussion on data visualizationMico Yuk will be our facilitator. I’m in great company as the other panelists are Ginger Grant, Paul Turley, and Chris Webb. This session will be on Wednesday (November 1) from 4:45pm – 6:00pm.

We’ll be taking questions on Slack in the #visualization of sqlcommunity.slack.com. So if you need advice or have been curious about some aspect of data viz, join us in room 2AB and send us your question via Slack.

If you are curious about my views on data viz, I wrote a sort of beginner’s guide for data viz in Power BI in the book Let Her Finish: Voices from the Data Platform (Volume 1).

I hope to see you at PASS Summit!

Books, Conferences, Microsoft Technologies, Power BI

Let Her Finish: Voices from the Data Platform

This year I had the pleasure of contributing a chapter to a book along with some very special and talented people. That book has now been released and is available on Amazon!  Both a digital and print version are available. My chapter is about data viz in Power BI, combining platform agnostic concepts with practical applications in Power BI.

The other chapters are:

  • Azure Data Catalog by Melody Zacharias (b|t)
  • Biml for Beginners: Script and Automate SQL Server Integration Services (SSIS) Development by Cathrine Wilhelmsen (b|t)
  • Care and Feeding of a SQL Server by Jen McCown (b|t)
  • Indexing for Beginners by Kathi Kellenberger (b|t)
  • Creating a Disaster Recovery Plan by Rie Irish (b|t)
  • Using Extended Events to figure out why an application is slow by Mindy Curnutt (b|t)

Special thanks to Rie and Melody for putting this together and for being awesome and inspiring.

We may all be women authors, but this book isn’t about WIT issues. We each got to write about an area of the data platform in which we have expertise.

If you’d like to support us, or are just curious what we have to say, you can pick up a copy on Amazon or purchase a copy at PASS Summit. If you are going to be at PASS Summit, you can also stop by the WIT Happy Hour/book release party on Oct 31 or the panel session on Nov 2.

For more information, check out the website (kindly provided by SentryOne, who sponsored the book).

It’s tough to write a book chapter about a technology that changes every month, but I think the content holds up fairly well a few months after writing it. A few things changed on me (e.g., the Office Store for custom visuals is now App Source, and drillthrough actions exist now), but my approach to data viz in Power BI is still relevant and in use today with several clients. I hope you’ll give it a read and let me know what you think.

 

Microsoft Technologies, Power BI, SSAS

The Tabular Model Documenter is now a Power BI Template

A while back I created the Tabular Model Documenter Power BI model that can connect to your SSAS Tabular or Power BI model and display metadata about the model to help you see relationships, calculations, source queries, and more.  I had been meaning to turn it into a parameterized template since templates became available and just finally got around to it.

You can now download the PBIT file here. Note: This works for SSAS 2016/compatibility level 1200, but may need some adjustments for Azure AS and SSAS 2017.

When you open it, you’ll need to fill in the instance and database name, then allow some native queries to run. Then you will have your tabular model documented for you without a lot of manual effort.

tabular-doc-relationships

For more info on how to use the Tabular Model Documenter, see the original post.

Microsoft Technologies, Power BI

My Thoughts After Completing a Power BI Report Server POC

Last month I worked on a proof of concept testing Power BI Report Server for self-service BI. The client determined Power BI Report Server would work for them and considered the POC to be successful.

Here are the highlights and lessons learned during the project, in which we used the June 2017 version of Power BI Report Server.

Power BI Desktop

There is a separate version of Power BI Desktop for use with Power BI Report Server. You can tell that you have the correct version by checking that the month and year are shown in the title bar.

If you are using both PowerBI.com and Power BI Report Server, this means you will have to manage two versions of PBI Desktop.

Another confusing aspect is that the June release of Power BI Report Server only allows a live connection to SSAS, but the Get Data Button is still there in Desktop with all the data sources listed. If you happened to get data from a non-SSAS source, you might have made it through the development of the model and report. Only when you tried to save the report to the report server would you find out that you had used incompatible features. This is only a temporary issue and will be resolved in the next release, since it will allow for Power BI models that connect to a wide array of sources.

Another difference between the Cloud and Report Server versions of PBI Desktop is that you do not publish to Power BI Report Server. You Save As. The Publish button is gone from the ribbon but still present in the File menu. But what you want is Save As – Power BI Report Server.

PBIRSDesktopSaveAs

Limitation of Live SSAS Connection Data Source

In addition to managing to versions of Power BI Desktop, I also found myself mentally managing two sets of features. I was constantly asking myself “Can I do that in Power BI Report Server?”. Some of that is because PBI Desktop for Report Server is on a quarterly release cycle rather than monthly, so I had to remember if a feature I wanted to use was new (or in preview) and therefore not available in this version. The other part is trying to remember what you can and cannot do with a Live Connection. For example, you can make report measures, but you can’t use ad hoc grouping and binning.

We had several scenarios where users wanted to be able to group fields in multiple ways that changed somewhat frequently. Since we couldn’t use grouping and binning in Power BI Desktop to accomplish this, we set up an Excel data source in the SSAS Tabular model, and allowed users to change the groups there and refresh the Tabular model when finished. This could get rather unwieldy if you had lots of users who needed this kind of flexibility.

While there are downsides like the above with using a centralized data source, there are some upsides as well. Having a centralized data source can help ensure calculations are tested and approved before being used in a report. It also allows the BI team to ensure that security is properly implemented in the model. This is reassuring for organizations that are just starting their self-service BI program. But it also places more burden on the BI team since they must be very responsive with new calculations and data sets. It’s also necessary for the BI team to publish a data dictionary and tabular model documentation (relationships, calculations) up front so users can understand how to use the data in the tabular model. Our users were excited to get started and immediately had questions as to how calculations were implemented.

Content Management and Sharing

When we were ready to migrate to production, we wanted to avoid moving content by hand. The RS.Exe utility ignores pbix files (I tried it to see what would happen). But works fine on any SSRS reports you want to move.

We had to do some research to understand how to set up mobile access for Power BI Report Server. Power BI Report Server uses the same app as the Power BI web service, but it requires a way for users to access the report server within the network. It requires either a VPN on your mobile device or use of Oauth with a web application proxy and ADFS running on Windows Server 2016. This is all fairly standard, but not usually something a BI team would handle, so make sure to involve your networking/security team early on to help plan.

PBIRSMobileArchAnother important aspect to plan is the folder structure on the Power BI Report Server. In a self-service environment, content owners will manage the content and permissions, so you want to keep things as simple and painless as possible. You’ll want to decide if you want folders for each department, each audience, or each subject area (or some combination thereof). You’ll also want to identify the power users and data owners who will act as content managers and decide if you will have a review/certification process before new reports can be published. This may be different for each department. It doesn’t have to be one size fits all, but you probably want to start with some process and loosen up as you get the hang of things rather than beginning with a free-for-all and then trying to implement some rigor in the deployment process. Permissions in Power BI Report Server work the same as SSRS native mode, so you’ll probably have to teach users how that works.

One area that seemed to have some gaps was the lack of subscription and alerting capabilities in Power BI reports. The normal subscription capabilities for Reporting Services reports are present, but there is nothing for Power BI reports. We used this opportunity to discuss how we might not need much push reporting if users have access to reports on their phones and can easily get to the information that way.

Looking Forward

A lot of the issues noted above should be resolved in the next version of Power BI Report Server. An August 2017 preview was released with the ability to use Power BI models (as opposed to being limited to SSAS Live Connections), which removes a lot of the limitations. Right now the August Preview doesn’t provide a way to refresh a Power BI model, but there should be something in place when the next GA version hits in Q4.

If you would like to use Power BI, but your organization can’t/won’t move to the cloud, Power BI Report Server can be a viable alternative, especially if you get a license for free because you had SQL Server Enterprise with active Software Assurance.

 

 

 

Data Visualization, Microsoft Technologies, Power BI

You Can Now Put Values On Rows In Power BI

Back in January 2016, I wrote a blog post explaining a DAX workaround that allows you to put measures on rows in a matrix in a Power BI report. I’m happy to say that you no longer need my workaround because you can now natively put measures on rows in a matrix in both Power BI Desktop and PowerBI.com.

This is accomplished via a new formatting option for the matrix.

As a quick example, I made a table with years on columns and measures in my values (and nothing on rows). I added three measures: Sales Amount, Total Cost, Gross Margin.

Initially, my matrix puts the measures across the columns.

But I can change that in the formatting options.

Find the Values section on the formatting pane and look for Show On rows. Toggle that switch to On.

And that gives you the three values on rows.

 

 

Excel, Microsoft Technologies, Power BI, SSIS

Installing the Microsoft.ACE.OLEDB.12.0 Provider for Both 64-bit and 32-bit Processing

I recently got a new laptop and had to go through the ritual of reinstalling all my programs and drivers. I sometimes work with SSIS locally to import data from Excel and occasionally do demos with Power BI where I read from an Access database so I needed to install the ACE OLE DB provider.  If you try to do either of those things without the ACE OLE DB provider you will get a message like the one below.

The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine.

I needed both the 32-bit and the 64-bit adapters to cover my various use cases. I started by installing the 64-bit Microsoft Access Database Engine 2010 Redistributable.  If you have ever attempted this, you know there is a secret. You cannot install the 2010 Access database engine with a different processing architecture (bittedness) from your Office install (or install both versions of the 2010 Access database engine) without a workaround.  If you try, you will get an error message.

ACE engine bittedness error

There are two options for getting the other version installed.

Option A: Install the 2007 Office System Driver: Data Connectivity Components

The 2007 Access database engine can read the same format, but didn’t have a 64-bit install. It doesn’t have the check to see what other products are on your machine and can co-exist peacefully with the 64-bit 2010 Access engine.

Option B: Install from the Command Line and Use the Passive Switch

  1. Open the command prompt.
  2. Type the file path and file name for your Access Database Engine install file.
  3. Follow it with a space and then “/passive” and then hit Enter.
  4. Delete or rename the mso.dll registry value in the following registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Common\FilesPaths

ACE install cmd

I wrote up a document for this a while back for a client, but I can never remember where I put it. So I’m posting this on my blog to make this info easier to find for me as well as you.