Monthly Archives: December 2016



This article passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at
Recommended article: The Guardian’s Summary of Julian Assange’s Interview Went Viral and Was Completely False.


CRM Software Blog Top 10 CRM Posts for 2016

[unable to retrieve full-text content]

The CRM Software Blog provided a wealth of Microsoft Dynamics CRM in 2016. See which posts were the most popular this year and congratulations to our top authors: Microsoft Dynamics CRM: SubGrid Control Functions by Beringer Associates Microsoft Dynamics CRM 2016 – Voice of the Customer Surveys by Beringer Associates   Free Download of CRM 2016 […]

The post CRM Software Blog Top 10 CRM Posts for 2016 appeared first on CRM Software Blog.

CRM Software Blog

Power BI Desktop – 2016 Year in Review

2016 has been quite the year for the Power BI Desktop. Over the course of our 11 releases this year, we’ve delivered over 190 features and improvements based on your feedback. For December, we are taking a break from our monthly releases to focus on quality. We’ll start the new year with another Desktop release. In this month’s post, we’ll take a look back at the past year, recapping several of our favorite features, and taking a look at our big focus areas from this past year. If you’d like to see the features in action instead of reading about them, go ahead and download the file I used in this blog post.

Here’s the list of focus areas and features we’ll be recapping:


  • See data
  • Export data
  • See records
  • Trend lines
  • Dynamic reference lines
  • Analytics pane
  • Forecasting
  • Grouping
  • Binning
  • Clustering


  • Pick the location of your R environment
  • Support for Unicode characters and spaces in the R environment directory path
  • Support for localized number formats
  • R script data connector
  • Run R scripts in the Query Editor
  • Use your own R IDE
  • R-powered custom visuals

Visual formatting

  • Chart borders
  • Background images for reports and visuals
  • Formatting features for tables, matrices, and multi-row cards
  • Predefined table and matrix styles
  • Configure line chart data labels
  • Data label improvements for column and bar charts
  • Customize tooltips
  • Keyboard nudging for visuals
  • Snap to grid
  • Shape map
  • Custom maps for Shape map
  • ArcGIS maps

Slicers and filters

  • Search in slicers
  • Date slicer
  • Top N filters
  • Include/exclude
  • Dropdown slicer


  • Row-level security

Data connectivity

  • 22 new data sources
  • DirectQuery support for 6 additional data sources
  • Many improvements to existing connectors
    • URL validation for SharePoint List connector
    • Hierarchy support in SAP BW
    • Multi-select values for a given variable or parameter when using SAP BW and SAP HANA
    • Web page previews in the web connector

Data preparation

  • Append tables improvement
  • Conditional columns
  • Query dependencies view
  • Enhanced combine binaries experience
  • Improved function authoring and invoking

A big focal point for this year was analytics. At the start of the year, we began small, but by the end of the year, we were releasing major updates every month.

It started back in February when we released our first analytical features of the year, See data and Export data. With these two features, you can see the data we use to create the chart and then export it to do further analysis on it. This is especially valuable if you weren’t the report creator and want to learn more about the data.

6497e156 278d 4ae5 9c54 10b59915a592 Power BI Desktop – 2016 Year in Review

In April, we followed that up with the release of See records, which allows you to see all the data records behind a data point. Using this feature, you can focus on a given data point and see the detailed records of it.

In April, we also released trend lines on visuals, which are a quick and easy way to identify either a positive or negative trend in charts. In August, we expanded this functionality with dynamic reference lines, which allow you to create data bound lines in your charts, such as Average lines or Median lines, to help with analysis of your charts. We also gave these trend and value lines a home in a dedicated Analytics pane. You can use these features in combination to find insights in your data. For example, you can use the trend line and average line to see how net satisfaction changes based on deal size and how that compares to the average net satisfaction of our customers.

6b18fc50 de8c 4a0c 8222 6129687e7270 Power BI Desktop – 2016 Year in Review

In September, we added yet another feature to the Analytics Pane, forecasting. This forecasting feature uses built-in predictive forecasting models to automatically detect the step (monthly/weekly/annually) and seasonality in your data to provide forecasting results. This is a very powerful feature that can help you predict things such as your sales in the coming months.

be701725 7ef8 4673 857f 1f2883231320 Power BI Desktop – 2016 Year in Review

In October, we introduced the ability to group points on your visual to help you more easily explore and analyze related categories. Our grouping feature lets you create these ad-hoc groups, which allows you to easily associate related data points, group many small data points into an Other bucket, and refine the data in your chart to make analysis easier. Binning gives a similar experience for numerical and time fields. In November, we followed up with our clustering preview. Clustering will automatically find associated data points for you and create the groups for you to use throughout your report. This will frequently find associations you might not have realized existed in your data. One example is finding related products that have similar satisfaction and likely to purchase again ratings. You could then use those product clusters in other charts in your report to do further analysis.

cd77e287 0c0a 4959 83c5 20ed2757e7a2 Power BI Desktop – 2016 Year in Review

