Adatis

Adatis BI Blogs

PowerBI Optimisation P3– Extracting and Source Controlling PowerBI Data Models

Source Control – once seen as “something proper developers do” – has been an integral part of the way business intelligence developers work for a long time now. The very idea of building a report, data model or database without applying some kind of source control actually pains me slightly. However, there has been a push for “Self-Serve” reporting tools to strip out anything that looks remotely like a technical barrier for business users - This includes the ability to properly track changes to code. We find ourselves in a very familiar situation – versions of PowerBI desktop files are controlled by including version numbers in file names. I’ve seen several copies of “Finance Dashboard v1.2.pbix”. This is obviously dangerous – who’s to say that someone didn’t open up the file, edit it and forget to increment the name. Once a file has been shared, there’s no controlling what changes happen at that point. If this happened to an SSIS package, for example, we would still be able to perform a code comparison. This would highlight differences between the two packages so we could accurately see what caused the changes. This is not currently possible with PBIX files in their entirety. We can, however, compare the data model behind the file. This allows us to check for changes in business logic, amendments to DAX calculations, additions of new fields etc. If the performance of two PBIX files different drastically even if they were meant to be the same “version”, then this is a reasonable starting point! Extracting the Data Model from a PowerBI PBIX File Firstly, we need to extract the JSON that describes the Tabular Model embedded model (technically, this is TMSL, the tabular model scripting language, but it’s still JSON…) We can do this by connecting to the model via SSMS. I’ve talked about the steps required to do this here. So, assuming you have found your temporary SSAS port and connected via SSMS, you should see something like this: As we would with any other Tabular model, you can right-click and script out the database as so: If we do this to a new query window, you’ll see the various JSON objects that describe your PowerBI model: This script contains the details for all tables, attributes, DAX measures etc required for your data model. Comparing PowerBI Data Models What if someone has been using a specific version of my PowerBI desktop file, but they’ve modified it and it has stopped working? For a Tabular model, I’d compare the model definition to source control which will automatically highlight any changes. Now that we can script out our PowerBI model, we can apply the same principles. Say, for example, I make a couple of changes to my sample PowerBI report and want to figure out how it has changed compared to a baseline script I exported previously. The easiest option is to use a tool like Textpad – here you can compare two text documents and it will highlight any differences it finds between the two. For example, I changed the name of a table and removed a column, the text comparison highlights this change as below: I can now be confident that if someone sends me a PBIX file, I can check to see if there are any data model changes without having to manually eyeball the two side by side. This alone is a huge leap forward in manageability of models. The next step would be to add this file to an actual Source Control provider, such as Visual Studio Team Services. This tool is free for the first 5 users and can be used with Visual Studio 2015 Community Edition – which is also free! Essentially you would add this exported script to your source control directory each time you updated the model. By checking in your new model, you can compare previous versions, much like with the TextPad editor above. Final Thoughts In the end, this isn’t real, true Source Control. If you make a mistake, you can only view what the previous configuration was, you cannot roll back code directly into your PowerBI model. It is, however, a step towards managing PowerBI with a bit more discipline and rigour. I don’t see this as a huge drawback as rumours on the wind are hinting at larger steps in this direction coming with future releases. Let’s hope we’re not having to work around these problems for much longer!    

From Nothing to Source Control

Source Control, often called Revision Control or Version Control, is a process of maintaining projects. It is essential for any software project, collaborative or otherwise. Below is a non-exhaustive list of benefits of Version Control: Version Control and Tracking Provides locking and serialized changes to any file Structure Everyone operates in the same way Centralized storage Multiple users can work on the same project simultaneously. When a person makes a change and checks it in, everyone else can get the latest version Backups If you accidentally delete a file you can just undelete it Ability to branch If you want to develop a large feature or make small fixes separate from the main trunk so as not to disturb or break the main development effort your can create a branch of code History You can see who made changes to what and when Roll back If a build is broken you can roll back the changes made These features are fantastic for large projects but also for small ones, even if you are working by yourself. As well as the above points, there is also capacity to accommodate integrated automated builds and project planning and tracking, for starters. So how do you go about getting free source control? Visual Studio Online includes free TFS for up to 5 users! However this does not include MSDN Subscribers. Navigate to http://www.visualstudio.com/ Click on ‘Get started for free’ and you will be redirected to a login screen. This is to create a Visual Studio Online account. You will then be asked to sign in with your Microsoft account or an organisational account before being asked to fill in some extra details. If you do decide to sign up with an organisational account, it means that users can only sign into the Visual Studio Online account if they are a member of the connected Azure AD or 365 Subscription. If you sign up with a Microsoft account then users are fully managed by the Visual Studio Online account owner (and Administrators) and only Microsoft accounts can sign into it. When you sign up you will need to provide a URL in the form of: https://<yoururlhere>.visualstudio.com/. This has to be unique. If you choose a URL that is already taken you will be presented with an error message stating that that URL is reserved. Once you’re all signed up you will be presented with your newly created Visual Studio Online page. Here you can start creating Projects and adding users. If you navigate to Users you will see that you have yourself as a user and that on the far right you should have the number of free and total users. You can add new users by email address and select whether they are a basic or Eligible MSDN Subscriber. You can edit existing users to change their License type. Finally you can delete users. If you navigate back to the homepage you can create your first project. With your Project you can choose between two different versions of Version control. These are Team Foundation Version Control and Git. With Team Foundation, typically, team members have only one version of each file on their dev machines. Historical data is maintained only on the server. Git is a decentralized version control system. Each developer has a copy of the entire source repository on their dev. You can learn more about the differences in this extensive page: TFVC or Git You also have the choice of a process template. You have the choice of 3: Microsoft Visual Studio Scrum 2013.3 MSF for Agile Software Development 2013.3 MSF for CMMI Process Improvement 2013.3 These define the set of work item types (WITs), queries, and reports that you’ll use to plan and track your project. They are detailed here: Process Templates Once you have created the project you can open it with Visual Studio. This requires you to have Visual Studio 2013. There is a link on the home page to obtain VS 2013. There are 3 free Express versions to choose from or you can trial Ultimate 2013. You need to configure the workspace mappings before you can open the solution. This maps the files to a local directory on your machine. Once you have chosen a directory of your choice then you can click ‘Map & Get’ and then you are ready to go. The same applies if you are using Git where you have to specify the directory for the local repository. In the Solutions section you should see the option ‘New…’ This brings up the familiar New Project dialogue. Once you add one, you can check it in via ‘Pending Changes’, and you’re done! If you wanted to sign into your online source control starting from Visual Studio on your desktop instead then open up Visual Studio and on the ribbon choose ‘TEAM’ > ‘Connection to Team Foundation Server…’ The Team Explorer window will appear on the right. Then choose ‘Select Team Projects’ and add your URL you created earlier, as a server. You will need to provide credentials. If you wish to add more projects you can do so via the home page using ‘New’ under the recent projects & teams section. You can always get back to it by clicking the Visual Studio Online link that is in the blue bar at the top left of the page. This can also be done from within the Team Explorer on your desktop VS.

