SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Browse Tab Cube, Query Out Large Blocks of Historical Data an Option to Restore History to a Table?


Browse Tab Cube, Query Out Large Blocks of Historical Data an Option to Restore History to a Table?

Author
Message
quinn.jay
quinn.jay
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4052 Visits: 865
Hello, with my question, I'm looking for a way to extract a block of historical data from a cube to a table, and this is an idea i want to know is feasible.

I have two very large cubes, one hold subs data back 13 months, it loads daily, and is a 2014 formatted Tabular cube, and all of it's data is stored in one month data partitions. Same story for a Revenue tab cube formatted in 2014 living in a 2016 formatted instance with 38 months, each month in a data partition.

Can I navigae to the cube in the SSAS instance, right click it, go to Browse, and write or design a query that can extract out certain columns across a year or multiple years of data and pipe that out to a table in a database?

The issue, for either of the cubes, I take in one or two months at a time to a trunc and load table, where it then gets processed into the appropriate data partition of the cube. So I do not have a table of historical data, the cube hold the historical data in an aggregated format. I need that historical data in table format (or flat file maybe) so a sister team can make use of it to stop back hauling the same several years of data every other day and hammering the system so.

I'm open to any and all ideas to efficiently accomplish this task.

Thank you,
JQ
Martin Schoombee
Martin Schoombee
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16452 Visits: 4715
You should be able to extract the cube data to Excel, and use the Excel file as source for your table. An SSIS package can be developed to import the data.

That being said, you're just putting a band-aid on the real problem of not storing your historical data in the first place.



quinn.jay
quinn.jay
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4052 Visits: 865
Martin Schoombee - Wednesday, February 14, 2018 5:42 AM
You should be able to extract the cube data to Excel, and use the Excel file as source for your table. An SSIS package can be developed to import the data.

That being said, you're just putting a band-aid on the real problem of not storing your historical data in the first place.


Hi, Excel goes to 1 million records, I'm around 1.5+B rows. Thanks
Martin Schoombee
Martin Schoombee
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16452 Visits: 4715
quinn.jay - Wednesday, February 14, 2018 9:54 AM
Martin Schoombee - Wednesday, February 14, 2018 5:42 AM
You should be able to extract the cube data to Excel, and use the Excel file as source for your table. An SSIS package can be developed to import the data.

That being said, you're just putting a band-aid on the real problem of not storing your historical data in the first place.


Hi, Excel goes to 1 million records, I'm around 1.5+B rows. Thanks

Well now that would've been great information to have at the start, but in any event you can surely limit the data to fit into an Excel workbook...or us multiple workbooks etc.




quinn.jay
quinn.jay
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4052 Visits: 865
Martin Schoombee - Wednesday, February 14, 2018 10:27 AM
quinn.jay - Wednesday, February 14, 2018 9:54 AM
Martin Schoombee - Wednesday, February 14, 2018 5:42 AM
You should be able to extract the cube data to Excel, and use the Excel file as source for your table. An SSIS package can be developed to import the data.

That being said, you're just putting a band-aid on the real problem of not storing your historical data in the first place.


Hi, Excel goes to 1 million records, I'm around 1.5+B rows. Thanks

Well now that would've been great information to have at the start, but in any event you can surely limit the data to fit into an Excel workbook...or us multiple workbooks etc.

I think an approach I've not tried before, would be OPENQUERY linked to the SSAS Tab instance, and try to extract that large volume that way, then find a way to build deltas say monthly from that processes. I'll have to ping back in to mention if this was a feasible solution.

jordan 32624
jordan 32624
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 7
Power Query and any connector using M has the ability to connect to and import data from an SSAS db. SSIS 2017 should have the ability to add a connector that takes advantage of this, so your data source could be SSAS db directly.


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search