Power BI’s R integration is an integral part of our analytics offering. R is a programming language widely used by statisticians, data scientists, and data analysts to perform advanced analysis, clean data, and create visuals. Throughout this year, we’ve vastly improved upon the integration and added new ways to combine the power of R with Power BI.

The improvements started right away in our January release, where we made three improvements to the R integration. The first was the ability pick the location of your R environment from a drop down in the Options dialog. This location is shared between the R Script data connector and the R visuals. The other improvements allowed you to use Unicode characters and spaces as part of the R environment directory path and added support for localized number formats.

In March, we moved the R script data connector out of preview. This connector, which can be found under the Other list of connectors in Get data, allows you to connect to any data source supported by R and run R scripts to create a data model to use in your reports.

R integration was further extended to the Query Editor in July. From the Query Editor, you can select Run R script to enter an R script to transform and shape your data. Data cleansing is an important and necessary part of data analysis and greatly affects how easy it is to find insights later in the process. With this feature, you can use the power of R to do all the prep work necessary for your analysis.

In September, we updated our R visual integration to allow you to use your own R IDE to create your R visuals. When you select an R visual from the visual gallery, the Power BI editor will open. But if you have a favorite IDE or need a more powerful editor, you can use the pop out icon to open your editor. In October, we released another update to the R visuals experience. The R visuals integration adds advanced analytical depth and endless visualization flexibility to your Power BI reports. These reports can then be used by all business users on the Power BI web service, not just R experts. However, report creators don’t always have R script expertise, and with this update, we removed this barrier with the R-powered visuals gallery. With this gallery extension, you can download any pre-built R-powered custom visual and use it in your reports.

e58efdfe a462 476f a772 036b6f300cf6 Power BI Desktop – 2016 Year in Review

2016 has been a big year for visuals, with many new formatting features and a couple of new ways to visualize map data. A top priority for us this year was to allow you to customize your charts just the way you want, and we have many more features planned in this area for the coming year as well.

We started out in January adding the ability to add borders to your charts. The ability to add borders and choose the border’s colors was one of the first customizations added. Many users like to use this feature to align their charts and create a visible flow through the report. Also in January, we added the ability to control the background image for report pages and the plot area of charts, which is very useful when you are creating a report with a theme.

Tables and matrices are some of our most used visuals, and this year we started updating them with more formatting features. In April, we added many highly requested formatting features for tables, matrices, and multi-row cards. Starting with this release, you were able to start customizing the header, background, text, and grid colors of your tables, as well as grid lines, spacing, and padding. We followed up in July with predefined table styles and in August with predefined matrix styles. Using these predefined styles is a quick way to set the style of your chart and then do customization on top of that. We also added the ability to conditionally format a measure column in a table (May) and on matrices (November). This lets you quickly see low and high values in the visual. Additionally, in November, we released column formatting for both table and matrices, so you now have full control over your table column’s font and background color. You can use this feature to do things like change font color on conditionally formatted values so they are easier to read or use a bold background color on a key column that you want to draw attention to. We have many more table and matrix features planned for the coming year as well, so you will continue to see many improvements in this area.

4014bd9a c821 48aa 9304 cc04ead94f69 Power BI Desktop – 2016 Year in Review

Data labels and tooltips was another area we focused on this year. Tooltips are an essential component in providing more context and details to your viewers. With our May release, we added the ability to add additional context to your visual by adding more measures to the tooltip. You can look for another update in this area early next year. In June, we made an improvement to our line chart data labels. You can control the density of data labels on continuous line charts through a density property slider. In October, we extended this by providing a density control for data labels on column charts with a continuous axis. At the same time, we provided the ability to control data label position and orientation for column, bar, and waterfall charts.

adcc5a76 ed3f 4ba6 bfb1 a431f831727c Power BI Desktop – 2016 Year in Review

Once you have well formatted visuals, you also need to lay them out on your report. At the start of the year, you had to manually align them the best you could without any guidance. In May, we improved this by allowing you to move visuals around with the arrow keys on your keyboard, which allowed you to be much more precise. However, this was still a difficult, time-consuming task until our October release of Snap to grid, which made laying out your charts a breeze. You can now easily drag your visuals around the canvas and they snap to a grid layout.

We’ve also done a fair amount of work on maps this year. In June, we added a new map type to the Desktop in preview. Shape map’s main purpose is to show relative comparisons of regions on a map by coloring them differently instead of showing precise geographical location of data points as our other maps do. We followed up in July with the ability for Shape maps to consume custom maps in TopoJSON format, giving you full control over the map’s appearance. This is a powerful feature as it allows you to create non-geographical maps such as floor plans. In September, we added a fourth map type, ArcGIS maps for Power BI by Esri, which unlocked new capabilities. In collaboration with Esri, a leader in the geographic information systems (GIS) industry, you can now take geographic information to a whole new level. Some of the benefits of this new map include map backgrounds, reference layers, and fine grained control over how your data appears on the map.

aea49747 dd47 4e1d 994f a500c7205b56 Power BI Desktop – 2016 Year in Review

