project Archives - Business Intelligence Info

Tag Archives: project

Collaborating for a Successful Dynamics 365 Project

August 26, 2018 Microsoft Dynamics CRM

In order for a Microsoft Dynamics 365 project to succeed, it is critical to get input early and often from the key stakeholders. Oftentimes projects are driven from either the business or the IT side of a company. However, the most successful projects have these two sides of the business working together from the start to the completion of the project.

Why don’t companies always have IT and business working side by side? If you take some time to think about this question, it seems a bit rhetorical. Of course, both teams need to provide input based on their unique knowledge bases – however, this often isn’t the case.

Siloed Projects

The desire to conduct projects in a siloed manner can stem from distrust. If teams are meant to work together, the individuals need to share ideas and to trust each other. Some of the mistrust can be a byproduct of failed past projects that were not worked on in tandem. When the business conducts a project without sufficient input from IT, things can be missed.

Here are some examples:

Not taking into account the amount of data that needs to be transferred from a legacy system.
Not formulating a plan for the data transfer from a legacy system.
Not accounting for any integrations with other systems.
Security planning.
Testing plans.
Go Live planning input.
Incomplete knowledge regarding IT enterprise planning.

When IT runs a project without significant input from the business, there are also risks:

The use cases for Dynamics 365 development are not fully realized as the IT team members don’t use Dynamics 365 in the same way the business does.
The IT team may not understand the pain points the Business is trying to address with the build/upgrade.
How will success for the project be measured?
It is important to understand the strategic vision when implementing the application – the business may own this knowledge.
Incomplete knowledge of how training should be delivered.
How to win at successful user adoption.

It is not uncommon for Dynamics 365 projects to fail when a business unit tries to lead the project in a siloed fashion. It can be challenging for companies to move to a cooperative development between IT and the Business, but the benefits outweigh the negatives in so many ways. Some of the benefits include: gaining trust between the two areas of the business, less meeting time, less rework and the higher likelihood of creating a product that functions as both groups require.

Benefits to Collaboration

Let’s expand on how having the two groups work on the deliverable can be beneficial. By working together on a project or initiative, the teams can learn to work together. Both groups are stereotypically very different. Oftentimes if you asked the business, they would stereotype IT as moving very slow and methodically and not understanding the needs of the business. If you asked IT, they might say the Business can be very reactive and lacks an understanding of what needs to be done to the IT infrastructure to get what they need built. If the two groups are encouraged and allowed to work together, they can learn about each group’s needs and requirements.

When these teams better understand each other’s requirements, this can promote a better working relationship in the long run. The teams can learn the facts behind the stereotypes to hopefully promote a more productive and successful working environment. It can lead to a solid partnership with shared goals, metrics, and deep collaboration.

Collaborating for a Successful Project

It is imperative for the two groups to start working together on the vision for the Dynamics 365 build from the inception, and all team roles should be represented. For instance, it is tempting to try to save on budget and try to roll in certain team members later in the project such as the quality assurance (QA) and training resources. This can backfire and the cadence of the project is affected when the project has to pause to catch up the late joiners.

Another very important requirement is to get input from the business users that will use Dynamics 365 on a daily basis. The team needs to understand what their roles are and how they will use Dynamics 365. This will allow the team to capture all use cases and to provide innovative input. Consultants need to understand the business in order to be consultative. They also need to understand the vision for the future. This helps them to create a backlog and to help guide the project through the first phase. If we tried to build a system that is perfect from the start, we would never get to a state to release the build.

Saving Time with Collaboration

Let’s take some time to talk about the possible time savings by having a collaborative project team. When demos are presented to the entire team, all members can give input and a discussion can occur. Team members can hear pros and cons from both teams and better understand the direction the team chooses to follow. Instead of showing the build to IT and then to the business – it is done at the same time, which allows for decisions to actually be made sooner. The stakeholder(s) in the project can serve as a “tie-breaker” if there are decisions that need to be made.

Agile Methodology

When we talk about a “demo”, we are assuming that the project is following an agile methodology. Agile development definitely has pros and cons. Not all companies can or want to adopt this methodology as it can sometimes increase costs due to the need for more meetings. That said, agile development does allow the project team to see progress and to provide input through each of the development stages. So if the project is headed in a direction that the company did not intend, it can be righted before the project is done. Of course, the stakeholders need to remain engaged for the entire process for this to occur.

Improved Transparency

By having a collaborative team with high stakeholder involvement, there is better transparency for the project. If the project gets behind in development, it is visible right away to the stakeholders. This was the stakeholders can provide input on feature prioritizations. When it comes time to prioritize the backlog, they have the knowledge and background to do this, as they have been involved in the project from the start. For example, if there is a change in business needs for the company, the stakeholders can re-prioritize what the team works on.

In the end, a collaborative team with the right members can produce a Dynamics 365 environment that is useful to the company. Both IT and the Business have knowledge of how it was developed and why. They will also understand what is in the backlog for the next phase, and when it is time to start on the next phase, they can move forward. The team will be accustomed to working together and can successfully start another phase of the project.

Need help on your next Dynamics 365 project? From implementation to support to training – we can help! Contact us today.

Happy Dynamics 365’ing!

Let’s block ads! (Why?)

PowerObjects- Bringing Focus to Dynamics CRM

Project Flogo: From IoT to Machine Learning

June 21, 2018 TIBCO Spotfire

