- 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.
- 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])
- 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.
- 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/
- 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.
- 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.
- 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.
- 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/
- 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.
- 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.