Many of the top asks from you in our UserVoice forums were around filtering and slicers. In the second half of this past year, we made great strides in this area.

It all started in June when we added search to slicers. Many times, the list of items in slicers is long, and it can be cumbersome to find the item you are looking for. By adding the search bar through the More () menu, it makes your slicers much more user friendly for anyone using your reports.

7a79f23e 4cea 4da6 90c1 26a70807afc1 Power BI Desktop – 2016 Year in Review

Our first new slicer type of the year, the Date Slicer, was released in October. Slicers are a great way for report viewers to quickly slice and dice the data they are seeing, and if you want to slice by dates, the date slicer’s slider and calendar picker is a very intuitive way to do this. It makes it very easy to filter your report to dates before or after a specific date.

03a6fbc6 73c5 4a0a ad92 c30f1d306547 Power BI Desktop – 2016 Year in Review

October was also a big release for filter functionality, with the release of our Top N filter and include/exclude of data points. The Top N filter is a great way to quickly filter your data to a given number of key data points, homing in on what matters most. Including and excluding data points through the right click menu is another great way to do this. You can choose to just include the outlier points or filter out points that are irreverent to the analysis you are doing.

Finally, in November, we release the much-anticipated dropdown slicer. Reporting canvas space can be a precious commodity, and slicers can take up too much of this space, especially if the list is very long. By switching the slicer type to Dropdown, you can conserve this much-needed space, while still providing your report viewers with a quick and easy way to filter the report data.

2ce667b1 55a6 4320 8b08 f440f152fc7c Power BI Desktop – 2016 Year in Review

A big concern for many report creators is making sure the data is secure and viewers only see what they are allowed to see. In the June update, we provided a way in the Desktop to control data access with row-level security. Row-level security allows you to secure your data at the row level with security filters based on roles you create. Using the Manage roles button in the Modelling tab, you can create roles then use DAX expressions to control what each role sees. Then, once you publish it up to the web, you can assign users to each role.

16c7b2d9 30a6 4c89 94d2 bc929bc7c9f6 Power BI Desktop – 2016 Year in Review

Over the course of this past year, we’ve vastly improved data connectivity in the Power BI Desktop. We’ve added support for 22 new data sources, such as the March release of our SAP Business Warehouse connector, the July release of the Amazon Redshift connector and Impala connector, and the August release of our Snowflake connector. We’ve also added DirectQuery support for 6 data sources: Oracle, Teradata, Amazon Redshift, Impala, Snowflake, and Spark.

We’ve also made many improvements to existing connectors based on customer feedback. In February, we improved the SharePoint List connector, which lets you provide the URL to your SharePoint site root and select the SharePoint lists you want to connect to. The improvement was adding URL validation when the URL provided is invalid. in July, one of many months with SAP BW improvements, we saw the addition of hierarchy support in SAP BW and the ability to multi-select values for a given variable or parameter when using SAP BW and SAP HANA. August included another big improvement with the addition of Web page previews in the Web connector, which lets you actually select the tables you want to connect to in context of the web page.

With over 50 different data connectivity improvements this year, this is just a snapshot of the work we did in this area. We will continue this pace next year as we strive to empower you to easily connect to any data you want in the manner that you want.

We pride ourselves on having very powerful built-in data preparation features in the Desktop that make it very easy to clean and transform your data before modeling and reporting on it. We put a lot of effort into continuing to innovate here, and during this year, we released almost 60 improvements in this area.

The first exciting addition of the year was in January when we extended the append tables experience to allow for more than two tables in one go. This new mode is extremely powerful for advanced scenarios where you want to quickly append many tables in one shot.

In April, we released conditional columns. This update made it extremely easy for you to create new columns in your queries based on values from a different column. This is very useful if you are trying to bucket your data into different categories.

ce957598 f008 41ff bbf5 2c9c32ec973a Power BI Desktop – 2016 Year in Review

One of the most popular requests in the data preparation area was the Query dependencies view, which we released in September. By using this dialog, you can quickly and easily understand the dependencies across all your queries in your Desktop file. This helps you know what other queries may be affected when you make an update to a given query.

Many of you take advantage of one of our folder-like connectors, such as Folder, SharePoint folder, HDFS, etc. to combine multiple files with the same schema into one table. Because of the popularity, we enhanced the combine binaries experience in November to make it much more intuitive. Now when you use this transformation, it will analyze each input file to determine the file format to use and then let you select a specific object to use from the first file to extract.

Between September and October of this year we released 3 improvements around function authoring and invocation. First in September, we added the Invoke Custom Function operation to the Add Column tab in the ribbon. This new operation makes it seamless for users to invoke a custom function over each row of a table. The dialog gives you the option to pick one of the custom functions in the current file and specify either static values or other columns as input arguments. We followed this up in October with support for table parameters in the Invoke Function experience. You can now use parameters of type “Table” when invoking either a built-in or user-defined function. Lastly, in November, we made it easier to update function definitions without the need to maintain the underlying M code. When a function definition is created based on another query, a link is created between the original query and the newly generated function so that they remain synchronized. When the original query is modified with additional steps, the linked function will be automatically updated for you.

