August 7, 2017 at 3:36 pm
Hello, I've got an SSIS pkg that loads data to SQL Server database from a single summarized table from Teradata, and I've got a DSV created off that single SQL Server table as the feed into a Tabular Cube. Within the design of the cube, I have hidden some columns, as well as summarized them.
When I connect with Excel, I can see them as facts and measures. In SSAS I create a hierarchy in the diagram, place some columns into the new hierarchy. Then in Excel, I can pull any column from the hieracrchy, and now matter which column I pull, I get the same value of what the first column is in that hierarchy.
I get the impression it's because the one table is all fact data, and not dimensional data. Is there a way in SSAS to turn one or more of these columns into a dimension? Or am I going to need to break up the data into more than one table and bring in as DSV's and join, and work that way?
August 8, 2017 at 1:35 pm
Are you using SSAS multidimensional (cubes) or SSAS tabular? They're two different applications and are designed differently.
You mention DSV which makes me think you have a multidimensional instance, but you keep referencing tabular..
August 9, 2017 at 7:39 am
Tom_Hogan - Tuesday, August 8, 2017 1:35 PMAre you using SSAS multidimensional (cubes) or SSAS tabular? They're two different applications and are designed differently.You mention DSV which makes me think you have a multidimensional instance, but you keep referencing tabular..
It's as Tab cube, and when I say DSV I mean the view I created from the the source table, that I have pulled into the tab model, sorry for any confusion
August 9, 2017 at 8:31 am
I'm not too familiar with tabular but your best bet it to split the columns you want to be dimensions into another "table." The easiest way would be to create a SQL Server view that only has the columns you want to leverage for slicing / filtering ( the dimension(s) ) from the single summarized table you have. Probably using a select distinct. IIRC, it's called a degenerate dimension. Not sure about the performance though. Actually splitting into another table may perform better.
This is based off my experience with SSAS multidimensional. I leverage SQL Server views as an intermediate layer between my data warehouse (which generally consists of a bunch of star schemas) and the cube. This gives me a lot of flexibility when it comes to presenting the data to the cubes (filtering, columns included and their names, etc.).
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply