Best way to use Netezza as DW with PowerBI

[tvaraujo]

Team, hello!

I'd like to connect my Power BI Destktop to a Netezza and show/hide the columns.

My main problem here is that if I connect Power BI to Netezza directly, I have a problem with the data not been as nice to see as it should be in the right column in Desktop.

I'd like to create "global" data source that people would connect there and see the data beautifuly as it is in SSAS, but since I can't afford analysis services right now, I need to have another way to ease the life of my users.

The other problem connecting directly to the netezza server is that if I give a read-only login/pass to my users, they will select a few tables, ok, but when I create new measures or when I create new fact/dimensions, those won't be immediately available.

Also, I won't be able to configure the data to be shown as Numbers/Currency/GPS/whatever... 

What's the suggestion by your experience, team?

[v-yuezhe-msft]

@tvaraujo,

What mode do you use to connect to Netezza, import or directquery?What do you mean "global data source"?

In addition, after you create new measures or fact/dimensions, have you refreshed your PBIX file? And after you import data to Power BI Desktop, select the columns in your table, you should be able to change its format to number/currency under Modeling .

Regards,
Lydia

[tvaraujo]

Thank you for your quick reply, Lydia.

I use Direct Query. 

By "Global Data Source", I mean.. take a look at how powrbi works with SSAS.

WIth SSAS, I can create a link with the database and all tables in a tabular model, for instance, and configure, inside the cube, the column "Sales Price" as a "Currency". Also, having SSAS, I won't show the "key" column "Customer_Key" that link the facts and dimensions to the user. Also, having SSAS, I can have the name of the column as "Sales Price" instead of "SALES_PRICE_AMOUNT" as it is in the database. If I add a new measure or detail in the dimension or fact, it will appear "automagically" inside all files that are connect to that SSAS database.

I know that I can fix all this inside the "Modeling" tab of PowerBI, but that will be "fixed" inside a ".pbix" file. If users copy that file, all formula and columns and relationships have to be recreated. For IT, that's a terrible rework. It's not in a "Global" connection or data source.

Since Netezza is a regular database like oracle and sql server, and I can't have a SSAS, I need to connect directly there.

What's the best design that I can have to work between Netezza and PowerBI in the scenario that I want to replicate new dimensions to everyone in a simple way? Did I make sense? 🙂

Kind regards

[v-yuezhe-msft · Accepted solution]

@tvaraujo,

It is not possible to make the regular database work as SSAS in Power BI Desktop as you describe, as Netezza  and SSAS are designed in a different way. If you want to hide columns for users in Power BI Desktop, you can right-click the column in Power BI Desktop and click hide.

Regards,
Lydia