Across the entire Desktop, we made huge leaps forward this past year. And we aren’t planning on slow down. We have many new features planned for the coming year that we are very excited to share with you.

Thank you for all the support and feedback you’ve given throughout 2016. We value your input and as you can see, work hard to integrate your needs into our plans. Please don’t forget to vote for other features that you would like to see in the Power BI Desktop in the future, and we’ll see you again in the new year for the January Desktop release.

Happy New Year!

This article passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at
Recommended article: The Guardian’s Summary of Julian Assange’s Interview Went Viral and Was Completely False.

Microsoft Power BI Blog | Microsoft Power BI

How the Right Marketing Automation Platform Saves You Time and Money

blog title bluenose keri keeling 351x200 How the Right Marketing Automation Platform Saves You Time and Money


You’re the VP of Customer Success and Operations. What does that entail?


My career has been in customer success, and that’s my primary focus at Bluenose. My goal is to grow and retain loyal customers and ensure that our software delights them and exceeds their needs.

In 2015, I also took on the responsibility of leading our marketing efforts. Bluenose produces a lot of rich content that supports our users – customer success stories, weekly blog posts about best practices, and more. I wanted to leverage these assets to nurture and engage our prospects and customers. I inherited Marketo as our marketing automation platform, so that was the tool that was available to help me.


What were you hoping Marketo would provide?


I knew I needed software that would streamline the process of building campaigns and reporting on their success. And I’m not a technical person, so it also had to be easy to learn and use. The faster I could implement my marketing strategy, the more time I would have to enable the success of our customers and prospects.


How was your experience with Marketo?


I found Marketo’s interface difficult to use. I felt like I had to spend an entire day doing something I should be able to accomplish with three clicks. And sometimes there were several different ways to accomplish a task, so it was hard to remember what approach was best. It was all very challenging. I might have fared better if I were an expert in HTML, CSS, and JavaScript, but I’m not.


What did you do?


I began looking for a new platform. My brief experience with Marketo brought my needs into sharp focus, and clarified what I expected from marketing automation software. It had to be easier to use, and I wanted to know that the support I would need was available.


How did you choose Act-On?


We created a comprehensive list of requirements that covered everything from customer experience to campaign performance reports, but the most important was usability and how independently I could work in the software.

Bluenose is a fast-growing company and I needed to be nimble, so my litmus test was to see how quickly I could build a landing page and deploy an email in Act-On without any prior experience or help. And it worked. In less than an hour I had a branded email template and a fully functioning landing page in place. The fact that Act-On passed my test with flying colors is what sold me on the product.

This article passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at
Recommended article: The Guardian’s Summary of Julian Assange’s Interview Went Viral and Was Completely False.

Act-On Marketing Action Blog

Late Night Political Humor

[Jokes from Dec. 13, 2016]

“This morning, Kanye West actually showed up to meet with Donald Trump at Trump Tower. People were shocked — they didn’t expect those two to meet until the first presidential debate in 2020.” – Jimmy Fallon

“Today, Donald Trump met with Kanye West. It’s always cool when two future presidents hang out.” – Conan O’Brien

“Donald Trump met with Kanye West today — what an amazing thing to see. Our next two presidents side by side.” – Jimmy Kimmel

“Kanye West met with Donald Trump at Trump Tower today. No word on what they interrupted each other about.” – Seth Meyers

“This morning, the president-elect met at Trump Tower with Kanye West. You can tell it was a high-powered meeting because Kanye wore his formal sweat suit.” – Stephen Colbert

“Kanye tweeted, ‘It’s important to have a direct line of communication with our future president if we truly want change.’ Yeah, that’s great for you, but what about us?” – Jimmy Kimmel

“The big story today was Trump’s meeting with Kanye West. The two met in Trump’s apartment and then took photos and answered questions in the lobby. When asked what they talked about, Trump had kind of a weird answer: ‘We’re just friends’. Just friends? What happened up in that apartment?” – James Corden

“Kanye needs to stay close to Donald Trump because at this point he’s the only person who can afford to buy his sneakers.” – Jimmy Kimmel

“Obviously, it’s a security risk gathering the two most powerful American narcissists in the same room. Just for security reasons, they had to keep Shia LeBeouf in an undisclosed location for continuity of government.” – Stephen Colbert

“Mitt Romney tweeted that it was ‘an honor’ to be considered for secretary of state. Romney then tweeted, ‘Oh, and has anyone seen my dignity?’” – Conan O’Brien

“A friend of Donald Trump’s said that Trump dangled the office of secretary of state to Mitt Romney to ‘torture him’. You can watch the whole thing in the new series ‘White Billionaire Pranks.’” – Conan O’Brien

“Jeb Bush told reporters today that he does not think Russia ‘influenced’ the election. And if anybody knows about not influencing elections, it’s Jeb Bush.” –Seth Meyers

“Trump announced he’s nominating Exxon-Mobil CEO Rex Tillerson to be his secretary of state, even though Rex supposedly has a friendly relationship with Vladimir Putin. Today, Rex said, ‘Putin and I aren’t friends,’ while Putin said, ‘That’s SO something Rex would say.’” – Jimmy Fallon

“On Twitter Trump announced his nominee for secretary of state, Rex Tillerson, CEO of a small mom-and-pop business called Exxon-Mobil. He announced he’s changing his slogan from ‘drain the swamp’ to ‘fill ‘er up!’” – Jimmy Kimmel

“Today Donald Trump chose Rex Tillerson, Exxon-Mobil CEO, as his secretary of state. I assume from now on, all gas stations are official U.S. embassies, which is perfect for any refugees who are seeking asylum and maybe a Slim Jim.” – Stephen Colbert

“Donald Trump has continued to make more surprising appointments in his cabinet. This morning, Trump chose the CEO of Exxon Mobil, Rex Tillerson, as his secretary of state. Because, you know, when you think responsible, ethical and trustworthy, you think Exxon-Mobil.” – James Corden

“Tillerson is an interesting pick. For one thing, he’s an oil main who believes in climate change. Well, of course he believes in it! He’s from Exxon — he INVENTED it. It’s pride of ownership. It’s his baby!” – Stephen Colbert

“Former Texas Gov. Rick Perry has been named Donald Trump’s pick for secretary of energy. And I have to say, he is the most qualified candidate from that particular season of ‘Dancing with the Stars.’” – Seth Meyers

“Donald Trump’s pick for secretary of state, Rex Tillerson, was once the president of the Boy Scouts of America. Or as Donald Trump calls that, ‘government experience’.” – Conan O’Brien

“Rudy Giuliani said today that he had ‘too much going on’ to accept a cabinet appointment from Donald Trump. Added Giuliani, ‘These pigeons aren’t gonna yell at themselves!’” – Seth Meyers

“These cabinet appointments are something else. I can’t wait to see who he picks to actually be president of this country.” – Jimmy Kimmel

“There are only 11 shopping days left until Christmas, and only 37 shopping days to fill your doomsday bunker with canned goods before Trump becomes the president.” – James Corden

“A NASA scientist has warned that Earth is unprepared for a surprise asteroid hit, and said, ‘There’s not a hell of a lot we can do about it.’ So, ladies and gentlemen, happy holidays!” – Conan O’Brien

share save 171 16 Late Night Political Humor

This article passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at
Recommended article: The Guardian’s Summary of Julian Assange’s Interview Went Viral and Was Completely False.

Political Irony

Your One Stop Shop for CRM Resources for Microsoft Dynamics 365

crm resources 300x225 Your One Stop Shop for CRM Resources for Microsoft Dynamics 365

To end this year armed with the CRM knowledge you need to make 2017 a soaring success, make sure you are in-the-know about the latest and greatest updates on Microsoft Dynamics 365. To make it easy for you, we’ve put together of list of 3 CRM for Dynamics 365 resources that you can dive into today!

1. UCOMING WEBINAR SERIES – CRM for Dynamics 365
– CRM for Dynamics 365: What’s New Overview  – Thursday, January 5, 1:00-1:30 pm

– Service CRM for Dynamics 365: What’s New in Field Service – Tuesday, January 10, 1:00-1:30 pm

– CRM for Dynamics 365: App for Outlook – Thursday, January 12, 1:00-1:30 pm

– CRM for Dynamics 365: Relationship Insights – Tuesday, January 17, 1:00-1:30 pm

– CRM for Dynamics 365: Mobile Features – Thursday, January 19, 1:00-1:30 pm

– CRM for Dynamics 365: What’s New in Portals – Tuesday, January 24, 1:00-1:30 pm-

– CRM for Dynamics 365: Designing the User Experience – Thursday, January 26, 1:00-1:30 pm

– CRM for Dynamics 365: Learning Paths – Tuesday, January 31, 1:00-1:30 pm

– CRM for Dynamics 365: What’s New in Project Service – Thursday, February 2, 1:00-1:30 pm

2. WEBINAR ON DEMAND – What is Dynamics 365 and What Does it Mean for Me?

123016 1814 YourOneStop1 Your One Stop Shop for CRM Resources for Microsoft Dynamics 365

This quick, 26-minute recorded webinar will cover the following:What exactly is Dynamics 365?
– How customer relationship management (CRM) works within the Dynamics 365 model
– Licensing options and details
– What Dynamics 365 means for existing PowerObjects customers
– What Dynamics 365 means if you’re NOT an existing customer
– Enhancements that come with Dynamics 365
– Timelines for rolling out Dynamics 365
– How to get rolling with Dynamics 365

3. Flowchart for current Dynamics CRM users: download it now!

123016 1814 YourOneStop2 Your One Stop Shop for CRM Resources for Microsoft Dynamics 365

To learn more, check out our Dynamics 365 landing page – your destination for Dynamics 365 content. If you have questions or would you like to see other content featured on our Dynamics 365 page, let us know!

Happy CRM’ing!

This article passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at
Recommended article: The Guardian’s Summary of Julian Assange’s Interview Went Viral and Was Completely False.

PowerObjects- Bringing Focus to Dynamics CRM

Evaluating Shared Expressions in Tabular 1400 Models

In our December blog post; Introducing a Modern Get Data Experience for SQL Server vNext on Windows CTP 1.1 for Analysis Services, we mentioned SSDT Tabular does not yet support shared expressions, but the CTP 1.1 Analysis Services engine already does. So, how can you get started using this exciting new enhancement to Tabular models now? Let’s take a look.

With shared expressions, you can encapsulate complex or frequently used logic through parameters, functions, or queries. A classic example is a table with numerous partitions. Instead of duplicating a source query with minor modifications in the WHERE clause for each partition, the modern Get Data experience lets you define the query once as a shared expression and then use it in each partition. If you need to modify the source query later, you only need to change the shared expression and all partitions that refer to it to automatically pick up the changes.

In a forthcoming SSDT Tabular release, you’ll find an Expressions node in Tabular Model Explorer which will contain all your shared expressions. However, if you want to evaluate this capability now, you’ll have to create your shared expressions programmatically. Here’s how:

  1. Create a Tabular 1400 Model by using the December release of SSDT 17.0 RC2 for SQL Server vNext CTP 1.1 Analysis Services. Remember that this is an early preview. Only install the Analysis Services, but not the Reporting Services and Integration Services components. Don’t use this version in a production environment. Install fresh. Don’t attempt to upgrade from previous SSDT versions. Only work with Tabular 1400 models using this preview version. For Multidimensional as well as Tabular 1100, 1103, and 1200 models, use SSDT version 16.5.
  2. Modify the Model.bim file from your Tabular 1400 project by using the Tabular Object Model (TOM). Apply your changes programmatically and then serialize the changes back into the Model.bim file.
  3. Process the model in the preview version of SSDT Tabular. Just keep in-mind that SSDT Tabular doesn’t know yet how to deal with shared expressions, so don’t attempt to modify the source query of a table or partition that relies on a shared expression as SSDT Tabular may become unresponsive.

Let’s go through these steps in greater detail by converting the source query of a presumably large table into a shared query, and then defining multiple partitions based on this shared query. As an optional step, afterwards you can modify the shared query and evaluate the effects of the changes across all partitions. For your reference, download the Shared Expression Code Sample.

If you want to follow the explanations on your own workstation, create a new Tabular 1400 model as explained in Introducing a Modern Get Data Experience for SQL Server vNext on Windows CTP 1.1 for Analysis Services. Connect to an instance of the AdventureWorksDW database, and import among others the FactInternetSales table. A simple source query suffices, as in the following screenshot.

FactInternetSalesSourceQuery Evaluating Shared Expressions in Tabular 1400 Models

As you’re going to modify the Model.bim file of a Tabular project outside of SSDT, make sure you close the Tabular project at this point. Then start Visual Studio, create a new Console Application project, and add references to the TOM libraries as explained under “Working with Tabular 1400 models programmatically” in Introducing a Modern Get Data Experience for SQL Server vNext on Windows CTP 1.1 for Analysis Services.

The first task is to deserialize the Model.bim file into an offline database object. The following code snippet gets this done (you might have to update the bimFilePath variable). Of course, you can have a more elaborate implementation using OpenFileDialog and error handling, but that’s not the focus of this article.

string bimFilePath = @”C:\Users\Administrator\Documents\Visual Studio 2015\Projects\TabularProject1\TabularProject1\Model.bim”;
var tabularDB = TOM.JsonSerializer.DeserializeDatabase(File.ReadAllText(bimFilePath));

The next task is to add a shared expression to the model, as the following code snippet demonstrates. Again, this is a bare-bones minimum implementation. The code will fail if an expression named SharedQuery already exists. You could check for its existence by using: if(tabularDB.Model.Expressions.Contains(“SharedQuery”)) and skip the creation if it does.