Let’s block ads! (Why?)

The TIBCO Blog

Microsoft launches Project Brainwave for deep learning acceleration in preview

May 8, 2018 Big Data

Microsoft today announced that Project Brainwave, its system for running AI models with specialized chips, is now available in preview on Azure. Brainwave allows developers to deploy machine learning models onto programmable silicon and achieve high performance beyond what they’d be able to get from a CPU or GPU. Microsoft claims Project Brainwave makes Azure the fastest cloud to run real-time AI today.

Brainwave uses specialized field programmable gate array (FPGA) chips to achieve its speed and low latency, and Brainwave on Azure will use Intel Stratix 10 chips and be able to support ResNet50-based neural networks.

Microsoft first revealed its use of Brainwave for serving AI models last summer, and in March said it’s being used to make AI that powers Bing search results 10 times faster.

The news was announced onstage at Build, the Microsoft annual developer conference being held May 7-9 at the Washington State Convention Center in Seattle, Washington.

Also announced today were updates to Azure Bot Service and IoT Edge, a Speech Devices SDK, a $ 25 million initiative to support the creation of AI for people with disabilities, and Amazon joined Microsoft onstage for a demonstration of how Cortana and Alexa will work together.

Microsoft launches Project Brainwave for deep learning acceleration in preview

Let’s block ads! (Why?)

Big Data – VentureBeat

The Great Function Project – Part 3

April 25, 2018 Self-Service BI

Another great trilogy comes to an end… well at least until the next one comes out.

If you haven’t “seen” part 1 & part 2, go back and check those out in sequence. We’re two-thirds of the way through a co-development project to build a complete reference of every DAX, Excel & M function available to you. In part 3 today, we’re finishing it out by adding the NKOTB into the mix – M.

Yeah, M has its challenges when you’re starting out with it… Search Engine utilization being just one of them. We’ll resolve that one today so that you’ll never need to search for an M function again.

Lack of Intellisense when writing M is another major one. Just like DAX and Excel, there is an expected syntax to M, but Power BI is not going to help you out with this – yet. We’re also going to improve that situation too by getting you definitions and examples of many of the functions.

Sounds good? Let’s go.
So click Edit Queries to enter the Power Query Editor, and we’ll get started.

The M Formula Bar is disabled by default, so make sure you have it enabled. If you don’t see it go to the View menu, then check the box on the left that says Formula Bar.

Go to New Source –> Blank Query

Not all M is going to be this easy, but this one will be a good confidence builder wlEmoticon winkingsmile 1 The Great Function Project – Part 3 . Go to the Formula bar and tweet write:

Yep, that’s it. One case-sensitive word gets you the complete library of M Functions – and some extras we’re not interested in right now.

A list of names isn’t enough you say? Well, aren’t you getting a little spoiled now? OK, OK. We can do this, but to get the rest of the metadata that I know you want, that will mean writing a few custom M columns and doing a little cleanup. It’s about a 10 step process, so for the sake of time, we’ll take a shortcut and you can reverse engineer the details of this query once we’re done (spoiler: Value.Metadata is the magic function).

Go to Home –> Advanced Editor

Then copy the below code over whatever you have appearing in the Advanced Editor window then hit OK.

let
Source = #shared,
#”Converted List to Table” = Record.ToTable(Source),
#”Added Type Column” = Table.AddColumn(#”Converted List to Table”, “ValueType”, each Value.Type([Value])),
#”Is this row a function?” = Table.AddColumn(#”Added Type Column”, “Is Function?”, each Type.Is([ValueType], type function)),
#”METADATA!” = Table.AddColumn(#”Is this row a function?”, “GoodStuff”, each Value.Metadata([ValueType])),
#”Show me the Metadata” = Table.ExpandRecordColumn(#”METADATA!”, “GoodStuff”, {“Documentation.Name “Documentation.Description”, “Documentation.LongDescription”, “Documentation.Category”, “Documentation.Examples”, “Documentation.DisplayName”, “Documentation.Caption”}, {“Documentation.Name”, “Documentation.Description”, “Documentation.LongDescription”, “Documentation.Category”, “Documentation.Examples”, “Documentation.DisplayName”, “Documentation.Caption”}),

#”If there is more than one example take the first one” = Table.AddColumn(#”Show me the Metadata”, “Examples”, each try if Type.Is(Value.Type([Documentation.Examples]), type record) then [Documentation.Examples] else List.First([Documentation.Examples]) otherwise null),
#”Show me the examples” = Table.ExpandRecordColumn(#”If there is more than one example take the first one”, “Examples”, {“Description”, “Code”, “Result”}, {“Description”, “Code”, “Result”}),
#”Renamed Columns” = Table.RenameColumns(#”Show me the examples”,{{“Name”, “Function name”}, {“Code”, “Example Code”}, {“Result”, “Example Output”}, {“Documentation.Description”, “Short Description”}, {“Documentation.LongDescription”, “Long Description”}, {“Documentation.Name”, “Documentation Name”}, {“Documentation.Category”, “Category”}, {“Documentation.DisplayName”, “Documentation DisplayName”}, {“Documentation.Caption”, “Documentation Caption”}, {“Description”, “Example Description”}}),
#”Filter out non-functions” = Table.SelectRows(#”Renamed Columns”, each ([#”Is Function?”] = true)),
#”Removed Columns” = Table.RemoveColumns(#”Filter out non-functions”,{“Value”, “ValueType”, “Documentation.Examples”, “Is Function?”}),
#”Added Alt Category” = Table.AddColumn(#”Removed Columns”, “Alt Category”, each Text.BeforeDelimiter([Function name], “.”), type text),
#”Added Subcategory” = Table.AddColumn(#”Added Alt Category”, “Subcategory”, each Text.AfterDelimiter([Function name], “.”), type text),
#”Replaced Value” = Table.ReplaceValue(#”Added Subcategory”,””,””,Replacer.ReplaceText,{“Long Description”}), #”Replaced Value1″ = Table.ReplaceValue(#”Replaced Value”,””,””,Replacer.ReplaceText,{“Long Description”}),
#”Replaced Value2″ = Table.ReplaceValue(#”Replaced Value1″,”