TF204017 The operation cannot be completed because the user does not have one or more required permissions (Use) for workspace

The titled error message (above) often occurs when a number of people work on a particular server, using the same credentials. This article explains the different workspace permissions that can be assigned in Visual Studio, as well a way of eradicating the error message. Please Note: This post is only relevant to TFS 2010 and beyond. Any previous versions will only allow a workspace to use the ‘private workspace’ setting Background When creating a TFS workspace and linking it to Source Control, the associated solutions (and files) are saved to a local location. By default, everything saved here is set with a ‘private workspace’ permission. As a result, only the associated user (that is logged on to a server) can create, edit or delete parts of the solution. In most cases, you would want TFS to apply the ‘private workspace’ permission. This ensures individuals have their own copy of locally developed work and prevent the risk of anything being accidentally overwritten or deleted. However, there may be a need to share a workspace across multiple users, where the solution files are store on a shared drive. Furthermore, some servers use generic log in credentials (to a server), in which more than one person would connect to TFS. Switching the TFS workspace to a more ‘public’ setting would enable everyone to share the same solution files. Workspace Permissions There are 3 types of workspaces in TFS 2010 (and beyond); 1. Private a. Default setting and most commonly used. Only the logged in user will be able to create, edit or delete from the local files. In effect, only the owner will have permissions. 2. Public-limited a. Additional users are granted the Read and Use permissions on the workspace. 3. Public a. Every valid user of the team project collection has the same permissions as the owner: Read, Use, CheckIn, Administer. Edit TFS Workspace Settings The below instructions illustrate how to change a workspace permission in Visual Studio 2010. The same logic applies to Visual Studio 2012 and 2014. 1. From the File menu, click Source Control, and then click Workspaces. 2. In the Manage Workspaces dialog box, under the Name column, highlight the workspace that you want to modify, and then click Edit. 3. In the Edit Workspace dialog box, Click Advanced. 4. Change to the desired workspace by using the ‘permissions’ drop down. Fig 1.0 – Edit Workspace Example 5. Click OK to confirm. Test Workspace Permissions You'll need to log onto the machine which has the public workspace. After starting Visual Studio 2010 and connecting to the server which has a public workspace, you'll be able to see the workspace in the appropriate drop-down combo boxes in the Source Control Explorer and Pending Changes tool windows. 1. From the Team Explorer home page (Keyboard: Ctrl + 0, H), choose Source Control Explorer. 2. From the menu bar choose View, Other Windows, Source Control Explorer. 3. Choose the recently changed ‘Public Workspace’ and begin to use it. Summary Although the original TFS error message was the reason for my findings, it also highlights the fact that there are now different workspace permissions that can fit your needs. To any users of TFS and Source Control, these types of articles are priceless when learning more about it. Visit the websites below for a more in depth look into workspaces: - http://social.msdn.microsoft.com/Forums/vstudio/en-US/21440b8d-67c4-4aa5-8a1b-3c747ebbb440/tf204017-the-operation-cannot-be-completed-because-the-user-does-not-have-one-or-more-required?forum=tfsgeneral - http://blogs.msdn.com/b/phkelley/archive/2010/02/04/improvements-to-workspaces-in-tfs-2010.aspx - http://msdn.microsoft.com/en-us/library/ms245466(v=vs.100).aspx