10 Things I Learned Working with the New BI Semantic Model (TABULAR)

  1. Use DIVIDE in measure calculations instead of a/b if there is any chance the denominator can be null or zero.  DIVIDE will automatically clean up divide by zero and other issues that normally throw errors out in Excel.
  2. Names must be unique in the model.  That means if you want a measure called ‘Sales’, you can’t have a column called ‘Sales’ in your data feed even if you are hiding it from client tools.  I had over 130 measures in a model that I built and what I did was try to perform as many calculations in the views that feed the model as possible.  I used a ‘m’ prefix on all those measure names in the data feed so that when I built my “sum([field])” measures I could use the name.  So I fed ‘mSales’ into the model so that I could create a measure called ‘Sales’.  Sales:=sum(‘Fact’[mSales])
  3. Usual Star Schema design patterns work well where the “fact” table has keys and measures and lookup (“dimension”) tables have the attributes.  When my model was done, I had 27 dimensions and 130 measures over 4 million rows and the model performed pretty well.
  4. Lots of features of normal OLAP cubes are available (Measure Folders, Actions) but are not supported in the tool used to build the models.  Use BIDS Helper from codeplex to access these features.  It has a lot of other features that are very helpful as well.   http://bidshelper.codeplex.com/
  5. To keep the model clean, you should re-use the first connection whenever you want to change or add tables to your model.  Click on ‘Model’, ‘Existing Connections’ instead of ‘Import from Data Source…’ after the model has been started.
  6. Some menu items and features are only available in the Grid view and the Diagram view.  To work with measures, you have to be in the Grid view.  To establish relationships, you have to be in the diagram view.
  7. You can edit measure calculations by hand in the Code View of the BIM file, but when you start editing, Visual Studio will try to auto-indent.  The script text used to build these measures is quirky.  You must remove all tabs that the editor throws in before saving.
  8. You can document your cubes whether they are OLAP or TABULAR using the schema queries built into the cube.  Go into SQL Management Studio and connect to your cube (OLAP or TABULAR), then start a new MDX query.  It is especially useful in large models because the measure expression is also in the table so it is pretty easy to give the client a list of measures and calculations for them to review.
    select * from $system.mdschema_measures
    select * from $system.mdschema_dimensions
    There are lots more here: http://bennyaustin.wordpress.com/2011/03/01/ssas-dmv-queries-cube-metadata/
  9. There appears to be a bug in the way Excel works with Tabular BI models. If you hide table in the model view, it will not appear in the list of dimensions and measures that normally appears. It will however show up in the “Show Fields” drop down box that appears at the top of the PivotTable fields dialog.
  10. If you change the schema of a table in your model, you have to make sure to select that column to include it in your model. Open the grid view then select the tab associated with the table that changed in your data source. Then select “Table Properties”. Your new columns should appear in the preview window. If you notice, there are small check boxes in the left corner next to each field name. For the new fields, be sure to check the box so that they will be added to your model.



Thursday, October 30
2 PM – 3 PM EDT

Do you feel comfortable that your data will not be leaked or lost? Are you comfortable that your data will not be turned over to outside authorities without your prior knowledge and approval?

In this webinar, you will discover:

  • Why your data is likely to be more secure in a cloud services provider’s facility than in your own data center.
  • How to address the fear and lack of trust with good policy, quality control and correct application architecture and design.

Don’t be a slave to fear. Take a calm and realistic look at how your organization should use the cloud.


Business Anywhere

Mobilize Your Business with Nintex

Friday, October 3, 2014

2 PM – 3 PM EDT


The quickest return on investment when it comes to SharePoint projects is business process automation. During this presentation, TCSC will cover how Workflow and Mobile enabled forms have provided measurable efficiency gains for many business units. We will showcase how mobile devices and simple drag and drop user interfaces can be used to create extremely powerful workflows and forms on any device.




Internet Explorer 8 users and the light version of Outlook Web App

Starting June 30, 2014, Internet Explorer 8 will support only the light version of Outlook Web App. Changes will take effect during the weeks following June 30th.

If your organization uses Internet Explorer 8 to access older web apps and services, you might want to upgrade to Internet Explorer 11 and try Enterprise Mode for Internet Explorer 11. Enterprise Mode helps provide better backward compatibility for older web apps.

For more information I encourage you to visit our web browser guidance at the “Office 365 System Requirements” website by going here.


Sentiment Analysis

how's your brand

Sentiment Analysis Webinar

Thu, Jun 19, 2014 

2 PM – 3 PM EDT


Many organizations have come to realize that social media sites can provide a wealth of information related to how customers and others perceive their products and brands. In this webinar, we will explore an example strategy using technology to mine the mass amounts of data available to show you how to take advantage of this market intelligence. In what has become a billion dollar field, the value that text analytics delivers to a spectrum of industries, including life sciences and healthcare, marketing, market research and customer experience, government and security, and media + publishing is astonishing.  But the power of this intelligence isn’t limited to large organizations with endless budgets. In fact, we will walk you through an example using tools that are already accessible to you.


If you cannot attend the live event, register to receive the recording and special offering