”,””,Replacer.ReplaceText,{“Long Description”}),
#”Replaced Value4″ = Table.ReplaceValue(#”Replaced Value3″,”

”,””,Replacer.ReplaceText,{“Long Description”}),
#”Replaced Value5″ = Table.ReplaceValue(#”Replaced Value4″,”</li>”,””,Replacer.ReplaceText,{“Long Description”})
in

#”Replaced Value5″”, “Documentation.Description”, “Documentation.LongDescription”, “Documentation.Category”, “Documentation.Examples”, “Documentation.DisplayName”, “Documentation.Caption”}, {“Documentation.Name”, “Documentation.Description”, “Documentation.LongDescription”, “Documentation.Category”, “Documentation.Examples”, “Documentation.DisplayName”, “Documentation.Caption”}),
#”If there is more than one example take the first one” = Table.AddColumn(#”Show me the Metadata”, “Examples”, each try if Type.Is(Value.Type([Documentation.Examples]), type record) then [Documentation.Examples] else List.First([Documentation.Examples]) otherwise null),
#”Show me the examples” = Table.ExpandRecordColumn(#”If there is more than one example take the first one”, “Examples”, {“Description”, “Code”, “Result”}, {“Description”, “Code”, “Result”}),
#”Renamed Columns” = Table.RenameColumns(#”Show me the examples”,{{“Name”, “Function name”}, {“Code”, “Example Code”}, {“Result”, “Example Output”}, {“Documentation.Description”, “Short Description”}, {“Documentation.LongDescription”, “Long Description”}, {“Documentation.Name”, “Documentation Name”}, {“Documentation.Category”, “Category”}, {“Documentation.DisplayName”, “Documentation DisplayName”}, {“Documentation.Caption”, “Documentation Caption”}, {“Description”, “Example Description”}}),
#”Filter out non-functions” = Table.SelectRows(#”Renamed Columns”, each ([#”Is Function?”] = true)),
#”Removed Columns” = Table.RemoveColumns(#”Filter out non-functions”,{“Value”, “ValueType”, “Documentation.Examples”, “Is Function?”}),
#”Added Alt Category” = Table.AddColumn(#”Removed Columns”, “Alt Category”, each Text.BeforeDelimiter([Function name], “.”), type text),
#”Added Subcategory” = Table.AddColumn(#”Added Alt Category”, “Subcategory”, each Text.AfterDelimiter([Function name], “.”), type text),
#”Replaced Value” = Table.ReplaceValue(#”Added Subcategory”,””,””,Replacer.ReplaceText,{“Long Description”}), #”Replaced Value1″ = Table.ReplaceValue(#”Replaced Value”,””,””,Replacer.ReplaceText,{“Long Description”}),
#”Replaced Value2″ = Table.ReplaceValue(#”Replaced Value1″,”

”,””,Replacer.ReplaceText,{“Long Description”}),
#”Replaced Value4″ = Table.ReplaceValue(#”Replaced Value3″,”

”,””,Replacer.ReplaceText,{“Long Description”}),
#”Replaced Value5″ = Table.ReplaceValue(#”Replaced Value4″,”</li>”,””,Replacer.ReplaceText,{“Long Description”})
in
#”Replaced Value5″

And now you have a complete listing of 674 M functions along with a decent amount of the associated metadata! There are a total of 12 M functions being used in this query (highlighted yellow) and you now have the resource to take a closer look at what they do.

Bringing It All Together (Optional Exercise in Futility)

We’ve already created a lookup table to cross-reference DAX & Excel in Part 1. There’s no reason why we can’t add M to that lookup table, even though there is zero overlap.

You’re probably going to run into errors at this step or on the refresh of the Function Lookup Table. The DAX query from part 1 is going to be the culprit. You’ll need to re-run DAX Studio and update the DAX query Source step with both the new local host and new catalog_name fields following the steps from part one here or here.

To update your Function Lookup Table with the M functions.

1 – Select the Function Lookup Query you created in Part 2
2 – Click the gear icon on step 2 of the query ‘Appended Query’
3 – Change the default Append Query behavior from appending ‘Two tables’ to ‘Three or More tables.’
4 – Select the M table
5 – Click Add to now combine DAX, Excel & M

And we’re done. Hit close and apply and proceed to layout your complete Power BI & Excel reference in any way that works for you!

Let’s recap what WE covered in the Great Function Project:

We queried the metadata of a PBIX file (twice) even writing a little T-SQL along the way.

We used DAX Studio.

We connected to SQL Server Analysis Services.

We built a web scraping application.

We performed some ETL – indirectly writing M in the process.

We created a dynamic lookup table driven my 3 separate fact tables.

We have written M and we’ve also “borrowed” some, both of which are fine.

….and you have done this using a free application with no traditional data sources in (hopefully) less than 30 minutes total!

As much as this project was building up to the end product, we sure did get some reps in a lot of different techniques along the way! If any of this is new to you, I hope it’s demystified a few things for you.

Please download the .pbix for your reference here

I’d also LOVE to hear how you put your workbook to use and would love to see what you’ve put together. Feel free to share it with me [email protected]

We like to think that is no accident. We’re different. First of a new breed – the kind who can speak tech, biz, and human all at the same time.

Want this kind of readily-absorbable, human-oriented Power BI instruction for your team? Hire us for a private training at your facility, OR attend one of our public workshops!

Let’s block ads! (Why?)

PowerPivotPro

Managing Project Scope

April 12, 2018 Microsoft Dynamics CRM

To be or not to be…. That is a scope question.

Have you ever been on a project and been faced with the decision of whether something should be part of the project or not? Often times, people see this as a single decision or perhaps more commonly, maybe two.

Many Project Managers assert that there are several decisions you should make when defining whether something is a part of project scope. The value of asking these questions is that they reduce the chances of scope creep, help harden your project documentation, and help you include key decision makers in changes to the project.

Let’s walk through this process to see what it looks like! (Reference the diagram below).

041118 1509 ManagingPro1 Managing Project Scope

Obviously, the starting point is that there is ambiguity around whether something is included in the current project scope.

Your first decision is to define if it is indeed in the existing project scope or not. Typically, you would refer to the Statement of Work, Project Charter, or other organizational scope document. If you’re unable to resolve this question by referencing one or multiple project documents, then you may need to revisit your documentation as it may lack the detail to adequately define and help manage scope now and moving forward. Identifying this issue early in a project will help mitigate one of the most common issues, which is “scope creep.” Often the most common cause of scope creep is that scope was not well defined and documented at the beginning of a project.

If the proposed item is defined as not being in scope, then you need to determine if you should increase the current project scope to include it. This is the most common path for project changes, and results in either a project scope change, or pushing the item to a later date or dropping it entirely.

If the proposed item is within scope, your next decision should be if it is worth including in the existing project NOW.

Typically, this question is not asked as a part of the process. Usually once an item is defined as being within scope, it is immediately assumed that it is included in current scope without any real vetting of the impact or value of the addition.

Asking this question has some key benefits. The first benefit is that it helps you understand where this item came from and why it changed. If it is a something that was missed, then it may highlight that there could be more items like this now or in the future. If this was a change, then there is the possibility it may change again which means you need to understand now so you can plan appropriately. All too often project scope expands because changes are included without effectively defining the extent those changes will have on an entire project and not just on a specific project item.

The other benefit of asking this question is that you engage key stakeholders in defining whether something should be in the current project. As a part of this process, you must clearly define the value of these items. This will aide in the later steps of getting more resources or time to complete the work if it is added to the current project.

If it is worth including now, then the follow-up question should be if you could adjust or cut current scope or just add it in. This is another question that is rarely asked, but can help mitigate change orders that increase cost, project duration, and needed resources. Sometimes project or organization constraints force the question of one item taking the place of another instead of just adding it in. Projects can sometimes grow exponentially in scope and end up being nearly two or three times what was initially planned. This results in significant cost and duration increases that are perceived as poor project or program management when the true root cause was poor project scoping at the beginning of the project.

If you determine that is it not worth including now, then you should move that item to a later phase, put it onto the backlog, or move it to another project. Often this is for items that will change again or items whose scope you’re unable to adequately define now. In most cases, items that are pushed out usually change significantly or end up being dropped all together. One key advantage of asking this question beyond managing scope is that you engage the business on the value of adding something to the project scope.

When you follow this process you ensure that you have tried your hardest to mitigate scope creep, have determined if everything in scope is ‘value added,’ as well as making sure all stakeholders are aware of how and why project scope changed along with any changes in budget, duration, and resources.

Looking for more helpful guides to Dynamics 365? Be sure to subscribe to our blog!

Happy Dynamics 365’ing!

Let’s block ads! (Why?)

PowerObjects- Bringing Focus to Dynamics CRM

Organizational Units with Dynamics 365 for Project Service Automation

April 7, 2018 Microsoft Dynamics CRM

Microsoft released an end-to-end solution to manage projects within Microsoft Dynamics 365 for Project Service Automation (PSA). Professional Services firms can track their projects from sales to delivery and invoicing. To fully use its capabilities, new components must be configured. Let’s get familiar with the terms!

Business Units vs Organizational Units: What’s the difference?

If you’ve been around Microsoft Dynamics 365, you may already be familiar with Business Units: the basis of the security structure per your organization. If you are a new to this, that’s ok too. You can learn more about Business Units here.

When configuring PSA, you must first consider the levels of access a division needs; as you would normally do with Microsoft Dynamics 365. This will help you define your Business Units. Then, consider the costs related to running a division of your professional services firm. Does using a billable resource from “Division A” instead of a billable resource from “Division B” affect the cost of delivering a project? Is there a difference even if they have the same role/position? If it does, now we are talking about Organizational Units.

