How can I create an empty table in a SSAS tabular cube? I don't want this empty table to be a calculated table. And, I don't want to import an empty table from SQL Server. I'm working in Visual Studio 2019, with the Microsoft Analysis Services Projects extension installed.
Add a comment
|
2 Answers
Pull in data in Power Query, then remove all rows then all columns. Here's an example using my date dimension.
let
Source=#"CSV Calendar csv",
#"Filtered Rows" = Table.SelectRows(Source, each [DateKey] = #date(1111, 1, 1)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"DateKey", "DateInt", "YearKey", "QuarterOfYear", "MonthOfYear", "DayOfMonth", "MonthName", "MonthInCalendar", "QuarterInCalendar", "DayOfWeekName", "DayInWeek", "MonthYear", "SortColumn", "AcctWorkDays", "HolidayWorkday"})
in
#"Removed Columns"
It's a bad idea if you are using Visual Studio for the model. You can't add measures, nor update them if they are there. The blue selected cell in the grid is in the "Add Column" column and is not editable.
The only way I know to get a measure on this table is to add a column to enable the grid, then you can add a measure, and then you can delete the column. That all works, but then I got the displayed message:
3 Comments
skyline01
I can create / update / view measures on an empty (DAX-generated) table in Visual Studio. I always use Visual Studio to create my tabular cubes.
TheRizza
I added more detail above. How do you add a measure to a calculated table with no columns?
TheRizza
Or by "empty" do you just mean no rows?
I think this might be what you're looking for. As far as I know, you need at least one existing DataSource to add a table, but then the table doesn't need to refer to it. In this example, I just added a local text file. Then right click it and and choose Import New Tables. This will pop open Power Query pointed at the DataSource.
In the formula bar, you can change the formula to = Table.FromRecords({},{"Column1"}):
