SSAS Tabular: How to mark a table as Date Table?

Standard

Problem:

For time intelligence in SSAS Tabular models, it’s important to mark a date table. So, how do you do that?

Solution:

Here are the steps:

1. After you have the data imported in a table, in the model designer, select the date table

2. Also, make sure that necessary relationships are created between date table and fact tables.

3. Now, on the menu bar, select Table > Date > Mark as Date Table

SSAS Tabular mark as date table4. A dialog box should ask you for a unique identifier of the date table, so it could be a date column which has the unique columns for the date table that you imported.

Also, make sure that the unique identifier does not have blank values because the model designer will not allow you to select it as your unique identifier and so you may have to go back to your table properties and put a logic to filter out blank values or make sure that the a row with blank value has a value assigned to it.

After you do that, you’re all set to go! I hope this helps.

Advertisements

How to add additional Columns to an Existing Table in a Power Pivot Model?

Standard

Problem:

There’s a Power Pivot Model. It imports some number of columns from a table. Now, there’s a need to import an additional column to this table. How do you do that?

Solution:

Here are the steps:

1. Open Power Pivot Model.

2. Go to Design Tab.

3. Click on Table Properties:

Power Pivot Table Properties

4. From here, You can add additional columns:

Add Columns Table Power Pivot5. click on save and that should add the column to your model:

Power Pivot Edit Connections TableFAQ:

#1: I can’t see what you see inside Design Tab. What do I do?

Make sure you have the latest Power Pivot version. You can read this: http://parasdoshi.com/2013/06/04/excel-2010-how-to-check-the-installed-version-of-powerpivot-and-whats-the-latest-version/

#2: How about changing the data source & pointing to new cube/database? It’s very helpful when you switch between QA, Dev or PROD servers. Here are the steps:

you can do that too! Go to Design > Existing connections > Select Connection > Edit > Make changes > Test it! > Save > “Refresh”. Any Problems? No? Great. That’s Done!

Related Posts:
How to add a column in an imported Table while developing SQL Server 2012 Analysis services Tabular Model

Getting Started: Implementing Dynamic Security with row filters in Tabular Models

Standard

In this blog post. I’ll help you get started w/ implementing dynamic security with row filters in Tabular Models.

Scenario:

We’ve users that connect to a Tabular Model via Excel for Data Analysis purposes. One of the analysis that they do is Countries VS. Total Margin:

tabular models countries total margin profit

What we want to do is restrict someone from Europe to see data only about France, Germany and United Kingdom

Solution:

1) Open Tabular Model in SSDT (SQL Server Data Tools)

2) Toolbar > Model > Roles

tabular models BISM roles

3)  Role Manager > NEW > change Name to Europe and Permissions to Read

4) Under the Row Filters, for the Geography Table, enter the following code:

=[Country Region Name]=”France” ||  [Country Region Name]=”Germany” || [Country Region Name]=”United Kingdom”

How to edit code for your scenario? change the [country region name] to your column(s) and also change the values

role tabular dax filter ssdt code

5) Click OK

6) Now let’s test it!

7) Toolbar > Model > Analyze in Excel

8) Select the role Europe

dynamic row filter in tabular models9) Click ok.

10) From Pivot Table, Select Margin & Countries:

DAX tabular models dynamic row filters based on location

11) As you can see, since the role Europe was selected for testing purpose in step 8 –  ONLY France, Germany and UK data is shown in our test! This means that our row filters are working perfectly!

I hope this tutorial helps you get started on implementing dynamic security in Tabular models.

Resource:

WhitePaper: Securing the Tabular BI Semantic Model

 

How to add a column in an imported Table while developing SQL Server 2012 Analysis services Tabular Model

Standard

Do not get intimidated by the long title – it’s not as complex as it sounds! So what am i talking about? Recently, I was developing SQL Server 2012 analysis services Tabular Model (referred to as ‘model’ from now on) and during development I realized that I need to import a column for a table. Let me give an example of what I am talking about:

Consider In the data source there’s a table named ‘Date’ with 15 columns, But for the model I needed only few columns – So I imported only 5 (out of 15) columns from this table:

DateKey
EnglishMonthName
MonthNumberOfyear
CalendarQuarter
CalendarYear

Now During development I figured I need one more column which I had not imported before. Let’s call it FullDateAlternateKey

So How do you add the column ‘FullDateAlternateKey’ in your model during development?

Turns out, It is very simple. This is how you do it:

1. Select the Table in your model designer
2. Go to Table properties.
(If you are not able to see the properties window. Go to View > Properties Windows OR Just select the table and press F4)
3. This is how a properties windows for a Table looks like

properties windows table sql server 2012 tabular mode analyis services

4. Now here click on ‘..’ button after you select the ‘Source Data’ field from the Basic Properties
Source Data edit table sql server 2012 tabular mode analyis services
5. You’ll see a ‘Edit Table Properties’ dialog box. Here you check the column that you wish you import to the model. In my case, I checked the box for the column ‘FullDateAlternateKey’:analysis services check the box for column to be imported 6. Click on OK.

Allow it few seconds and you would see the column in your table! Didn’t I tell you – It’s very simple!

That’s about it for this post. So next time, You need to import a column while developing the model, do not worry – it’s just couple of clicks. But, Even though I was able to add a column later, I had to look at the relationships – create one relationship – make sure other things were right. In short – It was more work. So it’s better that you select the columns that you want in the first place. But If the need arises, you can always add columns (and even remove them) via Table properties. I hope it was helpful!