040318 2037 Organizatio1 Organizational Units with Dynamics 365 for Project Service Automation

These divisions, or Organizational Units above, are how your company is organized, either by locations, functions, or any other factor. The one thing to look for is security for Business Units and costs for Organizational Units.

How to Create an Organization Unit

Once you define your divisions, you are ready to create an Organizational Unit as follows:

1. In your Dynamics 365 portal, go to Project Service and select Organizational units.

040318 2037 Organizatio2 Organizational Units with Dynamics 365 for Project Service Automation

2. Click New.

040318 2037 Organizatio3 Organizational Units with Dynamics 365 for Project Service Automation

3. Complete the General information area per the division you want to create.

040318 2037 Organizatio4 Organizational Units with Dynamics 365 for Project Service Automation

Name
(required): provide a meaningful name to easily identify the division
Description (optional): include a brief description to later remember what the name means
Currency (required): add the currency used to run the business within the division. Great for global companies with different divisions using the currency of their respective location. Yes, you’ll create an organizational unit for each!

4. Save your Organizational Unit to create the record, and continue.

5. Add a Cost Price List by clicking “+” on Cost Price Lists.

040318 2037 Organizatio5 Organizational Units with Dynamics 365 for Project Service Automation

6. Click the magnifying glass to search for a Cost Price List.

040318 2037 Organizatio6 Organizational Units with Dynamics 365 for Project Service Automation

7. Select a Cost Price List from the records shown or scroll-down to look for more.

040318 2037 Organizatio7 Organizational Units with Dynamics 365 for Project Service Automation

NOTE: At the beginning, you might not have a Cost Price List to add. Yet, every organizational unit should have at least one cost price list associated. Once you have created a Cost Price List record with the roles and expenses costs usually incurred by the division, return to the organizational unit record. Remember, this price list must have the cost context, and the same currency as the organizational unit your updating.

8. Close the record by clicking Save & Close.

Each company is different and so is the number of Organizational Units needed to set up your Microsoft Dynamics 365 for Project Service Automation. From smaller companies with only one, to companies with multiple organizational units to meet their complex structure and global businesses – no number of Organizational Units is better than the other. The best configuration is one that fits your business.

Learn more about Dynamics 365 for Project Service Automation here.

Happy Dynamics 365’ing!

Let’s block ads! (Why?)

PowerObjects- Bringing Focus to Dynamics CRM

The Great Function Project – Part 2

April 5, 2018 Self-Service BI

My name is Mathew Herring Jack, and I am a member of Traditional Excel Users Anonymous.

As a Traditional Excel User, I could not stop copying and pasting downloaded reports. Even when my manager diagnosed me with inaccurate month-end reporting, I could not stop using external workbook links. I admitted my reckless use of nested IF statements was hurting others, yet I knew I would do it again. I still don’t know why I needed to type FALSE at the end of my VLOOKUP’s – it makes no sense.

ab3c59ff2b17dbafcb50c98c988d9f62 The Great Function Project – Part 2

Yet, here I am today, using Excel as much as almost anyone on the planet, now many years SUMPRODUCT free.I almost never use the CTRL key anymore. In fact, I use the mouse a LOT in Excel now. A Traditional Excel User could never admit such a thing.

How is it possible to still be here today saying that I am an Excel expert and yet I don’t even use the formula bar anymore!

I found a way out. By following the 28-step program as described in the Big Book of Traditional Excel Users Anonymous and living in the disciplines of DAX and M every day, I can remain abstinent from Traditional Excel and the allure of absolute referencing.

I now know how to live without Traditional Excel. The compulsion to show off even my array formulas has been removed. My progressive illness has been arrested. My disease has been put into remission. I now dedicate my life to traveling the country helping others recover from the affliction… See you in Boston!

And Back To Your Regular Scheduled “Programming”

Today we’re revisiting my gateway drug, and we’re going to use the Power BI web connector to import every Excel Function to go alongside the DAX reference we built in part 1 of The Great Function Project.

If you missed part 1, go back and follow the steps. It should take all of about 5 minutes to get caught up. Part 2 should only take 5-10 minutes on top of that.

The general idea is that we’re together co-developing a Power BI reference that contains information on every DAX, Excel and M function available. We’ll analyze the similarities and differences between the three languages and will leave you with a useful resource to help you and your organization transition from Traditional Excel to Modern Excel.

If you did follow along with Part 1, re-open your workbook and insert coin to continue.

Web Scraping? Sounds Painful

Not everyone wants their online content harvested, but if you want to enrich your data model, there is a vast data source that you might be ignoring – the web. Need performance reporting adjusted from the population? Can do. Want to quantify the impact of a Patriots win on your Sunday sales? There’s a source for that.

Need every Excel function available? Check this link out.

Scroll down the page and look at all 471 of those Excel functions! Your scrolling finger is getting sore? OK then, I have a better idea. Let’s use this page as a data source and save it in Power BI for later.

Go to Get Data –> Web

Paste this url into the next screen and hit OK.

https://support.office.com/en-us/article/excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188

Power BI looked at that webpage and what it found were two important elements – Document and Table 0. Table 0 looks to have all the good information in it, so check the box next to it and hit ‘Edit.’

Commence Right Clicking

Rename your new Query to Excel and let’s do a little data cleanup… The goal here is to have three columns – Function Name, Function Group and Function Description AND to have Function Name is a similar enough format to the DAX Query that later on we’ll be able to link them together by a relationship.

