Forum Discussion
migration data model from AS to Power BI
How to migrate data model created in Analysis Services / Azure Analysis Services to Power BI Desktop?
I have a hundreds of measures and do not want to manually recreate all ones
Hi Anonymous,
I'm afraid we can't do that for now. But I found a workaround indeed. The measures in an Excel model can be imported into PBI Desktop. And we can use VBA to create measures fastly. The workaround is clear now. Please try the demo in the attachment.
1. Install the DAX studio from here.
2. Connect to the SSAS tabular and run the query below.
select [name], [expression] from $SYSTEM.TMSCHEMA_MEASURES
3. Now, we get all the measure names and their details (part 5 in the image). Copy the details to a workbook in sheet1 starting from cell A1.
4. Copy a few cells in a sheet and create a very simple data model in Excel.
5. Open the Visual Basic window and paste the code below and run it.
Public Sub add_measure() Dim Mdl As Model Dim tbl As ModelTable Dim nRows As Integer Dim i As Integer Dim measureName As String Dim measureContent As String Set Mdl = ActiveWorkbook.Model Set tbl = Mdl.ModelTables(1) nRows = Sheet1.UsedRange.Rows.Count 'get the total rows For i = 1 To nRows measureName = Sheet1.Cells(i, 1) measureContent = Sheet1.Cells(i, 2) ' MsgBox measureName ' MsgBox measureContent Mdl.ModelMeasures.Add measureName, tbl, measureContent, Mdl.ModelFormatWholeNumber(1) 'insert a measure to the model Next i End Sub6. Now, we can see all the measures in the model and we can import all of them into Desktop.
Best Regards,
Dale
6 Replies
Hi Anonymous,
You can connect to the SSAS models. But I'm afraid you can't import the SSAS model and turn it into Desktop Model directly. BTW, if it's SSAS tabular, the measures are created with DAX which is also used in the Desktop. So you can just copy the measure and paste it in Desktop.
Best Regards,
Dale
Thanks Dale,
Ya, it is SSAS tabular. Can I do copy / paste measures in a bulk? No one by one, because it will take too long.
I know I can document measures with Dax Studio but can I then push them to pbix some how?
If I create new pbix file and recreate tables, then
If I change pbix extention to zip, I will be able to find file with a data model:
Unfortunatelly I am not able to open it. Can I paste measures there somehow?
Hi Anonymous,
I'm afraid we can't do that for now. But I found a workaround indeed. The measures in an Excel model can be imported into PBI Desktop. And we can use VBA to create measures fastly. The workaround is clear now. Please try the demo in the attachment.
1. Install the DAX studio from here.
2. Connect to the SSAS tabular and run the query below.
select [name], [expression] from $SYSTEM.TMSCHEMA_MEASURES
3. Now, we get all the measure names and their details (part 5 in the image). Copy the details to a workbook in sheet1 starting from cell A1.
4. Copy a few cells in a sheet and create a very simple data model in Excel.
5. Open the Visual Basic window and paste the code below and run it.
Public Sub add_measure() Dim Mdl As Model Dim tbl As ModelTable Dim nRows As Integer Dim i As Integer Dim measureName As String Dim measureContent As String Set Mdl = ActiveWorkbook.Model Set tbl = Mdl.ModelTables(1) nRows = Sheet1.UsedRange.Rows.Count 'get the total rows For i = 1 To nRows measureName = Sheet1.Cells(i, 1) measureContent = Sheet1.Cells(i, 2) ' MsgBox measureName ' MsgBox measureContent Mdl.ModelMeasures.Add measureName, tbl, measureContent, Mdl.ModelFormatWholeNumber(1) 'insert a measure to the model Next i End Sub6. Now, we can see all the measures in the model and we can import all of them into Desktop.
Best Regards,
Dale