project Archives - Business Intelligence Info

Tag Archives: project

Project Flogo: From IoT to Machine Learning

June 21, 2018 TIBCO Spotfire

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

Project Flogo Launches Native Machine Learning Capabilities

November 4, 2017 TIBCO Spotfire

At TIBCO NOW last week, I had the pleasure of announcing and demonstrating native machine learning capabilities, directly within Flogo Flows at the edge, directly on device without any networking requirements—all of the inferencing and aggregation is happening on device! What does this mean? Well, in some cases, it can lower operational costs up to 85% and users can begin to look at implementing use cases that were not possible before due to the potential latency and network traffic required to stream and run models in the cloud. This is truly the next phase of IoT edge computing!

Last week, we launched with support for Google TensorFlow and TIBCO Statistica. Let’s break this down a bit. First and foremost, with TensorFlow you’ll use Python to write your training script and export the saved model. Flogo supports the use the higher-level TensorFlow tf.estimator API to abstract away the low-level coding of the graph itself. The tf.estimator package exposes a model export method from Python, which will export the trained model protobuf (the file format used by TF) to a directory, that can be zipped up and passed directly to Flogo for native inferencing. Flogo takes the protobuf zip file (or path), decompresses, and executes within the Golang runtime via a CGO wrapping for the dynamically linked libtensorflow.so. This means that you don’t need Python on the edge device nor do you need to have anything installed from TensorFlow, other than the OS-specific dynamic library that is linked at runtime.

From within Flogo, you simply add the Flogo ML Inferencing activity, where you specify the location to the compressed model file (or path itself). You’ll also need to configure the activity with a few specifics—the model location (a path on the edge device or a zip file location), the input tensor name, and finally, a collection of features that are used to populate the graph for inferencing. An additional field titled ‘framework’ must be specified. Currently, ‘Tensorflow’ is the only available option, however, the activity has been developed with an extensible contribution model enabling additional backend deep learning frameworks to be plugged in and the activity will work just as it does today.

The output of the activity is a complex object containing a collection of results. Let’s go ahead and take an example. At TIBCO NOW in San Diego, I demonstrated the capabilities via a Raspberry Pi, with a Flogo Flow and DNN model built in TensorFlow, to do classification of data from an accelerometer. The Flogo Flow aggregated the data over a period of 50ms and collected 11 aggregated results, and every 500ms passed this data to the TensorFlow model for scoring. The output of the TensorFlow model are the various classifications (walking, jogging, and standing) along with the probabilities of each. The results were delivered in ~60ms with >99% accuracy!

Demo shown at TIBCO Now demonstrating on device data classification with TensorFlow

The demo along with the Python training code and sample training data sets will be made available on the Flogo GitHub page

Okay, let’s take a look at the TIBCO Statistica support now, shall we? TIBCO Statistica provides a wide variety of comprehensive stats and machine learning capabilities in a visual workflow. To put it another way, much of what you’re doing in Python code to leverage TensorFlow, would be done graphically within the Statistica UI. Statistica is a powerful tool that has developed over time. What I want to focus on is the new addition to natively export models as Flogo Activities.

TIBCO Statistica training and exporting a model for use within Flogo

The export to Flogo will produce all of the Golang code and the JSON metadata data required by Flogo to execute the model directly within a Flogo Flow. You’ll just need to take the exported bundle from Statistica and place it within a git repo and install in Flogo WebUI (or manually add the ref to your Flogo app JSON). Then, just send the device data to this activity for inferencing. See the screenshot below of a Statistica produced Flogo Activity within Flogo WebUI.

Boosted Tree model implemented as a Flogo Activity

More details to come, including a catalog of sample TensorFlow and Statistica models, tutorials, and documentation. Check the Flogo GitHub page regularly for updates (and of course share your feedback via GitHub issues)!

Let’s block ads! (Why?)

The TIBCO Blog

Tag Archives: 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

Project Flogo Launches Native Machine Learning Capabilities

Recent Posts

Categories

Archives