1) Right click on Function Name column and select ‘Replace Values.’ Enter “ function” in as the Value To Find – note the space preceding the word ‘function.’ Hit OK.

2) Right-click on the ‘Type and description’ column and select Split Column –> By Delimiter. Select ‘Colon’ as the delimiter and check the ‘Left-most delimiter’ button. Hit OK.

Looking better, but happiness has not been achieved. More right-clicking is necessary.

3) Right-click column ‘Type and description.1’ and select Rename Columns. Rename to ‘Function Group.’ Right-click column ‘Type and description.2’ and select Rename Columns. Rename to ‘Excel Function Description.’

4) Right-click the ‘Excel Function Description’ column and select Transform –> Trim. This will get rid of spaces at the beginning or end of the column.

Excel – The Gateway Drug

I was a Finance guy. You wanted a business model? I could do that. Need a merger model to bolt onto that? I could do that too. Need a GAAP-compliant quarterly financial statement consolidations package that integrated with Word? Give me a few days. Traditional Excel put beer in my fridge for 15 years.

I’m not someone who’ll necessarily step outside my comfort zone. If I’m pushed outside of it, then I’ll run with it and thank you later. But in 2012, nobody was going to push me out of my Excel comfort zone. DAX was around, but let’s just say that its existence wasn’t well known, let alone how to find it.

So lucky for me, there was a lot of overlap between DAX and Excel – I didn’t need to step out of my comfort zone.

How much overlap EXACTLY though? Well, let’s find out… we’ll need to create a lookup table between our DAX and Excel tables.

Right-click on the DAX query from Part 1 and select ’Reference.’ Then rename that new query to ‘Function Lookup Table.’

Before we do the next step, go back and make sure that you have a column named ‘Function Name’ in both your DAX Query and your Excel Query. As a reminder, Power Query is case sensitive, so it needs to be exactly the same in both. My queries looked like this:

DAX Query

Excel Query

Now left click on Append Queries in the top right. Then select Excel from the drop-down menu Table to Append. If you get a message about Privacy settings just check ‘Ignore’ and move on.

You should now have a table that looks something like this:

This is more information than we need for the basic lookup table we are aiming for here.

Right Click on the Function Name column and select ‘Remove Other Columns,’ ’then right click again and select Remove Duplicates and now you have the most vanilla lookup table possible.

Hit ‘Close and Apply’ and Power BI will likely create the relationships for you, but if not just replicate this structure, creating the relationship using the Function Name columns..

Finally, time to write some DAX.

# of DAX Functions =
COUNTROWS ( ‘DAX’ )

# of Excel Functions =
COUNTROWS ( Excel )