tabularDB.Model.Expressions.Add(new TOM.NamedExpression()
    Kind = TOM.ExpressionKind.M,
    Name = “SharedQuery”,
    Description = “A shared query for the FactInternetSales Table”,
    Expression = “let”
        +      Source = AS_AdventureWorksDW,”
        +      dbo_FactInternetSales = Source{[Schema=\”dbo\”,Item=\”FactInternetSales\”]}[Data]”
        +  “in”
        +      dbo_FactInternetSales”,

Perhaps the most involved task is to remove the existing partition from the target (FactInternetSales) table and create the desired number of new partitions based on the shared expression. The following code sample creates 10 partitions and uses the Table.Range function to split the shared expression into chunks of up to 10,000 rows. This is a simple way to slice the source data. Typically, you would partition based on the values from a date column or other criteria.

for(int i = 0; i < 10; i++)
    tabularDB.Model.Tables[“FactInternetSales”].Partitions.Add(new TOM.Partition()
        Name = string.Format(“FactInternetSalesP{0}”, i),
        Source = new TOM.MPartitionSource()
            Expression = string.Format(“Table.Range(SharedQuery,{0},{1})”, i*10000, 10000),

The final step is to serialize the resulting Tabular database object with all the modifications back into the Model.bim file, as the following line of code demonstrates.

File.WriteAllText(bimFilePath, TOM.JsonSerializer.SerializeDatabase(tabularDB));

Having serialized the changes back into the Model.bim file, you can open the Tabular project again in SSDT. In Tabular Model Explorer, expand Tables, FactInternetSales, and Partitions, and verify that 10 partitions exist, as illustrated in the following screenshot. Verify that SSDT can process the table by opening the Model menu, pointing to Process, and then clicking Process Table.

ProcessTable 1024x673 Evaluating Shared Expressions in Tabular 1400 Models

You can also verify the query expression for each partition in Partition Manager. Just remember, however, that you must click the Cancel button to close the Partition Manager window. Do not click OK –   with the December 2016 preview release, SSDT could become unresponsive.

Congratulations! Your FactInternetSales now effectively uses a centralized source query shared across all partitions. You can now modify the source query without having to update each individual partition. For example, you might decide to remove the ‘SO’ part from the values in the SalesOrderNumber column to get the order number in numeric form. The following screenshot shows the modified source query in the Advanced Editor window.

ModifiedQuery 1024x390 Evaluating Shared Expressions in Tabular 1400 Models

Of course, you cannot edit the shared query in SSDT yet. But you could import the FactInternetSales table a second time and then edit the source query on that table. When you achieve the desired result, copy the M script into your TOM application to modify the shared expression accordingly. The following lines of code correspond to the screenshot above.

tabularDB.Model.Expressions[“SharedQuery”].Expression = “let”
    +     Source = AS_AdventureWorksDW,”
    +     dbo_FactInternetSales = Source{[Schema=\”dbo\”,Item=\”FactInternetSales\”]}[Data],”
    +     #\”Split Column by Position\” = Table.SplitColumn(dbo_FactInternetSales,\”SalesOrderNumber\”,Splitter.SplitTextByPositions({0, 2}, false),{\”SalesOrderNumber.1\”, \”SalesOrderNumber\”}),”
    +     #\”Changed Type\” = Table.TransformColumnTypes(#\”Split Column by Position\”,{{\”SalesOrderNumber.1\”, type text}, {\”SalesOrderNumber\”, Int64.Type}}),”
    +     #\”Removed Columns\” = Table.RemoveColumns(#\”Changed Type\”,{\”SalesOrderNumber.1\”})”
    + “in”
    +     #\”Removed Columns\””;

One final note of caution: If you remove columns in your shared expression that already exist on the table, make sure you also remove these columns from the table’s Columns collection to bring the table back into a consistent state.

That’s about it on shared expressions for now. Hopefully in the not-so-distant future, you’ll be able to create shared parameters, functions, and queries directly in SSDT Tabular. Stay tuned for more updates on the modern Get Data experience. And, as always, please send us your feedback via the SSASPrev email alias here at or use any other available communication channels such as UserVoice or MSDN forums. You can influence the evolution of the Analysis Services connectivity stack to the benefit of all our customers.

This article passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at
Recommended article: The Guardian’s Summary of Julian Assange’s Interview Went Viral and Was Completely False.

Analysis Services Team Blog

Microsoft previews SQL Server on Linux, opens features across editions

Microsoft this week made SQL Server 2016 Service Pack 1 generally available, and it released an initial public…

preview of SQL Server running on Linux. Both moves are intended to promote wider use of the flagship database, as well as its more recent feature additions.

SQL Server 2016 SP1 adds a consistent programming model across the different editions of the software. The programming updates mean several advanced features previously limited to solely the top-of-the-line SQL Server Enterprise Edition are now supported in some form on SQL Server Standard Edition. Even SQL Server Express developers can get in on the action, as the wider programming model encompasses that platform, too.

Meanwhile, the support for SQL Server on Linux now openly available in preview mode moves the database onto an operating system commonly found in the modern enterprise, opening a whole new field for deployment.

Among the features now supported across the Enterprise, Standard and Express versions of the software are data warehouse partitioning and data compression, as well as Always Encrypted row-level security and data masking.

All kinds of development shops have grappled with the problem of maintaining different Enterprise and Standard versions of their software, as well as sometimes spotty use of compression and encryption features. However, the effects have been especially felt among independent software vendors (ISVs), according to Mike Walsh, founder and owner of Straight Path IT Solutions, a SQL Server consultancy and managed service provider based in Milton, N.H.

“A lot of vendors were using compression, but their customers had to buy SQL Enterprise to use that feature,” he said. “ISVs had to have two versions of their software to deal with it.”

But it isn’t just ISVs that felt the problem. The split in SQL Server affected users, too — ones who might have not needed the Enterprise Edition’s high scalability, but had to buy it to get features like row-level encryption, an important factor for compliance, said Walsh, who is also a Microsoft SQL Server MVP and a blogger.

Read the SQL Server small print

There are caveats. Still not included across Standard and Express are various high availability, data management and business intelligence features that Microsoft has brought to SQL Server Enterprise, such as its Always On Availability Groups, Master Data Services and Data Quality Services technologies.

It’s true that with a common development model across editions, programmers can work with a wider set of SQL Server features. But jobs that need highly scaled processing likely will still remain the province of SQL Server Enterprise.

Developers can program in-database analytics with R, in-memory online transaction processing (OLTP) and column-store data warehousing jobs — all highly touted features of SQL Server 2016 — but the maximum number of processor cores supported are four and 24 for Express and Standard, respectively. Meanwhile, SQL Server Enterprise Edition supports an unlimited number of processor cores.

For Walsh and others, however, the support for a common programming model is an important step forward.

“Companies have begun doing column stores and OLTP in memory, and I think adoption will grow. These features allow better data retrieval and analytics,” he said. “But I’m actually more excited about the broader use of immediately practical features like encryption and table partitioning, no matter how many cores are used.”

Driving new apps on SQL Server

Microsoft’s efforts on SQL Server 2016 SP1 will ease use of advanced features and remove obstacles developers now face, according to Scott Guthrie, executive vice president of the company’s cloud and enterprise technologies group. Guthrie described the SQL Server enhancements, and a wide array of other product initiatives, at Microsoft’s Connect(); 2016 developers conference in New York.

“Cool programming model features, like in-memory database analytics support, as well as Always Encrypted support, have only been available with the enterprise editions of SQL Server,” Guthrie said. “This has been a barrier for developers and ISVs because it requires always having the most expensive version of SQL [Server].”

Among the new initiatives Guthrie discussed were Docker support for SQL Server, general availability of Azure Data Lake Analytics and Store, and a preview of Visual Studio for Mac, the latter a clear sign the company is working to fulfill promises for greater openness.

SQL on Linux a nod to openness

But the biggest nod to openness may be the Linux port. At the conference, Microsoft issued the first public preview of its promised SQL Server on Linux offering, which will be part of the next release of the database, currently referred to as SQL Server vNext. “We think it will enable a whole new opportunity to drive new data applications on SQL [Server],” Guthrie said.

SQL Server’s entry into Linux opens the database to use in areas Microsoft previously ceded to other players, according to Merv Adrian, a Gartner analyst.

“For users who will not use Windows, for whatever reason, SQL Server is a credible and capable option,” he said. “And for those using both environments, it provides an opportunity to leverage existing skills and enhance portability.”

For Straight Path’s Walsh, SQL Server on Linux is an affordable, feature-rich alternative to other offerings — and proof Microsoft is ready to adjust to meet wider user needs.

“These moves show that when Microsoft says they will meet you where you are, they mean it,” Walsh said.

This article passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at
Recommended article: The Guardian’s Summary of Julian Assange’s Interview Went Viral and Was Completely False.

Microsoft SQLServer news and trends

How to get the real part of a expression

I’m trying to get the real part of a expression, but I failed. It looks like this. vfTQ8 How to get the real part of a expression

Does anyone have a solution? By the way, it is quite weird because when I simplify this: CCFlu How to get the real part of a expression Mathematica returns the right answer, but I can’t tell the big difference between the two expressions.

Here is my code:

vectorVr = Array[Subscript[vr, #] &, 9];
vectorVi = Array[Subscript[vi, #] &, 9];
$  Assumptions = 
  Join[vectorVr, vectorVi] \[Element] Reals;

FullSimplify[ (0.` + 0.0576` I) (I Subscript[vi, 1] - 
  I Subscript[vi, 4] + Subscript[vr, 1] - Subscript[vr, 
  4]) (-I Subscript[vi, 1] + I Subscript[vi, 4] + Subscript[vr, 
  1] - Subscript[vr, 4]) // Expand // Re]

This article passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at
Recommended article: The Guardian’s Summary of Julian Assange’s Interview Went Viral and Was Completely False.

Recent Questions – Mathematica Stack Exchange

Video: Analytics Spur the Entrepreneurial Spirit at Georgetown

Analytics & Optimization Video: Analytics Spur the Entrepreneurial Spirit at Georgetown

Analytics at Georgetown Video: Analytics Spur the Entrepreneurial Spirit at Georgetown


Ever wonder how students use analytics to become entrepreneurs?

At Georgetown University in Washington, D.C., Students of Georgetown, Inc. – commonly known as “The Corp” – is the largest entirely student-run non-profit corporation in the world, with seven subsidiary companies generating annual revenues in excess of $ 5 million. As participants in the FICO Academic Engagement Program, Georgetown students were given the opportunity to analyze Big Data from their seven storefronts using some of the industry’s most widely used analytics tools. Watch the video to find out how The Corp leveraged their findings from FICO analytics to monitor customer behavior and improve sales.

Leave a comment

This article passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at
Recommended article: The Guardian’s Summary of Julian Assange’s Interview Went Viral and Was Completely False.