# of Functions in Both DAX & Excel =
CALCULATE (
    COUNTROWS ( ‘Function Lookup Table’ ),
    FILTER (
‘Function Lookup Table’,
[# of Excel Functions] = 1
&& [# of DAX Functions] = 1
    )
)

% of DAX already in Excel =
DIVIDE ( [# of Functions in Both DAX & Excel], [# of DAX Functions])

And there we have our answer, 48.09% of the DAX language was already present in Excel.

Now, this doesn’t tell the whole story at all, but regardless of the numbers, getting started with DAX is within the comfort zone of anyone that’s written a SUM in Excel and lived to tell the tale.

Excel isn’t going anywhere as the world’s most used BI tool – but in the years to come in might just be used a little less…. traditionally.

As for Power BI, in my opinion, this extensibility is what drives it “from nowhere to being the leader in BI in the cloud” with “a $ 500 million annualized billings run rate for its 2018 fiscal year”. Achieving these milestones is a lot easier when 1 billion people already know 48.09% of your new product’s language.

Next up in the series is M…. I’ll be posting that on Tuesday 17th April. The lack of intellisense in M is was the primary reason I started the Great Function Project, so it’s kind of the Grand Finale.

We like to think that is no accident. We’re different. First of a new breed – the kind who can speak tech, biz, and human all at the same time.

Want this kind of readily-absorbable, human-oriented Power BI instruction for your team? Hire us for a private training at your facility, OR attend one of our public workshops!

Let’s block ads! (Why?)

PowerPivotPro

The Great Function Project – Part 1

March 7, 2018 Self-Service BI

Your First Co-Development Project

P3 has tried many different flavors of our brand of “consulting” through the years, but co-development projectsthat combinereal projectswithcoaching and knowledge transferhave turned out to have been the sweet spot in terms of success.

Sometimes we are hands-on in the build process, guided by your superior industry expertise. Then sometimes we remain entirely hands-off but guide you in the build. The important part is that wherever possible, we work together and that neither party progresses things so far forward that the other feels left behind.

By reading this far,you have implicitly agreed to join me on a trial co-development journey. Today, you are going to drive, and I’ll be your navigator – and that’s a pretty apt analogy because we’re going off-road.

Co-development projects require a commitment from both parties regarding both time & perseverance. Today you’ll only need about 5 minutes of time to completely build out Phase I of this project. Regarding perseverance, you might just need to turn a blind eye to some ugly code while still paying attention to the hidden underbelly of Power BI that I’m going to show you.

We are going to produce this interactive workbook of every DAX function you have available to you. Good news right? Super practical. For me at least I’ve found it easier to refer to than the MSDN pages.

My version looks like this:

Now does that sound like something you might be interested in?

But I’ll do even better than that. Stick with me, and in my next post, we will do the same for every Excel function and then again for every M function. By the end of our project, you will have a single interactive go-to reference for all three languages!

Would that be F’n great? So great that wouldn’t it be called the Great F’n Project? What’s that Editor Reid? No, it wouldn’t be? Not very searchable? Oh OK then….

Welcome to the Great Function Project!

Now I want you to hit save…. as hard as you can…

So now comes the time to commit yourself to this project. Open up a completely blank workbook save it as The Great Function Project and lets GOOOOOO!!!!!.

You know when you’re typing in your SUM function and the Intellisense prompts you with some guidance? It’s helpful right? But how does it know all that stuff? It has to be somewhere deep in the bowels of the file structure right? That’s the metadata, and there’s a ton of it behind the scenes before you’ve even created a table.

We’re going to take our Power BI file and ask it to look inside itself to extract its Metadata. So so meta…

Did you know? Power BI and PowerPivot are virtually identical to SQL Server Analysis Service Tabular under the hood. So it makes sense that we will be connecting to Analysis Services it to find this useful metadata. So go ahead, your turn.

You should end up here… but we don’t yet have everything we need to connect the file with… uh itself.

So now that I’ve gotten you this far, I have to ask you, do you have DAX Studio installed? No? OK, that’s fine, our project has hit a minor speed bump. This happens, but changing course quickly is our specialty. We’re going to need DAX Studio for all of about 30 seconds.

So at this point in our co-development project, it’s your turn to take things over. Before we can go any further, I’ll need you to download DAX Studio, install it and then open it.

For our purposes, DAX Studio is a tool that can show you all the underlying structures and metadata buried beneath the surface of your Power BI file – it does a lot more than just that though.

Connect DAX Studio to the workbook you were just tricked into saving.

In the big white space aka the Query Window enter this query:

Select * from $ SYSTEM.DBSCHEMA_CATALOGS

Now go up to the top right and run that query… I know what you’re thinking… what are we doing here? I explained that we were going to get the metadata from our file, well this is the first step.

Technet says that the DBSCHEMA_CATALOGS “identify the physical attributes associated with catalogs accessible from the database management system (DBMS).”…. uh cool, I guess.

I don’t know what that means. But what I do know is that DBSCHEMA_CATALOGS is one of 100+ Dynamic Management Views (DMV’s), each of which stores some key information/metadata about the content of our not-as-empty-as-first-thought workbook. In this case, it’s telling me the database name of the SSAS cube hidden inside our Power BI model.

Another example of a useful DMV is $ SYSTEM.MDSCHEMA_FUNCTIONS.This is where some of that Intellisense info lives. We’re going to query that DMV a little later on, but not from DAX Studio, so we’re pretty much done in DAX Studio….

That wasn’t so bad now, was it? Take note of the localhost information in the bottom right indicated with the #2 above. Flip back to the Power BI workbook and enter whatever you are showing here as the Server in your SSAS connection window that should still be open.

Then come back to DAX studio and right click on the cell in the Catalog_Name field market with a #3 above, and then copy the contents. Now returning to the Power BI workbook, paste that into the Database information.

Lastly, expand the MDX or DAX query panel and enter:

select [FUNCTION_NAME], [DESCRIPTION], [ORIGIN], [LIBRARY_NAME], [INTERFACE_NAME] from $ SYSTEM.MDSCHEMA_FUNCTIONS

I would normally just do a select * to get everything here too, but for some reason that didn’t work for me, so I reverted to handpicking the columns based on this reference.

You should end up with a screen looking something like this.

Hit OK.Boom goes the dynamite! We have functions!!!!

But um…. I guess I knew less DAX than I realized – I don’t recognize any of those function names. Click Edit to go through to the Query Editor, and we’ll get to the bottom of this.

If you scroll down though, you will start to see some familiar DAX functions eventually. It turns out that 1 value in the Origin column is an MDX function and the rest look to be DAX, so filter out the 1’s.

Important last step 1: Right-click on the query an uncheck ‘Include in report refresh.’ The Analysis Services details will change later on and break the queries, and a static import will be fine.

Important last step 2: Rename the columns to the names you see below. We want to make sure no later transformations will be required later on… In hindsight, beginning with DAX was a bad idea. M & Excel won’t be so fragile.

And that’s it – for now. Rename the table to DAX and then Close & Apply. Now go to town laying out your interactive DAX reference report in whatever way works for you.

Come back next week and bring your completed file with you.Next week (using a different and simpler technique) I want to help you bring, inside the same file, all 471 Excel functions and their definitions. Why Excel? Well, it is the world’s most widely used coding language, and well, we need to talk about this.

Let’s block ads! (Why?)

PowerPivotPro

Organizational Units in Dynamics 365 for Project Service Automation

March 6, 2018 Microsoft Dynamics CRM

Microsoft has an end-to-end solution to manage projects within Microsoft Dynamics 365 for Project Service Automation (PSA) so professional services firms can track their projects from sales to delivery and invoicing. To fully use its capabilities, certain components must be configured. Let’s get familiar with the terms!

Business Units vs Organizational Units: What’s the difference?

030518 1603 Introducing1 Organizational Units in Dynamics 365 for Project Service Automation

How to Create an Organization Unit

Once you define your divisions, you are ready to create an Organizational Unit as follows:

1. In your Dynamics 365 portal, go to Project Service and select Organizational units.

030518 1603 Introducing2 Organizational Units in Dynamics 365 for Project Service Automation

2. Click New.

030518 1603 Introducing3 Organizational Units in Dynamics 365 for Project Service Automation

3. Complete the General information area per the division you want to create.

030518 1603 Introducing4 Organizational Units in Dynamics 365 for Project Service Automation

Name (required): provide a meaningful name to easily identify the division
Description (optional): include a brief description to later remember what the name means
Currency (required): add the currency used to run the business within the division. Great for global companies with different divisions using the currency of their respective location. Yes, you’ll create an organizational unit for each!

4. Save your Organizational Unit to create the record, and continue.

5. Add a Cost Price List by clicking “+” on Cost Price Lists.

030518 1603 Introducing5 Organizational Units in Dynamics 365 for Project Service Automation

6. Click the magnifying glass to search for a Cost Price List.

030518 1603 Introducing6 Organizational Units in Dynamics 365 for Project Service Automation

7. Select a Cost Price List from the records shown or scroll-down to look for more.

030518 1603 Introducing7 Organizational Units in Dynamics 365 for Project Service Automation

8. Close the record by clicking Save & Close.

Learn more about Dynamics 365 for Project Service Automation here.

Happy Dynamics 365’ing!

Let’s block ads! (Why?)

PowerObjects- Bringing Focus to Dynamics CRM

NetSuite’s Latest Release Adds Revenue Recognition, Project Profitability and More for Services Companies

November 30, 2017 NetSuite

Posted by Terry Melnik, Product Marketing Director

For the modern services company, the competitive landscape is getting more and more difficult. Differentiating yourself, and finding competitive advantages are critical to your growth. Driven by customers like you, the newest NetSuite release, 17.2 includes many enhancements to help you be more competitive, take advantage of market changes more quickly, improve compliance with the newest revenue standards, and track your performance the way you need to measure it.

For the CFO or finance team, profitability is one of the most important key performance indicators of a service company’s performance. You can have dozens or hundreds of customer engagements, but regardless of the complexity or duration of any of these engagements, profitability is a key measure of success. While the method used to calculate profitability may seem to be a standard formula, feedback from our broad customer base proves that the layout and data in a profitability report are varied. In 17.2, NetSuite introduce more flexibility in the Advanced Project Profitability report through the introduction of a dramatically easier-to-use interface which allows you to define the setup of the methods and elements used to calculate profitability. Easier controls on the project profitability will make it simple to define your own layout to see as much detail and context as required. Money leakage, through errors in time reporting or rates, or other issues with project accounting are reduced as well as improving your ability to track underperforming projects more effectively.

With the 17.2 release, we also help your finance team with the introduction of our Advanced Change Order Management to help address the evolving revenue recognition standards and ASC 606 compliance. For those not aware, ASC 606 provides a comprehensive, industry-neutral revenue recognition model intended to increase financial statement comparability across companies and industries and significantly reduce the complexity inherent in today’s revenue recognition guidance. While ASC 606 should ultimately simplify how revenue recognition is managed, Advanced Change Order Management will help facilitate the accounting changes required to get into compliance by enabling the automation of re-allocation and re-classification in ASC 606 where contract modification is not accounted for as a separate contract, according to the contract modification date. Advanced Change Order Management benefits your finance team with improved governance, compliance and controls.

As a business leader of a services company, the ability to understand your market, make critical decisions and execute is critical to your growth. When making the important decision to increase billing rates, either for a given engagement or to take advantage of growth opportunities or changes in market demand, timing is everything. With the introduction of NetSuite’s new Billing Rate Cards, we address that challenge by adding more control throughout current and future billing rate changes. With the new Effective Dating and Future Rate Changes, resource managers and financial teams can lock in future billing rates with an effective date at any time. Missed rate changes, or errors in agreed upon rates are practically eliminated, maximizing revenue from every resource. All invoices are updated automatically once the effective date is reached, avoiding inaccuracies and the required corrections, improving customer service and satisfaction.

17.2 is an exciting release for all our services industry customers and continues to demonstrate NetSuite’s leadership in the services space with industry-leading features that help drive better visibility, compliance and controls. Learn more about the latest NetSuite release, 17.2.

Posted on Wed, November 29, 2017
by NetSuite filed under

Let’s block ads! (Why?)

The NetSuite Blog

Tag Archives: project

Collaborating for a Successful Dynamics 365 Project

Siloed Projects

Benefits to Collaboration

Collaborating for a Successful Project

Project Flogo: From IoT to Machine Learning

Microsoft launches Project Brainwave for deep learning acceleration in preview

The Great Function Project – Part 3

Bringing It All Together (Optional Exercise in Futility)

Let’s recap what WE covered in the Great Function Project:

Managing Project Scope

Organizational Units with Dynamics 365 for Project Service Automation

Business Units vs Organizational Units: What’s the difference?

How to Create an Organization Unit

The Great Function Project – Part 2

The Great Function Project – Part 1

Organizational Units in Dynamics 365 for Project Service Automation

Business Units vs Organizational Units: What’s the difference?

How to Create an Organization Unit

NetSuite’s Latest Release Adds Revenue Recognition, Project Profitability and More for Services Companies

Recent Posts

Categories

Archives